topic Re: Clarification Needed: How Filters Work in DAX Commands and Tips

Clarification Needed: How Filters Work

SQLDev — Wed, 28 Aug 2024 18:26:26 GMT

As someone who is new to Power BI and just starting to learn, I'm trying to rank products based on their orders. However, I'm encountering an issue where the ranking does not update correctly when I apply a page-level filter on CategoryName. Below is the DAX formula I'm using. Could you please help me identify if I'm missing something?

Total Orders = DISTINCTCOUNT('Sales Data'[OrderNumber])

RankProducts = RANKX(ALLSELECTED('Product Lookup'),
 [Total Orders],
 ,DESC,Dense)

Re: Clarification Needed: How Filters Work

Jihwan_Kim — Wed, 28 Aug 2024 18:05:51 GMT

Hi,

Please try something like below whether it suits your expectation.

RankProducts = RANKX ( ALLSELECTED ( 'Product Lookup'[CategoryName], 'Product Lookup'[ProductName] ), [Total Orders], , DESC, DENSE )

Re: Clarification Needed: How Filters Work

SQLDev — Wed, 28 Aug 2024 18:22:10 GMT

Thanks for the suggestion. I tried your solution, but it resulted in an error because CategoryName and ProductName are in two different tables:

'Product Categories Lookup'[CategoryName]
'Product Lookup'[ProductName]

Re: Clarification Needed: How Filters Work

Jihwan_Kim — Wed, 28 Aug 2024 18:58:03 GMT

I am not sure how your semantic model looks like, but please try something like below.

RankProducts = RANKX ( ALLSELECTED ( 'Product Lookup'[ProductName] ), [Total Orders], , DESC, DENSE )

Re: Clarification Needed: How Filters Work

SQLDev — Tue, 03 Sep 2024 16:36:35 GMT

Initially, the DAX expression wasn't working because I created a calculated column instead of a measure. Once I used the same formula as a measure, it worked perfectly.

RankProducts = RANKX(ALLSELECTED('Product Lookup'),
 [Total Orders],
 ,DESC,Dense)