Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Color cells based on conditions

Hello!

 

I have a matrix which I want to use as a roadmap with list of programs and the timeline in quarters. I want to color the roadmaps in different colors (I put numbers and then change colors in specific cells)  based on these conditions:
1. If the program is Smart Planning, then put 3

2. If the program is UNIFY, then put 4

3. If the program End Date is before Q1 2024, then put 2

4. For all Project Period, put 1.

Here is the measure I am using:

CF Gantt =
VAR StartDate =
CALCULATE(
    MIN('reset programs'[Start date]),
    REMOVEFILTERS(dimDate))
VAR EndDate =
CALCULATE(
    MIN('reset programs'[End date]),
    REMOVEFILTERS(dimDate))
VAR ProjectPeriod =
    MIN(dimDate[Date]) >= StartDate
    && MIN(dimDate[Date]) <= EndDate
VAR BeforeQ1_2024 = EndDate < DATE(2024, 1, 1)

VAR ProgramName = VALUES('reset programs'[Program])

VAR SmartPlanning = MIN('reset programs'[Program]) = "Smart Planning"
VAR UNIFY = MIN('reset programs'[Program]) = "UNIFY"

VAR Result =
IF (
    ProjectPeriod && SmartPlanning,
    3,  -- Color for Smart Planning
    IF (
        ProjectPeriod && UNIFY,
        4,  -- Color for UNIFY
        IF (
            ProjectPeriod && BeforeQ1_2024,
            2,  -- Color for Before Q1 2024
            IF (
                ProjectPeriod,
                1,  -- Default color
                BLANK()
            )
        )
    )
)
RETURN
    Result

 

 

But it puts numbers only on the end of the line, and I need to have numbers in the whole line when it's inside of Project Period:

 Please help me!

Need Help
Tips and Tricks

3 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    Anonymous 

     

    can you please change the nb with the colors #hexa   

     

    and then use it in the conditional formatting -- >  field value 

    then in the next dropdown select the measure .  

     

     

     

    let me know if it works for you . .

     

    if not,  it would helpful if you could please share your power bi file so i can take a closer look / 

     

     

    best regards

     

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi!
      thanks for your answer. Can you please share your email here so I can send my powerbi file to you?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi!
      I know how to change color. The problem is it doesn't show numbers (that I will change into colors) on the whole line within the ProjectPeriod for each program

      Like I need to have 4s filling the whole line where the blue boxes are on each line and not only in the end of each line. I hope it makes sense.
      Thanks!