cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kyrpav
Helper IV
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

 

idmetriccolororderoperatorvalue

1

1

red1LESS0.8
21amber2LESS THAN EQUAL0.9
31green3BIGGER0.9
42red1LESS10
52amber2LESS THAN EQUAL13
62green3BIGGER13

 

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

2 REPLIES 2
v-xicai
Community Support
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.

 

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.

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)

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.