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.
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
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 |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |