Forum Discussion

czaldumbide's avatar
czaldumbide
Helper II
2 years ago
Solved

Add Ranking Column with filters

Hi Everyone,   I need some help creating an index column using DAX.  I have a table named ACCOUNTEXCEPTION with columns [ID] , [Key] and [Status]. I first want to create a ranking column, that sort...
"DAX help"
DAX FILTER ISSUES
Dynamic Ranking
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Order by key CC = 
VAR _firstid =
    MINX (
        FILTER (
            ACCOUNTEXCEPTION,
            ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] )
        ),
        ACCOUNTEXCEPTION[ID]
    )
VAR _condition =
    COUNTROWS (
        FILTER (
            ACCOUNTEXCEPTION,
            ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] )
                && ACCOUNTEXCEPTION[ID] = _firstid
                && ACCOUNTEXCEPTION[Status] = "Research"
        )
    ) = 1
RETURN
    IF (
        _condition,
        SUMX (
            FILTER (
                ACCOUNTEXCEPTION,
                ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] )
                    && ACCOUNTEXCEPTION[ID] <= EARLIER ( ACCOUNTEXCEPTION[ID] )
            ),
            1
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

 

Order by key CC = 
VAR _firstid =
    MINX (
        FILTER (
            ACCOUNTEXCEPTION,
            ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] )
        ),
        ACCOUNTEXCEPTION[ID]
    )
VAR _condition =
    COUNTROWS (
        FILTER (
            ACCOUNTEXCEPTION,
            ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] )
                && ACCOUNTEXCEPTION[ID] = _firstid
                && ACCOUNTEXCEPTION[Status] = "Research"
        )
    ) = 1
RETURN
    IF (
        _condition,
        SUMX (
            FILTER (
                ACCOUNTEXCEPTION,
                ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] )
                    && ACCOUNTEXCEPTION[ID] <= EARLIER ( ACCOUNTEXCEPTION[ID] )
            ),
            1
        )
    )
czaldumbide's avatar
czaldumbide
Helper II
2 years ago

Thanks for the solution. This worked perfectly.