Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rank with three conditions

Hi All,    I have spent hours on this and yet to find a result that works, I need to be able to dynamically rank based on three conditions: Conditon 1 = Level Condition 2 = Semester / Term Condi...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Keelin,

     

    this is the measure:

    ms Rank = 
var NoOfResponsesThreshold = SELECTEDVALUE(Slicer[Disconnected Responses Slicer] , 10) 
return
IF(AND(HASONEVALUE('Fact'[Record]) , AND(HASONEVALUE('Fact'[Level]) , HASONEVALUE('Fact'[Semester / Term])))
    ,IF(CALCULATE(SUM('Fact'[Responses])) >= NoOfResponsesThreshold
        ,RANKX(
            FILTER(
                CALCULATETABLE(
                    ADDCOLUMNS(
                        SUMMARIZE(
                            'Fact', 'Fact'[Semester / Term] , 'Fact'[Level] , 'Fact'[Record]
                        )
                        , "NoOfResponses" , CALCULATE(SUM('Fact'[Responses]))
                    )           
                    , ALLEXCEPT('Fact' , 'Fact'[Semester / Term] , 'Fact'[Level])
                )
            , [NoOfResponses] >= NoOfResponsesThreshold
            )
            ,CALCULATE(SUM('Fact'[Result]))
            ,,DESC,Skip
        )
        , BLANK()
    )
)

    that allows to create this matrix visual

    Change the parameter from Skip to Dense if you like to see a 2 instead of a 3.

     

    Regards,

    Tom

Anonymous's avatar
Anonymous
Not applicable
6 years ago

TomMartens  Thanks so much for this, its actually the Resutls I want to rank according to the three conditions, I tried to modify your amazing DAX to no avail! 

 

I modified the sum responses to sum results and its bringing back a ranking,  I cant get it to bring back the exact ranking, e.g. I need it to start at 1  for Term 1, UG, Responses > 50. 

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
6 years ago

Hey Keelin,

 

this is the measure:

ms Rank = 
var NoOfResponsesThreshold = SELECTEDVALUE(Slicer[Disconnected Responses Slicer] , 10) 
return
IF(AND(HASONEVALUE('Fact'[Record]) , AND(HASONEVALUE('Fact'[Level]) , HASONEVALUE('Fact'[Semester / Term])))
    ,IF(CALCULATE(SUM('Fact'[Responses])) >= NoOfResponsesThreshold
        ,RANKX(
            FILTER(
                CALCULATETABLE(
                    ADDCOLUMNS(
                        SUMMARIZE(
                            'Fact', 'Fact'[Semester / Term] , 'Fact'[Level] , 'Fact'[Record]
                        )
                        , "NoOfResponses" , CALCULATE(SUM('Fact'[Responses]))
                    )           
                    , ALLEXCEPT('Fact' , 'Fact'[Semester / Term] , 'Fact'[Level])
                )
            , [NoOfResponses] >= NoOfResponsesThreshold
            )
            ,CALCULATE(SUM('Fact'[Result]))
            ,,DESC,Skip
        )
        , BLANK()
    )
)

that allows to create this matrix visual

Change the parameter from Skip to Dense if you like to see a 2 instead of a 3.

 

Regards,

Tom

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    TomMartens 

    This is absolute magic and so quick! I cant thank you enough and I have learned so much from your code. 

    I really appreciate this.