cancel
Showing results for
Did you mean:  Helper IV

## custom cell formating measure by row in matrix

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

2 REPLIES 2  Community Support

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.

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.  Helper IV

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