Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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.

1 REPLY 1
Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors