topic Re: Dax formula help in DAX Commands and Tips

Dax formula help

mcdora — Thu, 27 Jun 2019 17:53:38 GMT

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.

Re: Dax formula help

HotChilli — Thu, 27 Jun 2019 22:55:30 GMT

Please rephrase the question.

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

Re: Dax formula help

mcdora — Fri, 28 Jun 2019 15:00:15 GMT

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

Re: Dax formula help

Anonymous — Tue, 02 Jul 2019 09:57:45 GMT

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

Re: Dax formula help

mcdora — Tue, 02 Jul 2019 15:03:38 GMT

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

Re: Dax formula help

mcdora — Tue, 02 Jul 2019 15:21:06 GMT

I want it to look like this

Re: Dax formula help

Anonymous — Tue, 02 Jul 2019 15:25:23 GMT

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

Best

Darek

Re: Dax formula help

mcdora — Tue, 02 Jul 2019 15:30:05 GMT

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

Re: Dax formula help

Anonymous — Tue, 02 Jul 2019 16:10:06 GMT

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

Re: Dax formula help

mcdora — Fri, 19 Jul 2019 16:32:46 GMT

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.