topic Re: Filter based on a related column with RANKX in DAX Commands and Tips

Filter based on a related column with RANKX

OBMBI — Wed, 04 Aug 2021 10:28:30 GMT

I am trying to calculate the TopN value for Confidence by Provider name for the latest date.

The below works for the ProviderId but the Provider Name is available in a separate table. These are related with an active Cross directional filter, which is set to 'both'.

How do I get this to work with the Provider Name? When I put the Provider Name in the legend on my trend chart, it does not recognise the relationship. I've also tried to add the USERELATIONSHIP into the RANKX function but also can't get this to work.

TopN Confidence by ProviderId = VAR SelectedTop = SELECTEDVALUE('TopN'[TopN]) VAR SelectedOrder = SELECTEDVALUE(FilterOrder[FilterOrder]) VAR RankOrder = IF(FilterOrder = "Top", 1, -1) RETURN SWITCH(TRUE(), SelectedOrder = "All", [Average Confidence], RANKX ( FILTER( ALLSELECTED( 'Confidence'[ProviderId], 'Confidence'[Date] ), 'Confidence'[Date] = MAX(SortByDate[Date]) ), 'Confidence'[Average Confidence] * RankOrder ) <= SelectedTop, 'Confidence'[Average Confidence] )

Re: Filter based on a related column with RANKX

amitchandak — Wed, 04 Aug 2021 12:32:24 GMT

OBMBI , if you create a rank on provider id and use it with provider name in visual, this means rank of provider id insider provider name

You need to include provide name in Rank

Re: Filter based on a related column with RANKX

OBMBI — Wed, 04 Aug 2021 12:35:14 GMT

Hi amitchandak,

Thanks for the reply, I am using the ProviderId in the RANKX and when I use the Provider Name in the visual, all ranks get set to 1.

Also, how would you include the Provider Name in the RANKX function? The ALLSELECTED only accepts fields from the same table, but they are in separate tables.

Re: Filter based on a related column with RANKX

Mohammad_Refaei — Wed, 04 Aug 2021 12:53:23 GMT

Please try this:

TopN Confidence by ProviderId = VAR SelectedTop = SELECTEDVALUE ( 'TopN'[TopN] ) VAR SelectedOrder = SELECTEDVALUE ( FilterOrder[FilterOrder] ) VAR RankOrder = IF ( FilterOrder = "Top", 1, -1 ) VAR RankingScope = IF ( ISINSCOPE ( ProvidersTable[ProviderName] ), "ProviderName", "ProviderID" ) RETURN SWITCH ( TRUE (), SelectedOrder = "All", [Average Confidence], IF ( RankingScope = "ProviderID", RANKX ( FILTER ( ALLSELECTED ( 'Confidence'[ProviderId], 'Confidence'[Date] ), 'Confidence'[Date] = MAX ( SortByDate[Date] ) ), 'Confidence'[Average Confidence] * RankOrder ), RANKX ( FILTER ( ALLSELECTED ( 'ProvidersTable'[ProviderName], 'Confidence'[Date] ), 'Confidence'[Date] = MAX ( SortByDate[Date] ) ), 'Confidence'[Average Confidence] * RankOrder ) ) <= SelectedTop, 'Confidence'[Average Confidence] )

Hopefully it will work.

Re: Filter based on a related column with RANKX

OBMBI — Wed, 04 Aug 2021 13:43:36 GMT

Hi Mohammad_Refaei,

This does not work as the columns in the second ALLSELECTED are from different tables

All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED/REMOVEFILTERS function must be from the same table.

Re: Filter based on a related column with RANKX

Mohammad_Refaei — Wed, 04 Aug 2021 14:18:29 GMT

You are right, I missed the fact that we are using 2 tables.

Why don't you add a calculated column with the related provider name and use it in the same context?

Re: Filter based on a related column with RANKX

OBMBI — Fri, 06 Aug 2021 10:33:01 GMT

Because this is my fact table.

1. The table is huge a calculated column would impact performance too much

2. The Provider isn't the only thing I need to rank, there are multiple columns in various dimTables, this is just 1 example.