Power BI DAX: Use the Criteria Table to get Color for Measure Value

[Anonymous]

FactMeasures: Table has sales CustomersCount details by each Quarter and Geo wise.

MeasureName	|Geo	|Measure Value	|Quarter
Sales	AMS	$2500	24-Q1
Sales	ASIA	$1500	24-Q1
Sales	EROPE	$1001	24-Q1
CustomersCount	AMS	551	24-Q1
CustomersCount	ASIA	149	24-Q1
CustomersCount	EROPE	280	24-Q1

Status_Color: Table will be having the criteria for each Measure (i.e. for Sales and CustomersCount different conditions) and based on criteria the Color will be assigned.

MeasureName	| MeasureUnit	| RangeStart_Sign	| RangeStart_Val	| RangeEnd_Sign	| RangeEnd_Val	| Status_Color
Sales	Decimal	>	0	<=	2500	Red
Sales	Decimal	>	2500	<=	5000	Yellow
Sales	Decimal	>	5000	null	null	Green
% YoYSales	Percentage	>	-100%	<=	25%	Red
% YoYSales	Percentage	>	25%	<=	50%	Yellow
% YoYSales	Percentage	>	50%	<=	100	Green
CustomersCount	Number	>	0	<=	1000	Red
CustomersCount	Number	>	1000	<=	5000	Yellow
CustomersCount	Number	>	5000	null	null	Green

Out put in Matrix expected is Measures and it’s values will be showed by Selected Quarter.  And the Values Color should be based on the Criteria in Status_Color Table.

MeasureName	| 24-Q1	| Color of Value should be
Sales	5001	Green
% YoYSales	44%	Yellow
CustomersCount	980	Red

This has to be achieved using DAX Query Measures only.  The report doesn’t have choice to change tables data or use Power Query to arrive.  Because of the Measure values are decided by User filter criteria, so dynamically the Measure value colors should apply based on Status_Color Table.

Could you please suggest, how the measure need to be created to follow Status_Color Table to arrive Font color on the Output Table ?  Let me know if you need any further information.

[MFelix]

Hi @Anonymous ,

You must create a measure similar to this one:

Format = 
    VAR MeasureTotal = SUM('Fact'[Measure Value])
	VAR Formatting = MAXX(
		TOPN(
			1,
			FILTER(
				'Format',
				'Format'[MeasureName] = SELECTEDVALUE('Fact'[MeasureName]) && 'Format'[RangeStart_Val] <= MeasureTotal
			),
			'Format'[RangeStart_Val]
		),
		'Format'[Status_Color]
	)
	RETURN
		Formatting

Be aware that you need to change the percentages to a decimal number and not a % value otherwise you will get text format on your table.

[Anonymous]

Hi @MFelix , 

Thanks for your reply.

Just one point to add here.
We should consider Range_Start_Sign, Range_Start_Value, Range_End_Sign & Range_End_Val fileds to make the condition to get the Status Color.

It is not always ">" value in Range_Start_Sign and "<=" Value in Range_End_Sign. So always need to consider these things to get the Status_color Value.


Could you please add these into consideration?

[MFelix]

This depends on the logic we applied in this case since we are doing a TOPN an picking up the first column sorted descending we always pick the correct result please see the examples below:

[Anonymous]

Hi @MFelix ,

Really appreciate your efforts.

Based on the DAX your provided, I seen two things will be missing. Could you please let me know how to add those?

• Only Start Range Conidtion is applied,  we also need t add Range End Condition.
• We should use RangeStart_Sign  and RangeEnd_Sign to make condition dynamic
  
  
  

[MFelix]

Hi @Anonymous ,

Sorry for the question but have you tried the calculation on your measures, and have you gotten incorrect results?

Also sorry for insisting, but the impact of the EndRange would only be significant if your ranges would be overlapping meaning that you could have one value that could be in two ranges only considering the start range in this case, since your ranges are starting were the previous one ended there is no need to include the End.

The dynamic part of the condition comes from the fact that we are comparing our start with the Values of the measure so when the calculation changes the filter context is changed and a new value is returned.