cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 328 members 3,323 guests
Please welcome our newest community members: