Top product based on sales, returns product even if it is filtered out

[Anonymous]

So I have this DAX code that gives me the top product based on sales and that can be filtered on.

Top product = 
VAR temp_table =
    SUMMARIZE (
        ALL ( sales_corona[Product] );
        sales_corona[Product];
        "SalesTotalValue"; SUM ( sales_corona[Sales] )
    )
RETURN
    CALCULATE (
        MAX ( sales_corona[Product] );
        FILTER (
            ADDCOLUMNS ( temp_table; "Rank"; RANKX ( temp_table; [SalesTotalValue] ) );
            [Rank] = 1
        )
    )

It works absolutely perfect. When I filter I get different results for different cities for example. However when I filter on the product itself. It remains the same. So let's say product X is my top product. Now I apply a filter that leaves product X out (I would assume that product X can't be my top product anymore), however it still is.

[Anonymous]

Hi @Anonymous.

That's because filters do not work in DAX the way you think they are.

First of all, your temp_table contains ALL products regardless of the filter on the column sales_corona[Product]:

ALL ( sales_corona[Product] );

Then, when you FILTER under CALCULATE it takes all products again.

Hence your filter on Product has no effect.

I know exactly what you want but will not write the code since your model is incorrect and the measure wouldn't work in this case anyway.

A correct model stores individual products in a dimension, not in the fact table. Please change it to a proper star schema. You can start with these vids:

https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.youtube.com/watch?v=_quTwyvDfG0

Please learn to build correct models. It's for your own good.

Best
D