PowerBI All function: table parameter vs column

[Anonymous]

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.

1. A Products table has 397 rows. Each product is one of four catgories
2. A matrix contains Product Categories on Rows, and a count in Values. If:

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?

[selimovd · Accepted solution]

Hello @Anonymous ,

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

[Anonymous]

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.

[selimovd]

Hey @Anonymous ,

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