Forum Discussion

Krishnan_47's avatar
Krishnan_47
Helper I
4 years ago
Solved

Rank by Grouping and Filtering using DAX Measure

Hi All,

 

I am trying to create a ranking measure to sort numbers by ascending order. I have two tables:
Table 1: Date and Sub Asset  columns.
Table 2: Asset Class, Sub Asset and Value columns.

I have also created a relationship mapping between the two tables (Sub Asset)


When user selects Jan 2021 as date selection, I want to show a table as shown in the below screenshot with DAX measure grouping Rank Value based on the Asset and Sub Asset Classes.


Creating a ranking measure is not looking straight forward, hence asking your help to find a solution.

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Krishnan_47 

    Try this, creat the measure

    Rank =
VAR _r =
    RANKX (
        FILTER (
            ALLSELECTED ( Table2 ),
            Table2[Asset Class] = MIN ( Table2[Asset Class] )
        ),
        CALCULATE ( SUM ( Table2[Value] ) ),
        ,
        ASC
    )
RETURN
    IF (
        ISFILTERED ( Table1[Date] ),
        IF ( MIN ( Table2[Sub Asset] ) IN VALUES ( Table1[Sub Asset] ), _r, BLANK () ),
        _r
    )

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Can help with the rank measure however can you share how your data will look in future months? My first thought is that the date should be on Table 2 as it needs to be associated with the Value but may be missing something.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi Krishnan_47 

    Try this, creat the measure

    Rank =
VAR _r =
    RANKX (
        FILTER (
            ALLSELECTED ( Table2 ),
            Table2[Asset Class] = MIN ( Table2[Asset Class] )
        ),
        CALCULATE ( SUM ( Table2[Value] ) ),
        ,
        ASC
    )
RETURN
    IF (
        ISFILTERED ( Table1[Date] ),
        IF ( MIN ( Table2[Sub Asset] ) IN VALUES ( Table1[Sub Asset] ), _r, BLANK () ),
        _r
    )

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.