cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcdora
Frequent Visitor

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
HotChilli
Super User II
Super User II

Please rephrase the question.

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

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

mcdora
Frequent Visitor

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

Anonymous
Not applicable

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

View solution in original post

mcdora
Frequent Visitor

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
Frequent Visitor

DFS graph.png

 

I want it to look like this

 

Anonymous
Not applicable

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

 

Best

Darek

mcdora
Frequent Visitor

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors