custom cell formating measure by row in matrix

[kyrpav]

I have a table with metrics and their thresold in different rows data example is like this

id	metric	color	order	operator	value
1	1	red	1	LESS	0.8
2	1	amber	2	LESS THAN EQUAL	0.9
3	1	green	3	BIGGER	0.9
4	2	red	1	LESS	10
5	2	amber	2	LESS THAN EQUAL	13
6	2	green	3	BIGGER	13

I have a measure that for each metric calculates a different value with a switch (this works) named MetricMes.

I want to use this measure to check it among the thresolds of the metric and get back the color to apply it to conditional formating. This means that measure can calculate values on matrix where in rows we have metrics and in columns different dates.

Since the thresolds are ordered for each metric ( and this ordering is done on import) then i was thinking on calculating a column where i could calculate for each thresold a value of Blank on False and metric color on True. For each thresold a switch could be used to undrestand from the operator what to be checked like if operator is Bigger this means that check is : 

MetricMec>value (of the specific thresold)

Then on this column i was thinking to use the firstnonblank function to get the first value that succeeded and send it to conditional formating.

Until now i am doing this:

formating_cells=
var metric_id= CALCULATE(SELECTEDVALUE(metrics[id]);FILTER(metrics;ALLSELECTED(metrics[id])))
var index2= ADDCOLUMNS(metrics;"Metric_selected";SWITCH(VALUES(metrics[operator]);
                "LESS"; if ([MetricMes]< LOOKUPVALUE(metrics[value];metrics[id];metric_id);metric_id;blank());
                "LESS THAN EQUAL"; if ([MetricMes]<= LOOKUPVALUE(metrics[value];metrics[id];metric_id);metric_id;blank());
                "EQUAL"; if ([MetricMes]= LOOKUPVALUE(metrics[value];metrics[id];metric_id);metric_id;blank());
                "GREATER"; if ([MetricMes]> LOOKUPVALUE(metrics[value];metrics[id];metric_id);metric_id;blank());
                "GREATER THAN EQUAL"; if ([MetricMes]>= LOOKUPVALUE(metrics[value];metrics[id];metric_id);metric_id;blank());
                "NOT EQUAL"; if ([MetricMes]<> LOOKUPVALUE(metrics[value];metrics[id];metric_id);metric_id;blank());
                BLANK()))
var col=SELECTCOLUMNS(index2;"thecol";[Metric_selected])
var nonblankid= FIRSTNONBLANKVALUE(col;ISBLANK([thecol])) 
result=LOOKUPVALUE(metrics[color];v_study_kpi_metrics[id];nonblankid)

Is there any possible solution to syntax for this calculation of columns and filtering cause it seems that col variable is not a column?

I get error that first Argument of Firstnonblankvalue can only contain a single column

[v-xicai]

Hi @kyrpav ,

First of all, you may make some changes for the last two rows in formula to escape the error message above. For the Conditional Formatting, you may refer to the article: Use conditional formatting in tables.

Correct syntax:



var nonblankid= FIRSTNONBLANKVALUE([col],1 ) 
Return

LOOKUPVALUE(metrics[color],v_study_kpi_metrics[id],nonblankid)

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kyrpav]

Well my problem is that [col] is not a column but a table with one column and i get the same error.

But i did make a workaround using table.

• I created a crossjoin table with my metrics table and the calendar dates (running year) that i need.

Metrics Table Raw Data = CROSSJOIN(metrics;FILTER(VALUES(calendar_reference[date]);calendar_reference[date]>=TODAY()-365 && calendar_reference[date]<TODAY()))

• I calculated in the table the value of the metric for each one of the thresolds in a new column

metric_value = SWITCH([metric_code];
            "code1";[metric1];
            "code2";[metric2];
            "code3";[metric3];
            "code4";[metric4];
            "code5";[metric5];
            "code6";[metric6];
            BLANK())

• I calculated the conditional formating color for each one of the thresold in a new column

conditional_formating = SWITCH([thresold_operator];
                "LESS"; if ([metric_value]<[thresold_value];'Metrics Table Raw Data'[thresold_hex_color];blank());
                "LESS THAN EQUAL"; if ([metric_value]<=[thresold_value];'Metrics Table Raw Data'[thresold_hex_color];blank());
                "EQUAL"; if ([metric_value]=[thresold_value];'Metrics Table Raw Data'[thresold_hex_color];blank());
                "GREATER"; if ([metric_value]>[thresold_value];'Metrics Table Raw Data'[thresold_hex_color];blank());
                "GREATER THAN EQUAL"; if ([metric_value]>=[thresold_value];'Metrics Table Raw Data'[thresold_hex_color];blank());
                "NOT EQUAL"; if ([metric_value]<>[thresold_value];'Metrics Table Raw Data'[thresold_hex_color];blank());
                BLANK())

• and last i created a measure that brings back the firstnonblank([conditional_formating];1)

Metrics Conditional Formating = FIRSTNONBLANK('Metrics Table Raw Data'[conditional_formating];1)