Forum Discussion

OBMBI's avatar
OBMBI
Frequent Visitor
5 years ago

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

 

DAX
RANKX
TopN

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      OBMBI
      Frequent Visitor
      5 years ago

      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.

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Solution Specialist
    5 years ago

    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's avatar
      OBMBI
      Frequent Visitor
      5 years ago

      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's avatar
        Mohammad_Refaei
        Solution Specialist
        5 years ago

        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?