cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fuhrer786
Frequent Visitor

RanKx on desired measure not working when using multiple other measures in matrix

Hi,

 

I am trying to filter a list of  Top N hospitals based on a value using Top N what if slicer selection. I have different other measures I need to show in the table visual to users too. It works fine when I just use the Test measure that Rankx is performing on but when I add other measures in the visual, the rankx DAX doesn't work.

Hospital_NameCustomHospitalTypeCustom_IDNFullAddressTestOther Measure
AHospitalStandAloneCT,06053100$10%
BHospitalStandAloneCA,9063898$15%
CHospitalStandALoneIL,6052195$12%

 

My Current formula:

TopN Facilities = var selectedTop=SELECTEDVALUE('TopN'[TopN])

return SWITCH(TRUE(),selectedTop=0,[Test],RANKX(ALLSELECTED(DefHC_FacilityList[HOSPITAL_NAME],DefHC_FacilityList[CustomHospitalType],DefHC_FacilityList[FullAddress],DefHC_FacilityList[Custom_IDN]),[Test])<=selectedTop,[Test])
 
The above formula works fine If I just have Test as the only measure in the table and user can select Top5 Hospital_name ranked by Test measure, but when I add another measure/multiple measures into the table, the rank DAX doesn't seem to work. It shows all rows vs what's selected in the Top N slicer.
 
I need to freeze rank DAX to show Top N hospitals based on Test measure only no matter how many measures are present in table visual.
 
Any help greatly apprecaited!

 

1 ACCEPTED SOLUTION
Fuhrer786
Frequent Visitor

HI @v-xulin-mstf 

 

Thanks a lot for looking into it. I was able to do it by doing rank as measure

 

Rank_Facilities = var selectedTop=SELECTEDVALUE('TopN'[TopN])
RETURN

SWITCH(TRUE(),selectedTop=0,[Test],(RANKX(ALLSELECTED(TableName),[Test])<=selectedTop),[Test])

View solution in original post

3 REPLIES 3
Fuhrer786
Frequent Visitor

HI @v-xulin-mstf 

 

Thanks a lot for looking into it. I was able to do it by doing rank as measure

 

Rank_Facilities = var selectedTop=SELECTEDVALUE('TopN'[TopN])
RETURN

SWITCH(TRUE(),selectedTop=0,[Test],(RANKX(ALLSELECTED(TableName),[Test])<=selectedTop),[Test])

View solution in original post

Hi @Fuhrer786

 

Congratulations, if the issue has been solved, please adopt your solution to help others.

 

Best Regards,

Link

v-xulin-mstf
Community Support
Community Support

Hi @Fuhrer786,

 

You can try to use RANKX by creating calculated column.

You can refer this related post  to implement the dax conversion.

So that the Rankx results are not affected by the line context.

 

Best Regards,

Link

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors