Forum Discussion

mcdora's avatar
mcdora
Frequent Visitor
7 years ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    7 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

  • HotChilli's avatar
    HotChilli
    Community Champion

    Please rephrase the question.

    Can you provide sample data (not a picture) and your desired result.

    • mcdora's avatar
      mcdora
      Frequent Visitor
      RecordTypeSchoolStudentGrpAvgDFSLegendColors
      1Chaparral ElementaryWhite100ActualBlue
      1Chaparral ElementaryHispanic or Latino40.1ActualBlue
      1Chaparral ElementaryTwo or More Races24ActualBlue
      1Chaparral ElementaryEconomically Disadvantaged14.8ActualBlue
      1Chaparral ElementaryAll Students-21ActualYellow
      2Chaparral ElementaryWhite35ActualBlue
      2Chaparral ElementaryTwo or More Races16ActualBlue
      2Chaparral ElementaryHispanic or Latino-8ActualBlue
      2Chaparral ElementaryEconomically Disadvantaged-8.4ActualBlue
      2Chaparral ElementaryAll Students-37.2ActualYellow

       

      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")

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not 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