Forum Discussion
Filter based on a related column with RANKX
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 r...
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.
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.
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?
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.