Conditional formatting in a matrix with repeated values in the rows of a single sublevel

[Barsine]

Hello everyone! I usually work with Power BI but I consider myself to be at a fairly beginner level and I need help to develop the dashboard I want to do.

I have created a matrix to resemble a Gantt chart schedule, setting out what machinery is being or will be used in the next few days of a calendar. The machinery is divided into Type, Subtype and Serial Number. A machine with a given serial number can be used on several projects throughout the established schedule.

To determine the status of each machine, I have painted the days representing the calendar cells according to the status of the machine: Blue, if the machine is planned to be used; Yellow, if the machine is being used; and Green, if the use of the machine for a given project is completed. The gray cells represent the days of the weekend.

To represent the days and colors in the matrix I have used the following formulas:

WeekDay = WEEKDAY(TimeTable[Date], 2)
WorkDay = IF(OR('TimeTable'[WeekDay]=6,'TimeTable'[WeekDay]=7), 0, 1)

- For the values of the matrix: 

Gantt_Values =
CALCULATE(
SUMX(ValuesTable,
IF(AND(
TODAY() >= MIN(TimeTable[Date]),
TODAY() <= MAX(TimeTable[Date])
), 1,
IF(
AND(
MIN(TimeTable[Date])>=ValuesTable[StartDate],
MAX(TimeTable[Date])<= ValuesTable[EndDate]
),
1,
""
))))

- For the conditional formatting of the values of the matrix, I have used the following formula: 

Gantt_Colors =
VAR Cond_Today = CALCULATE( SUMX(ValuesTable, [Gantt_Values]), TODAY() = TimeTable[Date])
VAR Cond_WEnd = CALCULATE( SUMX(ValuesTable, [Gantt_Values]), TimeTable_Gantt[WorkDay]= 0)
VAR Cond_P = CALCULATE( SUMX(ValuesTable, [Gantt_Values]), ValuesTable[StateTypeCode]= 1, TimeTable[WorkDay]= 1)
VAR Cond_D = CALCULATE( SUMX(ValuesTable, [Gantt_Values]), ValuesTable[StateTypeCode]= 3, TimeTable[WorkDay]= 1)
VAR Cond_C = CALCULATE( SUMX(ValuesTable, [Gantt_Values]), ValuesTable[StateTypeCode]= 4, TimeTable[WorkDay]= 1)

return
    SWITCH(Cond_Today, 1, "#c06500",
    SWITCH(Cond_WEnd, 1, "#E6E6E6",
    SWITCH(Cond_P, 1, "#305496",
    SWITCH(Cond_D, 1, "#FFC000",
    SWITCH(Cond_C, 1, "#92D050")))))

What I would like to be able to show is that if for a machine with a certain serial number it is planned to be used over time for two or more projects, to paint the days that are planned (Blue) in Red, to represent the conflict in planning the use of such machinery.

I have tried to do this with a formula that tells me, at the row sublevel level of the serial number, the number of projects in which a machine is used throughout the calendar, but then I don't know how to integrate this formula into the previous ones to be able to give the format of the days in red.

Any idea how I could do it?

Thank you very much in advance and greetings to all.

[_AAndrade]

Hi @Barsine,

Could you share a sample file so can I take a look?