Countrows based on RANKX result

[hemann]

Hi PBI gurus, 

I have one table of data called Table with two columns "Product" and "Volume". I have performed top 5 Rankx calculation which works fine. I now want to countrows based on Rankx result and return either top 5 or top 4 depending on the number of results from Top5 Measure. For example the Top5 measure result returns 9 results, I want to put a condition if it returns <=5 results I want to return Top5 measure, if not I want return Top4 so <=4. 

I believe this would need a countrows added to the Rankx calculation but struggling to combine the two. 

Top 5 Measure; 

Top5 = if(RANKX(ALL('Table'[Product]),calculate(sum('Table'[Volume])),,DESC,Dense)<=5,RANKX(ALL('Table'[Product]),calculate(sum('Table'[Volume])),,DESC,Dense),blank())

Result from Top 5 Measure

Product	Top5
J	1
N	2
G	3
I	3
E	4
A	5
B	5
K	5
M	5

Result wanted as Top 5 return 9 results (revert to top4)

Product	Top5
J	1
N	2
G	3
I	3
E	4

Data Table 

Product	Volume
A	2
B	2
C	1
D	1
E	3
F	1
G	4
H	1
I	4
J	21
K	2
L	1
M	2
N	17

[ryan_mayu]

@hemann 

here is a workaround for you

Measure = rankx(all('Table'[Product]),CALCULATE(sum('Table'[Volume])),,DESC)+rankx(all('Table'[Product]),CALCULATE(max('Table'[Product])),,DESC)/100

Measure 2 = rankx(all('Table'[Product]),[Measure],,ASC)

[hemann]

Thanks for your reply. Based on my requirements I'm not after a tie break splitter, but rather logic if top 5 results in more than 5 results revert to top 4. Only way I could see to do this is by countrows logic on the Rankx 

[ryan_mayu · Accepted solution]

@hemann 

you can try this

Measure 2 = 
VAR _tbl=SUMMARIZE(ALL('Table'),'Table'[Product],"value",sum('Table'[Volume]))
VAR _tbl2=ADDCOLUMNS(_tbl,"rank",rankx(_tbl,[value],,DESC,Dense))
VAR _count=COUNTROWS(FILTER(_tbl2,[rank]<=5))
return If(_count>5,IF([Measure]<5,1))

and add measure 2 to filter 

pls see the attachment below

[hemann]

This worked. I thought the solution involved creating virtual tables, but couldn't get them to work. 

Thanks legend!!!

[ryan_mayu]

you are welcome