Forum Discussion
Rank based on dynamic slicer selection
Hi All,
I have a sales table, and I am ranking my customers based on below dax:
I also have two slicers on my report , one which will filter customer location and another year. I have only one sales table and all columns are coming from it. When I don't select anything it give the correct ranking but when I apply filter on the slicers it doesn't work. How I can make the rank function work based on filter selection?
Hi testyuiiop
lbendlin , thanks for your concern about this case.
I’d like to acknowledge the valuable input provided by the Sahir_Maharaj. His initial idea was instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.
In my investigation, I took the following steps:My sample:
I created two new tables for each of the two slicers.
There is no relationship between the three tables.
I also created a following formula just to get the measure.
Total Sales = CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(Sales, Sales[Cust Name]))Create a measure as follows
rank = RANKX(ALLSELECTED(Sales), [Total Sales], , ASC, Dense)Result:
testyuiiop , if this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it!
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi testyuiiop
lbendlin , thanks for your concern about this case.
I’d like to acknowledge the valuable input provided by the Sahir_Maharaj. His initial idea was instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.
In my investigation, I took the following steps:My sample:
I created two new tables for each of the two slicers.
There is no relationship between the three tables.
I also created a following formula just to get the measure.
Total Sales = CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(Sales, Sales[Cust Name]))Create a measure as follows
rank = RANKX(ALLSELECTED(Sales), [Total Sales], , ASC, Dense)Result:
testyuiiop , if this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it!
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This doesn't work at all & how could it since the slicer tables are disconnected from the other tables??
Hello testyuiiop,
Can you please try this:
Customer Rank = RANKX( ALLSELECTED(Sales), [Total Sales], , ASC, DENSE )no this doesn't work
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
