cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcdora Frequent Visitor
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

Accepted Solutions
Highlighted
Super User
Super User

Re: Dax formula help

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

Re: Dax formula help

Please rephrase the question.

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

mcdora Frequent Visitor
Frequent Visitor

Re: Dax formula help

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

 

 

 

 

 

 

Super User
Super User

Re: Dax formula help

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

Re: Dax formula help

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

mcdora Frequent Visitor
Frequent Visitor

Re: Dax formula help

DFS graph.png

 

I want it to look like this

 

Super User
Super User

Re: Dax formula help

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

 

Best

Darek

mcdora Frequent Visitor
Frequent Visitor

Re: Dax formula help

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

Highlighted
Super User
Super User

Re: Dax formula help

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

Re: Dax formula help

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 29 members 1,552 guests
Please welcome our newest community members: