Dax formula help

[mcdora]

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.

[HotChilli]

Please rephrase the question.

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

[mcdora]

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

[Anonymous]

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]

I have different graphs for each record type and that's where i filter it. 

[Anonymous · Accepted solution]

-- 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]

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.

[mcdora]

I want it to look like this

[Anonymous]

What happens when you don't filter by RecordType? Do the averages get summed up for each StudentGrp?

Best

Darek

[mcdora]

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