Conditional Formatting required for 50 columns

[AnkitKukreja]

Hi! All,
My client requires to compare the data as there has been a system update and the logic has been built, I'm stuck on one of the asks to end this project. I have around 40-50 columns in the visual and the requirement is to highlight the value of those colums which are coming up in "Column_Differences". Like ChvTransferPrice for first few rows so only this column should be highlighted that I have in the middle of the table (2nd ss). Similarly for other columns like in 3 row , I need to highlight ChvTransferPrice and PerUnitprice in the table. I have all the columns in the table. Is there any simple approach to achieve this? 
I know a manual approach to add the condition to the conditional formatting, but that requires lot of manual efforts for 70 columns.

[v-pnaroju-msft · Accepted solution]

Hi AnkitKukreja,

Based on my understanding, to avoid the need for manually creating calculated columns for all 40 to 50 columns, you can utilize a more efficient approach with Power Query and a single dynamic measure.

1. Open Power Query Editor and access it via Transform Data in Power BI Desktop. Add a custom column (for example: HighlightFlag) with a formula such as:
   
   if Text.Contains([Column_Differences], Text.Lower(Text.From([ColumnName]))) then "Highlight" else null
   
   Replace  [ColumnName] with the name of the column being evaluated (for instance:ChvTransferPrice). Use Power Query’s Add Column > Custom Column feature and apply this logic across all columns by looping through them. 
    

2. Create a Dynamic Measure.
   DynamicHighlight =
   IF(
   SELECTEDVALUE('Table'[HighlightFlag]) = "Highlight",
   1,
   0
   )

   In the table visual, apply conditional formatting to all columns. Navigate to the Format pane > Conditional Formatting > Background Color > Field Value > Select DynamicHighlight. Set the value of 1 to your desired highlight color (for example: yellow) and 0 to the default color.

3. Test the Visual.Ensure that columns such as ChvTransferPrice are highlighted correctly based on Column_Differences. Save and publish to Microsoft Fabric.
   
   

   If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may be facing similar queries.

   Thank you.

[v-pnaroju-msft]

Hi @AnkitKukreja,

Thank you for your inquiry through the Microsoft Fabric Community Forum.

To address the issue you are facing, please follow the approach outlined below, which may assist in resolving it:

1. For each column you wish to format (for example, ChvTransferPrice), please create a calculated column in the following manner:
   Highlight_ChvTransferPrice =

   IF(SEARCH("ChvTransferPrice", [Column_Differences], 1, 0) > 0, 1, 0)

2. Apply Conditional Formatting. Within the visual, navigate to the Format Pane → Cell Elements → Background Color. Choose "Format by" → "Field value" and set it to the corresponding Highlight_ColumnName field.
   
   

   If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will aid other community members encountering similar queries.

   Thank you.

[AnkitKukreja]

Hi! @v-pnaroju-msft 

Thanks for your reply, My ask is do I need to do this for all the columns (40-50) that I have? Or is there any shortcut to achieve this? And what logic I can use for all the columns?