Forum Discussion
Dax formula help
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.
- Anonymous7 years ago
-- 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 )
9 Replies
- HotChilliCommunity Champion
Please rephrase the question.
Can you provide sample data (not a picture) and your desired result.
- mcdoraFrequent Visitor
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")
- AnonymousNot applicable
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