DAX Fridays! #7: ALL, ALLEXCEPT
ฝัง
- เผยแพร่เมื่อ 5 ต.ค. 2024
- The function ALL returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.
The function ALLEXCEPT removes all filters except the ones that have been applied to the specified columns.
Video Keynotes:
01:14 ALL function
06:20 ALLEXCEPT function
Link to PBI file: gofile.me/2kEOD...
PREVIOUS VIDEO: • DAX Fridays! #6: FILTER
NEXT VIDEO: • DAX Fridays! #8: CALCU...
Looking for a download file? Go to our Download Center: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Looking for a download file? Go to our Download Center: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
► Linkedin: goo.gl/3VW6Ky
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP
Another reason to love Fridays :) thanks a lot!
Hahha thanks!
/Ruth
Recently I learn about DAX and find your DAX Fridays are very good. When I discovered from your comment about add-ins PP Utilities, that is very good tools to put into Add-ins. I don't see many people mention it. I like it as well. Thanks. 😁😁😁
My pleasure :)
This is the first time that I've understood this.
👏 Bravo!
It gets cleared in your other video.
DAX Fridays! #152: ALL vs ALLEXCEPT
Thank you.
So the function ALL is counting uniques?
When you used it specifying the column ProductCategory, it always returned 4 regardless of the filters. So it is counting the unique values in this column, and not the number of rows, which would be 8.
If when you used all on the whole table, it had a duplicate row, would it return total rows - 1?
Gracias!!! Muy bien explicado, su método me ayuda bastante en la comprensión del lenguaje DAX!
+Norberto Vera Reatiga Muchas gracias Norberto, un placer saber que los vídeos son útiles para ti! :) /Ruth
i tried ALL(Products[ProductName]) formula in power bi measure and tried to see it in visualization pane under table ,but it shows error. could you please help me that what's wrong and also it does not show any evaluate formula in it like you use in dax studio. Also is dax studio is different from power bi ?
Please try to make a video by implementing this in power bi.
Thank you for the video! It was helpful for me :)
Hi curbal I have learnt a lot from your channel. I have a question for you. If we have applied filter steps to a table in power query and we want to work with this table with filter(s) removed inside power bi is there a way we can do this thing? Please answer.
excellent mam
Could you please provide Excel file, thanks in advance! here it is also missing , thanks Ruth
the website link above has 4 w's so the link does not work. U need to remove one of the w's.
+hd Jim I have that mistake on all videos :(
I guess I am going to be busy this weekend...
Many thanks for letting me know Jim and have a great weekend!
/Ruth
Around the 4:06 mark, how did you install the "Format DAX expressions", "Model Memory Usage" and "List measures" add-ins? Under my add-in menu, I only have the DAX Studio icon. Keep up the great work!
It is an addin calles PP utilities.
/Ruth
@@CurbalEN Thank-you! I've learned so much from your videos!
Thanks for posting this! :)
This video is not present in the 'DAX Fridays!' playlist. Can you please add it there, so we have all the DAX videos at one place?
+Kasturi G, Sometimes TH-cam does not allow me tag the videos before I publish them and I then forget to do it afterwards :S
Thanks for letting me know, Ruth
Oh I see :)
I'm stuck on the right DAX function to use. I'd like to get a total of sales for each department (there are over 40), and then within each department, I need to calculate EACH sales person's sales and compare it to the overall total of his own department. Because there are over 40 departments, do I specify each one in the ALLEXCEPT filter? This is where I keep getting stuck. Any suggestions? Thanks.
Hi Marilour! TH-cam comments is not the optimal place for answering details questions, can you post your question in the Power BI forum?
Depending on how you want to visualize the data you can do it without DAX.
/Ruth
Hi thanks. I did that also but I haven't had much luck getting response. But thanks
Hi again,
As the Power bi Community greps the competition for getting answers increases as well.
Have you read this? The key to secure an answer in the Power BI community:
community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
/Ruth
i cant access the files
How are ALLEXCEPT and ALL (with multiple columns) actually used? They must not be measures because Excel throws an error when I try to drop it into the Values section. Clearly from your example, they are returning a table, but how would I use the returned table?
+HomelessOnline Hi?
You are always working with tables right? So no difference there. Those functions help you create a new table that matches your conditions so you can do your calculations.
/Ruth
But I assumed they can be used in a pivot table. Am I wrong?
+HomelessOnline Not sure what you mean. Everything depend on the measure that you write.
/Ruth
+HomelessOnline You wouldn't typically use all() or allexcept() as measures themselves to go into a pivot table or report, but inside calculate or filter. For example suppose you have a data set for sales which includes the unit price, quantity sold, location of sale and date of sale in a table called Retail Sales. Your boss wants to see the total sales per location so you create a measure called Total Sales as
Total Sales = sumx('Retail Sales', 'Retail Sales'[Quantity] * 'Retail Sales'[Price])
You use this in a table with the location and see the total sales per location. Happy faces all around. But, then your boss wants to know what percentage of the overall total sales each location contributes. Having the location in the table implicitly filters the [Total Sales] measure for each row so what you need is a measure that avoids that filtering so you can divide the [Total Sales] for each location by it to get your percentage. So you create:
Total Sales unfiltered = sumx(all('Retail Sales'), 'Retail Sales'[Quantity] * 'Retail Sales'[Price])
which essentially repeats the [Total Sales] calculation but tells sumx() to ignore any filtering on the 'Retail Sales' table. You then get your percentage using:
%age sales = divide([Total Sales], [Total Sales unfiltered], 0)
Everyone is happy again, until your boss decides they want to be able to filter by the date of sale so they can look at how things change over time. You add a date slicer but since [Total Sales unfiltered] ignores all filters it always give the total for all the sales in the table, not just the date period selected. This means that the percentages are off, and don't add up to 100%. To resolve this you create two more measures:
Total Sales Date Filtered = CALCULATE(sumx('Retail Sales', 'Retail Sales'[Quantity] * 'Retail Sales'[Price]), allexcept('Retail Sales', Dates[Date]))
%age sales date filtered = divide([Total Sales], [Total Sales Date Filtered], 0)
[Total Sales Date Filtered] ignores all filters on Retail Sales unless they are on the date of sale (in this case I am using a dataset that has a Date table called Dates that has a one to many relationship with Retail Sales on Dates[Date] and Retail Sales[Purchase Date]). Using %age Sales Date Filtered in your table means it now obeys the date slicer.
Thanks for taking the time !
/Ruth
very nice
Happy Friday :)
:Ruth
hi the file download page does not exist.
HI ,
I am unable to add data in DAX studio 2.8 you export excel file or connect with any server
Can you contact the developers?
/Ruth
Try downloading 2.8.1 and see if the bug fixes solves your issue.
hi
i added DAX Studio Excel Add-In (Excel > Options > Add-Ins > COM Add-Ins), but i could not seen DAX Studio Add-In in excel ribbon.
i am getting Tabular Server option only after launching DAX studio
please solve issue ASAP
+Krishna Manjunatha Hi Krishna,
Once you have your DAX Studio installed, you will notice that there is a new “Add-ins” menu on the ribbon. Click on the DAX Studio button to launch DAX Studio.
/Ruth
+Krishna Manjunatha I recommend you remove your email from the comment to avoid spam.
after installation i did not get that on Excel Ribbon. so added through Options > COM Add-Ins but not appeared on ribbon in excel. finally directly launched DAX Studio but getting only Tabular Server option only for connecting :(
+Krishna Manjunatha Hi, I tested also and I am having the same issue.
Please contact them directly, I am sure they will be able to help you!
/Ruth
sorry to say
you also have the same issue. plz let me know the steps to clear issue if you already aware :)