Rank with three conditions

[Anonymous]

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

Condition 3 = Number of responses (greater than or equal to value on disconnected slicer)

The data looks like this:

Record	Level	Semester / Term	Responses	Result
Record 1	UG	Term 1	4	6.00
Record 2	UG	Term 1	1	6.00
Record 3	UG	Term 1	8	6.00
Record 4	UG	Term 1	18	5.75
Record 5	UG	Term 1	26	5.75
Record 6	UG	Term 1	15	5.60
Record 7	PG	Term 1	82	5.56
Record 8	PG	Term 1	4	5.50
Record 9	PG	Term 1	24	5.43
Record 10	PG	Term 2	49	5.28
Record 11	PG	Term 2	51	5.20
Record 12	PG	Term 2	77	5.17
Record 13	PG	Term 2	234	5.16
Record 14	UG	Term 2	176	5.15
Record 15	PG	Term 2	19	5.15
Record 16	UG	Term 2	65	5.11
Record 17	PG	Term 2	167	5.09
Record 18	PG	Term 2	278	5.08
Record 19	UG	Term 2	378	5.08
Record 20	PG	Term 2	495	5.01

Disconnected Responses Slicer:

10

20

50

100

my last test looks like this, but I get the error that there were too many arguments for the RANX function. 

I have tried summarize but havent managed to get it working. 

Would really, really appreciate help with this

_Test 5 = 

VAR Selected = SELECTEDVALUE(Ranking_Summary_Table_Course[Level])

VAR Term = SELECTEDVALUE(Ranking_Summary_Table_Course[Semester / Term])

RETURN

RANKX (

 FILTER (ALL(Ranking_Summary_Table_Course), 

 'Ranking_Summary_Table_Course'[Responses]>= [Selected_Responses] &&

 'Ranking_Summary_Table_Course'[Level] = Selected),

 FILTER(Ranking_Summary_Table_Course,

 Ranking_Summary_Table_Course[Semester / Term] = Term),


 CALCULATE (

 [Sum_Result],

 ALLEXCEPT('Ranking_Summary_Table_Course','Ranking_Summary_Table_Course'[Record])

 ),

 ,

 DESC,

 DENSE

)

[TomMartens]

Hey,

this 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(
            CALCULATETABLE(
                ADDCOLUMNS(
                    SUMMARIZE(
                        'Fact', 'Fact'[Semester / Term] , 'Fact'[Level] , 'Fact'[Record]
                    )
                    , "NoOfResponses" , CALCULATE(SUM('Fact'[Responses]))
                )            
                ,ALLEXCEPT('Fact' , 'Fact'[Semester / Term] , 'Fact'[Level])
            )
            ,CALCULATE(SUM('Fact'[Responses]))
            ,,DESC,Skip
        )
        , BLANK()
    )
)

allows to create something like this:

Hopefully this provides what you are looking for.

Regards,

Tom

[Anonymous]

@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 · Accepted solution]

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]

@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.