Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

 


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

  • 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's avatar
      Anonymous
      Not applicable

      Awesome thanks, I'll see how I go!

  • 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.