Helper 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 an Measure Sales: SUM(Sales[Sales])
Step 2: Add Table to visual. Add Customer Name and Sales Measure. Sort Visual by Sales.
Step 3: Create Customer Rank : Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),Sales[Sales_M],,DESC,Dense)
Step 4: Drag Customer Rank to Table visual
Step 5: Add visual level filter and include only Rank Source= Yes
Step 6: Add a single select slicer based on Source and remove blank from slicer list. Select any one source.
Step 7: Show the Rank by respecting both Visual level filter and Slicer.

Upto Step 7 everything is working fine and rank is also correct.

Step 8: Drag Category to Table and rank is incorrect now. I want to show Category as passive field without affecting the rank.
Step 9: Changed Rank calculation and context to exclude Category from Filter context.
Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),CALCULATE(Sales[Sales_M],REMOVEFILTERS(Sales[Categoy])),,DESC,Dense)

Rank is correct now

Step 10: Add Status to table. Now rank is incorrect again. Changed rank context again to remove Status from filter context
Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),CALCULATE(Sales[Sales_M],REMOVEFILTERS(Sales[Categoy],Sales[Status])),,DESC,Dense)

Rank is correct now

Step 11: Add Status to slicer. Remove Unknow from Slicer values so that it doesn't appear in slicer select list and then filter on one status.

Now data is filter for that status but Rank is not continuous like 1,2,3,5. It first calculated the rank and then filtered on status.

I want rank to respect the Status filter when applied in slicer but status should act as passive field in the visual.

Once Rank is correct then I need a Top N parameter where user can select Top N customers by respecting all slicers

Here is pbix file. PBIX

Thank you in advance for help.

Regards,
Cruncher

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.

20 Replies

johnt75
Super User
1 year ago
For the ranking you need to use ALLSELECTED( Sales[Status] ), not REMOVEFILTERS.

Customer_Rank = RANKX ( ALLSELECTED ( Sales[Customer] ), CALCULATE ( Sales[Sales_M], REMOVEFILTERS ( Sales[Categoy] ), ALLSELECTED ( Sales[Status] ) ), , DESC, DENSE )

For the TopN filter you can create a numeric parameter and then create a measure like

Row Is Visible = IF ( [Customer_Rank] <= [Top N Parameter Value], 1 )

Use this as a filter on the visual to only show when the value is 1.
- cruncher
  Helper II
  1 year ago
  Thanks johnt75 for looking into it. If I use your new rank calculation then it is not respecting the status slicer and Unknown is showing in the status in the table for Microsoft customer.
  - johnt75
    Super User
    1 year ago
    That seems to be the behaviour of the Select All option in the slicer. If you untick Select All and then select either Booked or Unbooked both then it works. With Select All selected the filter generated for the underlying DAX query is different.
    
    If you do not tick the Select All option and choose Booked then the filter generated is
    
    VAR __DS0FilterTable3 = TREATAS({"Booked"}, 'Sales'[Status])
    
    If you tick the Select All option and then untick Not Booked the filter is
    
    VAR __DS0FilterTable3 = FILTER( KEEPFILTERS(VALUES('Status'[Status])), NOT('Status'[Status] IN {"Not Booked"}) )
    
    The second version, with Select All ticked, checks that the Status is not "Not Booked", but this ignores the filter that you put on the slicer to not show Unknown.
    
    You should raise this as a bug with MS support.
    
    As a workaround, you may need to disable the Select All option.
v-kathullac
Community Support
1 year ago
Hi cruncher ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya
v-kathullac
Community Support
1 year ago
Hi @cruncher ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya
- cruncher
  Helper II
  1 year ago
  Hi v-kathullac I am still facing issue with showing correct rank when used with Top N calculation and john was not able to figure it out

v-kathullac

Community Support

1 year ago

Hi cruncher ,

can you try with below dax measures.

Create Sales Measure :

Sales_M = SUM(Sales[Sales])

Initial Rank Measure

Customer_Rank =
RANKX(
ALLSELECTED(Sales[Customer]),
[Sales_M],
,
DESC,
DENSE
)

Make Category Passive in Rank

Customer_Rank =
RANKX(
ALLSELECTED(Sales[Customer]),
CALCULATE([Sales_M], REMOVEFILTERS(Sales[Category])),
,
DESC,
DENSE
)

Make Status Passive in Rank (Breaks Slicer)

Customer_Rank =
RANKX(
ALLSELECTED(Sales[Customer]),
CALCULATE([Sales_M], REMOVEFILTERS(Sales[Category], Sales[Status])),
,
DESC,
DENSE
)

Final Rank Measure – Respects Slicer, Ignores Visual Filters

Customer_Rank =
RANKX(
FILTER(
ALLSELECTED(Sales[Customer]),
CALCULATE(MAX(Sales[Status])) <> "Unknown" // optional
),
CALCULATE([Sales_M], REMOVEFILTERS(Sales[Category])),
,
DESC,
DENSE
)

Enable Dynamic Top N Filter
1.Create Top N Parameter (from Modeling)
Table_Name: TopNValue
Range: e.g., 1–20

ShowTopN =
IF([Customer_Rank] <= [TopNValue], 1, 0)

Apply Visual-Level Filter : Drag ShowTopN to visual-level filter and Set it to show when value = 1

Regards,

Chaithanya.

v-kathullac
Community Support
1 year ago
Hi @cruncher ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya
- cruncher
  Helper II
  1 year ago
  It didn't work. Please try with pbix file I attached earlier
  - v-kathullac
    Community Support
    1 year ago
    Hi cruncher ,
    
    can you check with below mentioned measure and let us know if you need any further assistance. for your reference am attaching a screenshot as well.
    
    Customer_Rank 1 = VAR SelectedStatus = VALUES(Sales[Status]) RETURN RANKX( FILTER( ALLSELECTED(Sales), Sales[Rank Source] = "Yes" && Sales[Source] IN VALUES(Sales[Source]) && Sales[Status] IN SelectedStatus ), CALCULATE(SUM(Sales[Sales])), , DESC, DENSE )
    
    Regards,
    
    Chaithanya..
v-kathullac
Community Support
1 year ago
Hi @cruncher ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

Forum Discussion

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

20 Replies

Related content

List.Generate, make behave like Accumulate or XL REDUCE

line chart not behaving as expected

Today Function in Column

Using TREATAS DAX function in calculated columns

Web behaves differently than Desktop