Filter based on a related column with RANKX

[OBMBI]

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

[Mohammad_Refaei]

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.

[OBMBI]

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.

[Mohammad_Refaei]

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?

[OBMBI]

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.

[amitchandak]

@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

[OBMBI]

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.