Forum Discussion

cruncher's avatar
cruncher
Icon for Helper II rankHelper II
1 year ago
Solved

Rankx function is behaving weird when used in table with extra columns and Slicers

Hi Experts.   I am having a hard time understanding dax evaluation context in Rankx function. I want to rank the customers based on sales. These are the steps I have followed.   Step 1: Creating ...
DAX
RANKX
  • v-kathullac's avatar
    v-kathullac
    1 year ago

    Hi cruncher ,

     

    Can you try with below dax measure attaching the screenshot for your reference.

    Customer_Rank 3 = 
RANKX(
    FILTER(
        ALLSELECTED(Sales),
        Sales[Rank Source] = "Yes"
            && Sales[Source] IN VALUES(Sales[Source])
    ),
    CALCULATE(SUM(Sales[Sales])),
    ,
    DESC,
    DENSE
)

     

    Regards,

    Chaithanya.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

I think I understand what the problem is but unfortunately I don't know how to fix it.

Because there is a filter on the ShowTopNOpps measure the query generated for the table visual contains a SUMMARIZECOLUMNS which calculates the rows which should be visible in the final result. I think that this SUMMARIZECOLUMNS is generating a new shadow filter context, which is what is accessed by ALLSELECTED. This seems to be messing up the measure calculation.

Given that there is no way to control the DAX query generated for the table visual, there is no way to get around this.

The only thing that I could suggest is instead of using a parameter and measure to control the top N, use a Top N filter in the filter pane, ranking Customer by the customer ranking measure.

cruncher's avatar
cruncher
Icon for Helper II rankHelper II
1 year ago

Thanks for insights. I need a slicer in my view so I can't use the Top N filter. I will wait for someone else to chime in. A few questions if you can help:

 

1. How you're testing which filter are getting generated by dax queries like treatas, summarize as you shared in this post.

 

2. When to use the third parameter in rank. What is the significance of that and if there any performance benefit

 

3.  Can we use TOPN dax function to filter the top n customer instead of RankX to overcome this issue ?

 

4.  From where I can get advance knowldege of this evaluation context transitions. Its very confusing when to use which dax.

 

  • v-kathullac's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi cruncher ,

    Thank you for reaching out to Microsoft Fabric Community Forum.

     

    Below are the few Points that can solve your issue.

    1) How you're testing which filters are getting generated by DAX queries like TREATAS, SUMMARIZE?

    • Use DAX Studio to view effective filters, query plans, and server timings.
    • Use Performance Analyzer in Power BI to see which DAX queries visuals are generating.
    • Create debug measures using: CONCATENATEX(VALUES(Table[Column]), Table[Column], ", ")
    • Use ISFILTERED, HASONEVALUE, VALUES to detect if a column is being filtered

    2) When to use the third parameter in RANKX? What's the significance and any performance benefit?

    • Third parameter is used when the ranking expression returns a blank.
    • Acts as a fallback value, often set to 0 or -1.
    • Ensures blanks still receive a rank (e.g., customers with no sales).
    • Provides more predictable ranking behavior.
    • Minor performance impact; mostly used for logic consistency.

    3) Can we use TOPN DAX function to filter the top N customers instead of RANKX?

    • Yes, TOPN returns a table of top N values based on expression.
    • Works well in calculated tables or virtual tables for visuals.
    • Example with slicer: VAR N = SELECTEDVALUE(TopN[Value]) RETURN TOPN(N, VALUES(Customer[CustomerName]), [TotalSales], DESC)

    4) Where can I get advanced knowledge of evaluation context transitions?

    • Learn from sqlbi.com – highly recommended for DAX and model optimization.
    • Read articles on context transition, row context vs. filter context.
    • Use DAX.do for browser-based testing and DAX learning.

    Regards,

    Chaithanya.