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

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's avatar
    cruncher
    Icon for Helper II rankHelper 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's avatar
      johnt75
      Icon for Super User rankSuper 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.

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

      Thanks for clarfication. It helps.

       

      Now. I created a Topn numeric parameter from Modelling tab and used this calculation to filter the top n customers.

       

      ShowTopNOpps = IF([Customer_Rank]<='Top N'[Top N Value],1,0)
       
      I used this in Table visual filter and selected 1. Now Rank is again incorrect.
       

       

       

       

       

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

        can you upload the latest version of the PBIX ?