cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maikeru Regular Visitor
Regular Visitor

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!

 

expected_result.png

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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.
4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

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.
Maikeru Regular Visitor
Regular Visitor

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 v-yuezhe-msft
Moderator

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.
Highlighted
Maikeru Regular Visitor
Regular Visitor

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