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.

View solution in original post

4 REPLIES 4
Highlighted
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.

View solution in original post

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,241)