Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
hi - I'm working through Matt Alingtons Supercharge Powerbi and noticed for the All function that seems odd to me - different behaviour from inputting a table vs column into the All formula. P89 if you have access to the book.
Countrows ( All ( Products ) ) -> it returns all 397 rows in the table, broken up by the Cateogory in the Matrix. That's what I expected.
Countrows ( All ( Products [Category] ) ) -> it returns the value 4 for every row including the total. So it returned only the unique categories. I understand why it's the same value, I don't understand why it's only 4 instead of 397?
Solved! Go to Solution.
Hello @JNABM ,
I think the description from the dax.guide website helps to understand the function better:
Using a table argument, ALL returns all the rows of the table including any duplicated rows.
Using a single column argument, ALL returns all the unique values of the column.
Using two or more columns arguments, ALL returns all the unique combinations of values in multiple columns.
So if you use it on a table, you will get the distinct results for the whole table.
If you use it on a column, you will get the distinct results for this column. So all the duplicated values are removed and you will only see the distinct values.
As an example, for the Contoso database.
This is how an ALL for the whole table looks like:
https://dax.do/0GHr6LuFFW1N9s/
And this is how ALL for the color column looks like:
https://dax.do/Bh6efBFWEow2nK/
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Hello @JNABM ,
I think the description from the dax.guide website helps to understand the function better:
Using a table argument, ALL returns all the rows of the table including any duplicated rows.
Using a single column argument, ALL returns all the unique values of the column.
Using two or more columns arguments, ALL returns all the unique combinations of values in multiple columns.
So if you use it on a table, you will get the distinct results for the whole table.
If you use it on a column, you will get the distinct results for this column. So all the duplicated values are removed and you will only see the distinct values.
As an example, for the Contoso database.
This is how an ALL for the whole table looks like:
https://dax.do/0GHr6LuFFW1N9s/
And this is how ALL for the color column looks like:
https://dax.do/Bh6efBFWEow2nK/
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Thank you for taking the time to help.
I guess that does make sense since the table has a primary key so every row is unique.
Hey @JNABM ,
it makes sense since the data is in the background saved with a column store technology.
This means that anyway each column is saved independent with its unique values of the other columns.
Best regards
Denis
User | Count |
---|---|
83 | |
77 | |
71 | |
69 | |
53 |
User | Count |
---|---|
105 | |
100 | |
91 | |
79 | |
68 |