How to highlight the different columns based on a column that has list of columns using power bi?

[KKalyaNB]

Hello All,

I'm trying to complete one enhancement request for the current report. We are currently having a table visual with some 50 columns in it, there is a column called CheckFailed, that column has list of column has issues and those column names are seperated by comma (,). So the enhancement request is based on the values present in checkfailed they want to highlight the columns background for that row.  

I previously did conditional formating but mostly on numerical data but not on strings. So I'm not sure how to do this.

Can any one please help me with this?

Here is the example:

GIVEN DATA:

VIEWNAME	CheckFailed	UOS_QUANTITY	LINE_DESC	SERVICE_CD	PRIME_DESC	DIAG_DESC
claim_detail	UOS_QUANTITY, LINE_DESC	23	NULL		NULL	NULL
claim_detail	SERVICE_CD, UOS_QUANTITY	15	NULL	11	NULL	NULL
claim_detail	UOS_QUANTITY, PRIME_DESC, DIAG_DESC	10	NULL	NULL	NULL	NULL

EXPECTED RESULT:

VIEWNAME	CheckFailed	UOS_QUANTITY	LINE_DESC	SERVICE_CD	PRIME_DESC	DIAG_DESC
claim_detail	UOS_QUANTITY, LINE_DESC	23	NULL		NULL	NULL
claim_detail	SERVICE_CD, UOS_QUANTITY	15	NULL	11	NULL	NULL
claim_detail	UOS_QUANTITY, PRIME_DESC, DIAG_DESC	10	NULL	NULL	NULL	NULL

Regards,

Kalyan

[KKalyaNB]

Hello @kpost , is it possible to use one measure with Switch function? Reason for asking is I have more than 50 columns in a dataset I don't want to create 50 measures, that will impact the performance of the report. Instead can I use something like this:

SWITCH(
    TRUE(),
    CONTAINSSTRING('Table'[VIEWNAMECheckFailed], "LINE_DESC" ), 1,
    CONTAINSSTRING('Table'[VIEWNAMECheckFailed], "UOS_QUANTITY" ), 1, 
    etc.. for remaining columns
).

[kpost]

It may be annoying for the human developer (us) but the DAX engine would process it similarly either way.  And even if you were able to create a single measure, it would still need to be evaluated the same number of times.  Having 50 measures or 1, there would be the same number of evaluations of that measure.  Once for every cell in the table.

To have this figured it out in 1 measure rather than 50, you'd have to somehow include logic that figures out the column name and then include that in the logic, like this.  Note that in your switch statement, the format measure would not know which column it was in, so it would end up applying the same exact logic to every cell regardless of which column it was in.

Cell Format =

var rowname = SELECTEDVALUE('Table'[VIEWNAMECheckFailed])

var column_name = ??????

RETURN
IF(CONTAINSSTRING(rowname, column_name), 1, 0)

No switch statement would be necessary.

As far as I know, there is no way for a measure to detect the name of the column in which it finds itself.  (the ????? part).  For what it's worth, my original response got a "kudo" from a microsoft support account, so I think I'm probably on the right track.

But if you do find a way to do it, this is how it should look. 

[kpost · Accepted solution]

You'll need to create a custom format measure for each column.

Here's one for LINE_DESC column.

LINE_DESC_FORMAT =

var rowname = SELECTEDVALUE('Table'[VIEWNAMECheckFailed])

RETURN

IF(CONTAINSSTRING(rowname, "LINE_DESC"), 1, 0)

then you'll format that column using conditional formatting on the font or background.  (In the example you posted, you made the font red but you said background.  So just select whichever one you meant).

And then use rules-based conditional formatting with that measure.  If the measure you created = 1, then the font should be red

 And here's the result:

 

You'll need to make a measure like this for each column.

See attached .pbix file for implemented solution.

[KKalyaNB]

Thank you for the provided solution.. will try to implement this.