Forum Discussion

Maikeru's avatar
Maikeru
Icon for Helper II rankHelper II
8 years ago
Solved

Issues propagating the query context into a CALCULATE nested inside a RANKX function

 

Dear Team,

 

I am trying to create a Rank column (not measure) based on the total sales per customer in my dataset.

 

Rank column = 
RANKX(
    ALLSELECTED('Dataset'[Customer]), 
    CALCULATE(
        SUM('Dataset'[Sales]),
        FILTER(ALLSELECTED('Dataset'), 
        'Dataset'[Customer] = EARLIER('Dataset'[Customer])
        )
    ), ,
    DESC)

 

The formula worked fine until... I apply a query filter (say on the slicer on the material column).
I have tried to debug the formula quite a bit but I have the impression, that DAX cannot apply the ALLSELECTED query filter when the FILTER is nested inside the RANKX + CALCULATE functions.

 

What makes me think that is that the following formula works fine.

Sales by Customer = 
CALCULATE(
    SUM('Dataset'[Sales]),
    FILTER(ALLSELECTED('Dataset'),
    'Dataset'[Customer] = MAX('Dataset'[Customer])
    ) 
)

 

 

Thanks to all in advance!

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    Maikeru,

    Calculated columns don't respond to slicer selections, please check this KB.

    Regards,
    Lydia

4 Replies

  • Anonymous


    Thank you very much for taking the time to look into my issue!


    This is exactly the result I am expecting except the Rank needs to be column and not a measure.

    (Because I want to apply a RunningSum based on the rank column).

     

    Best regards

     

    Michael

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Maikeru,

      Calculated columns don't respond to slicer selections, please check this KB.

      Regards,
      Lydia

      • Maikeru's avatar
        Maikeru
        Icon for Helper II rankHelper II

        Anonymous

         

        Oh thanks Lydia, I missed out this key concept.

        I will then think to achieve what I am looking for.

         

        Thanks for your help.

         

        Best regards

         

        Michael