Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm tring to compare the Avg DFS for two different values found in a StudentGroup column to see which is larger. They are all in the same table as well as column. I'm comparing a value of a group of possible rows with one specific row.
Solved! Go to Solution.
-- This measure returns a color when only -- one record type and one student group -- is visible in the current context [Student Group Color] = var __studentGroup = SELECTEDVALUE( MyTable[StudentGrp] ) var __oneStudentGroupVisible = HASONEVALUE( MyTable[StudentGrp] ) var __oneRecordTypeVisible = HASONEVALUE( MyTable[RecordType] ) var __shouldDisplay = __oneStudentGroupVisible && __oneRecordTypeVisible var __recordType = SELECTEDVALUE( MyTable[RecordType] ) var __avgdfs = SELECTEDVALUE( MyTable[AvgDFS] ) var __allStudentsAvgdfs = CALCULATE( MAX( MyTable[AvgDFS] ); MyTable[RecordType] = __recordType, MyTable[StudentGrp] = "All Students", ALL( MyTable ) ) var __color = switch( true(), __studentGroup <> "All Students", if(__avgdfs > __allStudentsAvgdfs, "Green", "Red"), __studentGroup = "All Students", "Yellow" ) return if( __shouldDisplay, __color )
Please rephrase the question.
Can you provide sample data (not a picture) and your desired result.
RecordType | School | StudentGrp | AvgDFS | Legend | Colors |
1 | Chaparral Elementary | White | 100 | Actual | Blue |
1 | Chaparral Elementary | Hispanic or Latino | 40.1 | Actual | Blue |
1 | Chaparral Elementary | Two or More Races | 24 | Actual | Blue |
1 | Chaparral Elementary | Economically Disadvantaged | 14.8 | Actual | Blue |
1 | Chaparral Elementary | All Students | -21 | Actual | Yellow |
2 | Chaparral Elementary | White | 35 | Actual | Blue |
2 | Chaparral Elementary | Two or More Races | 16 | Actual | Blue |
2 | Chaparral Elementary | Hispanic or Latino | -8 | Actual | Blue |
2 | Chaparral Elementary | Economically Disadvantaged | -8.4 | Actual | Blue |
2 | Chaparral Elementary | All Students | -37.2 | Actual | Yellow |
I am trying to use a stacked bar chart to represent the numbers (using colors) by comparing the AvgDFS for all students to those in other student groups. I tried above the following Dax expresssion but can't quite get it right.
Colors = if ([StudentGrp] in {"Economically Disadvantaged", "Hispanic or Latino", "White”, "Two or More Races"} && [AvgDFS] > [AvgDFS] && [StudentGrp] = "All Students","Blue",
[StudentGrp] in {"Economically Disadvantaged", "White","Two or More Races"} && [AvgDFS] < [AvgDFS] && [StudentGrp] = "All Students","Yellow","Red")
Hi there.
The DAX is wrong but you know this already. The comparison [value] > [value] will always return FALSE.
Please state in words (as an algorithm) the exact logic you want to implement and I'll then turn this into DAX. I understand this Color you need is going to be a calculated column?
By the way, you have to take into consideration RecordType as well because for different RecordTypes different "All Students" means exist.
Thanks.
Best
Darek
I have different graphs for each record type and that's where i filter it.
-- This measure returns a color when only -- one record type and one student group -- is visible in the current context [Student Group Color] = var __studentGroup = SELECTEDVALUE( MyTable[StudentGrp] ) var __oneStudentGroupVisible = HASONEVALUE( MyTable[StudentGrp] ) var __oneRecordTypeVisible = HASONEVALUE( MyTable[RecordType] ) var __shouldDisplay = __oneStudentGroupVisible && __oneRecordTypeVisible var __recordType = SELECTEDVALUE( MyTable[RecordType] ) var __avgdfs = SELECTEDVALUE( MyTable[AvgDFS] ) var __allStudentsAvgdfs = CALCULATE( MAX( MyTable[AvgDFS] ); MyTable[RecordType] = __recordType, MyTable[StudentGrp] = "All Students", ALL( MyTable ) ) var __color = switch( true(), __studentGroup <> "All Students", if(__avgdfs > __allStudentsAvgdfs, "Green", "Red"), __studentGroup = "All Students", "Yellow" ) return if( __shouldDisplay, __color )
Thanks for the reply.
I think i need to become more familiar with Power BI. I still couldn't get it to work.
It's a table where more than one value is always visible.
I want it to look like this
What happens when you don't filter by RecordType? Do the averages get summed up for each StudentGrp?
Best
Darek
I want to compare the DFS from "All Students" and the other Student Groups. I created a visual and wanted to show the All Students in Yellow, if the DFS number for the other student groups is higher than the All Students I would like it Blue.
If the DFS number is less than the All Students number, I'd like it R ed. I even thought of either hard coding it or putting the All Students into a different table.
ex: If DFS from "White" > DFS from All Students then BLue,
if DFS from Economically Disadvantaged < DFS from All Students, then Red. DFS from All Students should be yellow
Hope I explained it right.
thanks,
Becky