Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MitieNHSFred
Frequent Visitor

Rank with Slicers applied

I have an “Init_Scores” table of data, from Excel, which I have used to create a Table visual, showing key data and has 4 slicers on other fields in the table that can be applied jointly or individually.  Some slicer fields come from Excel, others are DAX creations.

 

In the Excel, based on responses to the questions, I have come up with “Overall Impact” and “Overall Priority” values, which are then used to calculate a “Weighted Score” value.

 

In Power BI I want to show a ranking for each initiative, based on the “Weighted Score” AND the field slicer(s) in play at that time or no slicers if nothing has been selected.

 

I’ve tried creating a new

  • Column : Init_Rank_C = RANKX(ALLSELECTED(Init_Scores), Init_Scores[Weighted Score], , DESC, Dense)

  • Measure: Init_Rank_M =
                        VAR CurVal = ((SUM(Init_Scores[Overall Impact])/55) * SUM(Init_Scores[Overall Priority])/34)*100
                        RETURN
                        CALCULATE(
                                   RANKX(Init_Scores, [Weighted Score], CurVal, DESC, Dense),
                                   ALLEXCEPT(Init_Scores, Init_Scores[Status]))

 

But what I find is that, when I apply any/all of the field slicers, the report shows those initiatives I want but

  • the Init_Rank_C value remains unchanged and shows the ranking number as if no field slicer(s) had been applied,
  • the Init_Rank_M values do appear to change, but the rank values appear to jump, rather than be contiguous, even when there are no intervening rows

 

I’ve played around with the DAX and either end up with rank showing everything as 1 or what appears to be random numbers that I cannot explain.

 

Please can anyone help me

 

Thanks in advance

Fred

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

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/1447...

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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

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/1447...

We ended up going on a completely different approach, putting all the entries into a 4-square MoSCoW view, that gave us what we needed without the need for filtering and ranking.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.