Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
)
Solved! Go to 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
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
@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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |