Forum Discussion
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 conditionally format the background colour for the last hierachy (let's call it "Project") if the value satify certain conditions in the columns. So if the Project starts in one of the weeks in the column, the background is formatted to a certain colour, if the Project ends in one of the weeks, the background is formatted to another colour, and if the Project starts and ends in same week, the background if formatted to another colour. The problem I'm having now is that when a Project has a start date in one of the weeks (in the column) and the same Project has an end date in another week (in the column), only one of the field background gets formatted instead of the 2 fields that satisfy the condition. Please how do I fix this?
The current DAX I'm using is below.
Colour Format =
VAR rStart = MIN(Table[ResourceStart])
VAR rEnd = MIN(Table[ResourceEnd])
VAR weekStart = MIN(Table[WeekStart])
VAR weekEnd = MIN(Table[WeekEnd])
VAR rStartInWeek = rStart >= weekStart && rStart <= weekEnd
VAR rEndInWeek = rEnd >= weekStart && rEnd <= weekEnd
RETURN
SWITCH(
TRUE(),
rStartInWeek && rEndInWeek, "#FFA07A",
rStartInWeek, "#ADD8E6",
rEndInWeek, "#006884",
BLANK()
)
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
3 Replies
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() )Thanks alot Dinesh, I tried using SELECTEDVALUE as suggested but still having the same issue.
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
