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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rankx giving incorrect results on applying visual filter

Hi Everyone, 

 

I have below requirement to achieve. I have done all the steps but stuck with below problem. Could you please help?

 

1. I am summing up first impact and second_impact over Start_date, location, headline, Unit and then showing only the row having max Plan Code.

2. Then I have added a column Total Impact which is First_impact + Second_impact and then sorted the data as per Total impact.

3. I need to show the Top N records according to Total impact column and add a rank column.

4. I have added Rank column which shows rank as user selection from Top N slicer. 

 

Problem  -->>   The problem I am facing is that I am seeing the rank as per Top N selection but other all other rows are also visible with blank Total Impact values. I tried putting a Visual Filter (where Total Impact is not blank) but then it give incorrect values. **** Please note, the First_impact and Second_impact are being calculated on other slicer selections ****

 

Data I have:

 

Start_datePlanPlan CodeLocationheadlineUnitPriorityFirst_impactSecond_impact
01-Jan-21Dom15Site_1Air limitD_Other GDCFALSE00.47
01-Jan-21Planned10Site_1This is heaterMy_PokerTRUE0.060
01-Jan-21Test Planned8Site_1This is heaterMy_PokerFALSE0.010
01-Jan-21Unplanned5Site_2limitations trainGe_B12TRUE0.020
01-Jan-21Planned10Site_2safety regulationGe_CycleFALSE0.040
01-Mar-21Unplanned5Site_1limitations trainGe_B12FALSE0.020.01
01-Mar-21Planned10Site_1limitations trainGe_B12TRUE0.030.04
01-Mar-21Unplanned5Site_1limitations trainGe_B12FALSE0.010.01
02-Apr-21Planned10Site_2safety regulationMy_PokerFALSE23
02-Apr-21Dom15Site_2safety regulationMy_PokerTRUE51

 

 

 

Expected output:

 

RankStart_datePlanPlan CodeLocationheadlineUnitPriorityFirst_impactSecond_impactTotal Impact
102-Apr-21Dom15Site_2safety regulationMy_PokerTRUE7411
201-Jan-21Dom15Site_1Air limitD_Other GDCFALSE00.470.47
301-Mar-21Planned10Site_1limitations trainGe_B12TRUE0.060.060.12
401-Jan-21Planned10Site_1This is heaterMy_PokerTRUE0.0700.07
501-Jan-21Planned10Site_2safety regulationGe_CycleFALSE0.0400.04
601-Jan-21Unplanned5Site_2limitations trainGe_B12TRUE0.0200.02

 

 

Best Regards,

Sumit

 

 

@amitchandak @v-chenwuz-msft @MSFT_JT @Fowmy 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@Anonymous  There is a way to return the sum of TOPN and Ranking of TOPN through custom measures. You definitely need to adapt that to your scenario. But atleast it gives you the idea how to do it. The pbix is attached.

 

 

smpa01_0-1635659324958.png

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@smpa01 Apologies for my late response, I was on holiday.

 

The solution you provided works absolutely fine. Thanks heaps for your support, much appreciated 🙂

 

 

 

smpa01
Super User
Super User

@Anonymous  There is a way to return the sum of TOPN and Ranking of TOPN through custom measures. You definitely need to adapt that to your scenario. But atleast it gives you the idea how to do it. The pbix is attached.

 

 

smpa01_0-1635659324958.png

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@Anonymous  did you try the above ?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
lbendlin
Super User
Super User

You may want to read this article

 

Use of RANKX in Power BI measures - SQLBI

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.