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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
chiru5262
Helper III
Helper III

Changing the background colour for Matrix visual column having no value for todays date.

Hi

 

I would like to change the background colour of Matrix visual column if the date is today and irrespective of the column as a value or not.

 

Please advise, appreciate your help,

 

Kind Regards,

Chiru

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @chiru5262 ,

 

In your scenario, conditional formatting cannot work on blank value, so we need to do this:

Sample data:

sampledata.PNG

 

1. Create table tables.

 

Category = DISTINCT('Table'[Category])
Dates = CALENDAR(DATE(2020,8,1),DATE(2020,8,31))

 

 

2. Create relationships among the three tables.

relation.PNG

 

3. Create a Martix visual.

matrix.PNG

 

4. Create a measure.

 

1 = IF(MAX(Dates[Date])=TODAY(),1)

 

 

5. Set conditional formatting.

matrix cond.PNG

 

We can find that when value is blank, conditonal formatting doesn't work. So, we need to put the Measure "1" to the matrix, too.

matrix cond2.PNG

 

It works now. Then, we can hide this measure. Try this:

matrix cond3.PNG

 

Next, we can also try to hide to column name of "Value".

matrix cond4.PNG

 

In addition, regarding why we need to create another Dates table, I think this screenshot can explain:

matrix cond5.PNG

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

Hi @chiru5262 ,

 

In your scenario, conditional formatting cannot work on blank value, so we need to do this:

Sample data:

sampledata.PNG

 

1. Create table tables.

 

Category = DISTINCT('Table'[Category])
Dates = CALENDAR(DATE(2020,8,1),DATE(2020,8,31))

 

 

2. Create relationships among the three tables.

relation.PNG

 

3. Create a Martix visual.

matrix.PNG

 

4. Create a measure.

 

1 = IF(MAX(Dates[Date])=TODAY(),1)

 

 

5. Set conditional formatting.

matrix cond.PNG

 

We can find that when value is blank, conditonal formatting doesn't work. So, we need to put the Measure "1" to the matrix, too.

matrix cond2.PNG

 

It works now. Then, we can hide this measure. Try this:

matrix cond3.PNG

 

Next, we can also try to hide to column name of "Value".

matrix cond4.PNG

 

In addition, regarding why we need to create another Dates table, I think this screenshot can explain:

matrix cond5.PNG

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks mate, it worked!

pranit828
Community Champion
Community Champion

Hi @chiru5262 

 

I would create a conditional column as assign it a value 1 if it is today otherwise 0.

On the matrix visual I will conditionally advance format each column. Yes, you have to do it for each column.

pranit828_0-1597364521214.png

pranit828_1-1597364621596.png

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit

 

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Hi Pranit,

 

Thank you for the reply.

Appreciate if you can provide me  the rule for conditional column , as I want the column background colour to be shown only for todays date with value or without value.

 

Regards,

Chiru

Hi @chiru5262 

 

I would use the below for creating a column in the data section of power bi.

 

Is_Today = IF(DATEDIFF('Track Recording'[Date],TODAY(),DAY)=0,1,0)
 

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.