DAX Fridays! #7: ALL, ALLEXCEPT

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

SUBSCRIBE to learn more about Power and Excel BI!

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP

[cindyc]

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. 😁😁😁

[claudiorebelo]

Another reason to love Fridays :) thanks a lot!

[ltrafael]

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?

[NorbertoVeraReatigaNVR]

Gracias!!! Muy bien explicado, su método me ayuda bastante en la comprensión del lenguaje DAX!

[kapilsharma]

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 ?

[pranjalagrawal]

It gets cleared in your other video.
DAX Fridays! #152: ALL vs ALLEXCEPT

Thank you.

[vrgamer]

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.

[ozGold]

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!

[malourb]

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.

[Hustling_Learning]

Please try to make a video by implementing this in power bi.

[zqshen]

hi the file download page does not exist.

[HomelessOnline]

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?

[BigDataSport]

Could you please provide Excel file, thanks in advance! here it is also missing, thanks Ruth

[Phoenixspin]

This is the first time that I've understood this.

[hdjim]

the website link above has 4 w's so the link does not work. U need to remove one of the w's.

[tymothylim]

Thank you for the video! It was helpful for me :)

[krishnamanjunatha]

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

[malikanath]

HI,

I am unable to add data in DAX studio 2.8 you export excel file or connect with any server

[kasturi]

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?