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
swissman
New Member

Background color - What field should we base this on?

Hi

In the MS10 column of a report table, I want to highlight all fields that have the value blank in red. The MS10 column is therefore the input for 'What field should we base this on?' but not accepted. Why is that and what do I have to change?

The MS10 column is defined as follows:

MS10 =

    Var LatestDate=CALCULATE(MAX(Cockpit[Exportdatum]),Cockpit[MS10]<>0,Cockpit[MS10]<>BLANK())

    Var BeginDate=CALCULATE(MAX(Cockpit[Exportdatum]),Cockpit[Beginn]<>0,Cockpit[Beginn]<>BLANK())

    Return IF(LatestDate<>BLANK(),CALCULATE(MAX(Cockpit[MS10]),Cockpit[Exportdatum]=LatestDate),CALCULATE(MAX(Cockpit[Beginn]),Cockpit[Exportdatum]=BeginDate))

 

Many thanks!

2 ACCEPTED SOLUTIONS
MAwwad
Super User
Super User

 

It's not possible to directly apply conditional formatting based on a calculated column in Power BI. However, there is a workaround you can try.

Create a measure that returns 1 if the MS10 column is blank and 0 otherwise. For example:

 

scssCopy code
BlankMS10 = IF( ISBLANK([MS10]), 1, 0 )

 

Then, use this measure as the basis for the conditional formatting. In the formatting options for the table visual, select the MS10 column and choose "Conditional formatting" > "Background color" > "Advanced controls". In the "Format by rules" tab, choose "Field value" as the "Type", select the "BlankMS10" measure as the "Based on field", and set the "Background color" to red.

This should highlight all rows where the MS10 column is blank in red.

View solution in original post

If I choose "Field value" the measure BlankMS10 is not accepted as the basis. I have to choose "Rules" and then the measure will be accepted as the basis and the conditional formatting (If value = 1 as a number) works then fine as desired. 

View solution in original post

4 REPLIES 4
MAwwad
Super User
Super User

 

It's not possible to directly apply conditional formatting based on a calculated column in Power BI. However, there is a workaround you can try.

Create a measure that returns 1 if the MS10 column is blank and 0 otherwise. For example:

 

scssCopy code
BlankMS10 = IF( ISBLANK([MS10]), 1, 0 )

 

Then, use this measure as the basis for the conditional formatting. In the formatting options for the table visual, select the MS10 column and choose "Conditional formatting" > "Background color" > "Advanced controls". In the "Format by rules" tab, choose "Field value" as the "Type", select the "BlankMS10" measure as the "Based on field", and set the "Background color" to red.

This should highlight all rows where the MS10 column is blank in red.

If I choose "Field value" the measure BlankMS10 is not accepted as the basis. I have to choose "Rules" and then the measure will be accepted as the basis and the conditional formatting (If value = 1 as a number) works then fine as desired. 

Many thanks! I'll try this workaround.

Dont forget to accept this as a solution if it helped you in order to help others

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.