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 ...
  • 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 )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

mcdora's avatar
mcdora
Frequent Visitor
7 years ago

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