Forum Discussion

Tobz007's avatar
Tobz007
Frequent Visitor
2 years ago
Solved

Matrix Table Background Conditional Formatting

Hello Community, I have a matrix table that uses a parameter for the rows, the parameter contains 4 hierachies. The columns are based on weeks e.g Jan 1 - 7, Jan 8 - 15, etc. I am trying to conditio...
General Comment
Need Help
  • Tobz007's avatar
    Tobz007
    2 years ago

    For anyone interested in how I got it to work, this was the final DAX

    Colour Format =

    VAR rStart = SELECTEDVALUE(Table[ResourceStart])
    VAR rEnd = SELECTEDVALUE(Table[ResourceEnd])

    VAR weekStart = SELECTEDVALUE(Table[WeekStart])
    VAR weekEnd = SELECTEDVALUE(Table[WeekEnd])


    VAR _Result =
         SWITCH(
             TRUE(),
                  rStart >= weekStart && rStart <= weekEnd && rEnd >= weekStart && rEnd <= weekEnd, "#FFA07A",
                  rStart >= weekStart && rStart <= weekEnd , "#ADD8E6",
                  rEnd >= weekStart && rEnd <= weekEnd , "#006884",
              " "
          )

    RETURN
          _Result

DatawithDinesh's avatar
DatawithDinesh
Icon for Resolver II rankResolver II
2 years ago

The issue you're encountering is likely due to the aggregation in the matrix. When you use MIN to calculate the start and end dates, it might not be accurately reflecting the multiple occurrences of a project that span different weeks. To address this, you need to ensure that each cell in the matrix is independently evaluated for the conditions you specify.

Here’s a revised approach that should help you achieve the desired formatting:

Colour Format = 
VAR rStart = SELECTEDVALUE(Table[ResourceStart])
VAR rEnd = SELECTEDVALUE(Table[ResourceEnd])

VAR weekStart = SELECTEDVALUE(Table[WeekStart])
VAR weekEnd = SELECTEDVALUE(Table[WeekEnd])

VAR rStartInWeek = rStart >= weekStart && rStart <= weekEnd
VAR rEndInWeek = rEnd >= weekStart && rEnd <= weekEnd

RETURN
SWITCH(
    TRUE(),
    rStartInWeek && rEndInWeek, "#FFA07A",  -- Start and End in the same week
    rStartInWeek, "#ADD8E6",                 -- Start in the week
    rEndInWeek, "#006884",                   -- End in the week
    BLANK()
)
  • Tobz007's avatar
    Tobz007
    Frequent Visitor
    2 years ago

    Thanks alot Dinesh, I tried using SELECTEDVALUE as suggested but still having the same issue.