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 have different graphs for each record type and that's where i filter it. 

  • 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.