Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KKalyaNB
New Member

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

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:

VIEWNAMECheckFailedUOS_QUANTITYLINE_DESCSERVICE_CDPRIME_DESCDIAG_DESC
claim_detailUOS_QUANTITY, LINE_DESC23NULL 

NULL

NULL

claim_detailSERVICE_CD, UOS_QUANTITY15NULL11NULLNULL
claim_detailUOS_QUANTITY, PRIME_DESC, DIAG_DESC10NULLNULLNULLNULL

 

EXPECTED RESULT:

VIEWNAMECheckFailedUOS_QUANTITYLINE_DESCSERVICE_CDPRIME_DESCDIAG_DESC
claim_detailUOS_QUANTITY, LINE_DESC23NULL 

NULL

NULL

claim_detailSERVICE_CD, UOS_QUANTITY15NULL11NULLNULL
claim_detailUOS_QUANTITY, PRIME_DESC, DIAG_DESC10NULLNULLNULLNULL

 

Regards,

Kalyan

1 ACCEPTED SOLUTION
kpost
Super User
Super User

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

 

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

 And here's the result:

 

result.PNG


 

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

 

See attached .pbix file for implemented solution.

 

View solution in original post

4 REPLIES 4
KKalyaNB
New Member

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

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
Super User
Super User

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

 

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

 And here's the result:

 

result.PNG


 

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

 

See attached .pbix file for implemented solution.

 

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.