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

[Fuhrer786]

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_Name	CustomHospitalType	Custom_IDN	FullAddress	Test	Other Measure
A	Hospital	StandAlone	CT,06053	100$	10%
B	Hospital	StandAlone	CA,90638	98$	15%
C	Hospital	StandALone	IL,60521	95$	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!

[Fuhrer786 · Accepted solution]

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

[v-xulin-mstf]

Hi @Fuhrer786

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

Best Regards,

Link

[v-xulin-mstf]

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