Reply
Highlighted
Regular Visitor
Posts: 19
Registered: ‎04-17-2018
Accepted Solution

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

[ Edited ]

 

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!

 

expected_result.png


Accepted Solutions
Moderator
Posts: 10,202
Registered: ‎03-10-2016

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

@Maikeru,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Moderator
Posts: 10,202
Registered: ‎03-10-2016

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

@Maikeru,

Please check the DAX in the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1Nok2nX8pAE9CsexCEn

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 19
Registered: ‎04-17-2018

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

@v-yuezhe-msft


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

 

new_result.png

 

Moderator
Posts: 10,202
Registered: ‎03-10-2016

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

@Maikeru,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 19
Registered: ‎04-17-2018

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

@v-yuezhe-msft

 

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