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
Highlighted
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
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

 

Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)