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
Anonymous
Not applicable

conditionally highlight column IF certain category rows has a value

Hello! I am trying to make a Gantt chart with a matrix visual. (Free Gantt charts don't meet my needs)

I need help with some DAX, if it is even possible!

 

I want to highlight a whole column to show when a public holiday is. So the challenge is conditionally highlighting a cell when another cell in that column is also highlighted.

 

I have a measure [items in period] based on this blog https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/, to return a one if an item occurs on a date, based on the items start and end date.

 

I also have a measure to use for conditional formatting in the matrix. It colours based on if the item is on that date (2=blue in my picture) and  if it is a key date (1=orange in my picture).
 

dynamic background =

SWITCH( TRUE(),

    SELECTEDVALUE(Category[Category]) = "Key date", 1,

    [Items in period] >= 1, 2,

    99

   )


What I want to do is highlight the "Key date" in the other rows, if there is not an item in there. This the yellow (yellow =3) cells in my desired output.

Desired output

aaahello_0-1658366420194.png

 


Sample data

CategoryItemStart DateEnd Date
ProjectProject 1020/12/202121/12/2021
ProjectProject 1120/12/202131/12/2021
Key dateChristmas public holiday25/12/202125/12/2021
ProjectProject 1228/12/202130/12/2021


Any ideas?

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

Hi aaahello,

 

Sorry for that the information you have provided is not making the problem clear to me. Can you please tell me how does the [items in period] calculate and when to use the pink color?

Please provide me with more details about your data and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

lbendlin
Super User
Super User

If you want to report on things that are not there then you need disconnected tables (with all potential dates) and cartesian products (with all category items).  Then you can calculate a measure for each of the cells of your cartesian based on the key date logic.

Anonymous
Not applicable

Awesome thanks, I'll see how I go!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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