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
-- 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 )
mcdora's avatar
mcdora
Frequent Visitor
7 years ago

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.