conditionally highlight column IF certain category rows has a value

[Anonymous]

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

Sample data

Category	Item	Start Date	End Date
Project	Project 10	20/12/2021	21/12/2021
Project	Project 11	20/12/2021	31/12/2021
Key date	Christmas public holiday	25/12/2021	25/12/2021
Project	Project 12	28/12/2021	30/12/2021

Any ideas?

[v-jianboli-msft]

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]

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]

Awesome thanks, I'll see how I go!