Rankx giving incorrect results on applying visual filter

[Anonymous]

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_date	Plan	Plan Code	Location	headline	Unit	Priority	First_impact	Second_impact
01-Jan-21	Dom	15	Site_1	Air limit	D_Other GDC	FALSE	0	0.47
01-Jan-21	Planned	10	Site_1	This is heater	My_Poker	TRUE	0.06	0
01-Jan-21	Test Planned	8	Site_1	This is heater	My_Poker	FALSE	0.01	0
01-Jan-21	Unplanned	5	Site_2	limitations train	Ge_B12	TRUE	0.02	0
01-Jan-21	Planned	10	Site_2	safety regulation	Ge_Cycle	FALSE	0.04	0
01-Mar-21	Unplanned	5	Site_1	limitations train	Ge_B12	FALSE	0.02	0.01
01-Mar-21	Planned	10	Site_1	limitations train	Ge_B12	TRUE	0.03	0.04
01-Mar-21	Unplanned	5	Site_1	limitations train	Ge_B12	FALSE	0.01	0.01
02-Apr-21	Planned	10	Site_2	safety regulation	My_Poker	FALSE	2	3
02-Apr-21	Dom	15	Site_2	safety regulation	My_Poker	TRUE	5	1

Expected output:

Rank	Start_date	Plan	Plan Code	Location	headline	Unit	Priority	First_impact	Second_impact	Total Impact
1	02-Apr-21	Dom	15	Site_2	safety regulation	My_Poker	TRUE	7	4	11
2	01-Jan-21	Dom	15	Site_1	Air limit	D_Other GDC	FALSE	0	0.47	0.47
3	01-Mar-21	Planned	10	Site_1	limitations train	Ge_B12	TRUE	0.06	0.06	0.12
4	01-Jan-21	Planned	10	Site_1	This is heater	My_Poker	TRUE	0.07	0	0.07
5	01-Jan-21	Planned	10	Site_2	safety regulation	Ge_Cycle	FALSE	0.04	0	0.04
6	01-Jan-21	Unplanned	5	Site_2	limitations train	Ge_B12	TRUE	0.02	0	0.02

Best Regards,

Sumit

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

[Anonymous]

@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 · Accepted solution]

@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]

@Anonymous  did you try the above ?

[lbendlin]

You may want to read this article

Use of RANKX in Power BI measures - SQLBI