Does filter (All('TableName')) remove duplicates?

[arunbyc]

I am new to Power BI and am learning the basics and am having a tough time figuring out the following:

I have a Product Table with columns for product name, brand, and color. I have a matrix showing Brand and a measure "RedProductscount" The page has a slicer on color. The measure uses All ( ) command

I understand why the count is always the total count of all Red products no matter what color is selected in the slicer when the code in the measure is :

RedProductscount = countrows(filter(ALL('Product'),'Product'[Color]="Red"))

It ignores the color selected in the slicer and counts how many red products are. But I do not understand why it shows a count of 1 if I change the measure to apply ALL to the color column like so:

RedProductscount = countrows(filter(ALL('Product'[color]),'Product'[Color]="Red"))

The only thing I could think of is that 1 represents the count of "Red"s in a list of unique colors. But is it really that?

Questions: If it *is* the count of reds in the list of unique colors, what makes All to remove duplicates in the color column and turn it into a unique color list? Does it mean that Filter(All('TableName')) will ignore duplicates and do we have to be careful when we apply ALL() command on a table?

If it is not the count of reds in the unique list of colors, then what is 1?

[arunbyc]

Thank you for such a detailed explanation. Very helpful..

[OwenAuger · Accepted solution]

Hi @arunbyc 

To answer your question, yes, the value of 1 represents the single "Red" value in the list of unique colors.

When ALL is used with one or more column arguments, it returns all unique values (or combinations of values) from the column(s). The column arguments must all come from the same table. This is just how the function is defined when passed column arguments.

However, when ALL is used with a table argument (which must be a physical table), it retains all rows regardless of duplicates.

In all cases it adds a potential blank row generated for invalid relationships.

So the table expression ALL ( Table[Column] ) will not itself preserve the row count of the underlying table.

I would generally favour using ALL (or REMOVEFILTERS) as a modifier rather than a table expression.

I would personally write the RedProductscount measure something like this (assuming it should return the count of Red products ignoring all filters on the expanded 'Product' table):

RedProductscount =
CALCULATE (
    COUNTROWS ( 'Product' ),
    ALL ( 'Product' ), -- or REMOVEFILTERS ( 'Product' )
    'Product'[Color] = "Red"
)

You could also consider ALLCROSSFILTERED ( 'Product' ) to remove filters outside the expanded 'Product' table that filter 'Product', or simply ALL ( ) to remove all filters.

See here for more detail and examples.

This is also a good article:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

Please post back if needed 🙂