Forum Discussion

danny_b's avatar
danny_b
Regular Visitor
1 year ago
Solved

Gantt Matrix - How do I show multiple project phases in a single row and color format each phase?

Hello,

 

I have built a gantt chart using a matrix visual, however, each project has multiple phases on different dates.  My data is below and I also have a date table I'm referencing:

New Project Table

Project

Phase

First Date

Last Date

Project 1

Pilot

5/15/2024

6/14/2024

Project 2

Pilot

10/24/2024

2/1/2025

Project 3

Pilot

12/3/2024

2/17/2025

Project 1

Market

1/1/2025

1/29/2025

Project 2

Market

2/3/2025

3/26/2025

Project 4

Pilot

2/10/2025

3/12/2025

Project 4

Market

4/10/2025

5/10/2025

Project 3

Market

5/20/2025

8/25/2025

Project 5

Pilot

5/29/2025

7/3/2025

Project 4

National

6/10/2025

7/10/2025

Project 5

Market

7/17/2025

9/18/2025

 

I initially built the below gantt using a summarized project table with overall start/end dates with the following result.

Project Timelines

ProjectFirst DateLast Date
Project 15/15/20241/29/2025
Project 210/24/20243/26/2025
Project 312/3/20248/25/2025
Project 42/10/20257/10/2025
Project 55/29/20259/18/2025

 

Values measure is below, and I then conditionally formatted the background color and the font.  

CF Gantt =
VAR StartDate =
    CALCULATE(MIN('Project Timelines'[First Date]), REMOVEFILTERS('Fiscal Calendar'))
   
VAR EndDate =
    CALCULATE(MIN('Project Timelines'[Last Date]), REMOVEFILTERS('Fiscal Calendar'))

VAR ProjectPeriod =
    MIN('Fiscal Calendar'[Calendar Date]) >= StartDate
    && MIN('Fiscal Calendar'[Calendar Date]) <= EndDate

VAR Result =
    IF(ProjectPeriod, 1)

RETURN
Result  

 

I would like to use the first table (New Project Table) to build a gantt matrix like above, however, I want to see the gaps between each phase, AND I'd like to see each phase in a different color like below:

 

I have tried adding a variable to the measure above to try to identify which phase the project is in (ProjectPeriod && Phase), but it only allows me to calculate the MIN or MAX.  How do I get it to recognize the ProjectPeriod AND Phase?

 
Thanks,
Daniel

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from bhanu_gautam , please allow me to provide another insight:

    Hi, danny_b 
    Thanks for reaching out to the Microsoft fabric community forum.

    My idea is similar to bhanu_gautam's.Regarding the issue you raised, my solution is as follows:

    1.First, I created the following calculated table to serve as the columns of the matrix:

     

    Fiscal Calendar = 
CALENDAR (
    MINX (
        {
            MIN ( 'New Project Table'[First Date] ),
            MIN ( 'New Project Table'[Last Date] )
        },
        [Value]
    ),
    MAXX (
        {
            MAX ( 'New Project Table'[First Date] ),
            MAX ( 'New Project Table'[Last Date] )
        },
        [Value]
    )
)

     

    2.Next, I used the following measures as the values of the matrix:

     

    Measure = 
VAR market1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Market"
        )
    )
VAR market2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Market"
        )
    )
VAR Pilot1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Pilot"
        )
    )
VAR Pilot2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Pilot"
        )
    )
VAR National1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "National"
        )
    )
VAR National2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "National"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Fiscal Calendar'[Date] ) >= market1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= market2m, "#FF0000",
        
        MAX ( 'Fiscal Calendar'[Date] ) >= Pilot1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= Pilot2m, "#00FF00",
        
        MAX ( 'Fiscal Calendar'[Date] ) >= National1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= National2m, "#0000FF",
        
        "#FFFFFF" 
    )

     

     

    3.Then, I modified the background colour:

    4.Here's my final result, which I hope meets your requirements.

    5.You may need to note that the matrix's column display has a 100-row limit. For more details, please refer to the documentation:

    Solved: Missing Columns in Matrix - Microsoft Fabric Community

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

     

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    danny_b You need to create a measure that checks if a date falls within the start and end dates of each phase. 

     

    DAX
    CF Gantt Phase =
    VAR CurrentDate = MIN('Fiscal Calendar'[Calendar Date])
    VAR Phase1Start = CALCULATE(MIN('New Project Table'[First Date]), 'New Project Table'[Phase] = "Pilot")
    VAR Phase1End = CALCULATE(MAX('New Project Table'[Last Date]), 'New Project Table'[Phase] = "Pilot")
    VAR Phase2Start = CALCULATE(MIN('New Project Table'[First Date]), 'New Project Table'[Phase] = "Market")
    VAR Phase2End = CALCULATE(MAX('New Project Table'[Last Date]), 'New Project Table'[Phase] = "Market")
    VAR Phase3Start = CALCULATE(MIN('New Project Table'[First Date]), 'New Project Table'[Phase] = "National")
    VAR Phase3End = CALCULATE(MAX('New Project Table'[Last Date]), 'New Project Table'[Phase] = "National")

    RETURN
    SWITCH(
    TRUE(),
    CurrentDate >= Phase1Start && CurrentDate <= Phase1End, 1,
    CurrentDate >= Phase2Start && CurrentDate <= Phase2End, 2,
    CurrentDate >= Phase3Start && CurrentDate <= Phase3End, 3,
    BLANK()
    )

     

    Use the measure CF Gantt Phase to apply conditional formatting in the matrix visual. You can set different colors for the values 1, 2, and 3, which correspond to different phases.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from bhanu_gautam , please allow me to provide another insight:

    Hi, danny_b 
    Thanks for reaching out to the Microsoft fabric community forum.

    My idea is similar to bhanu_gautam's.Regarding the issue you raised, my solution is as follows:

    1.First, I created the following calculated table to serve as the columns of the matrix:

     

    Fiscal Calendar = 
CALENDAR (
    MINX (
        {
            MIN ( 'New Project Table'[First Date] ),
            MIN ( 'New Project Table'[Last Date] )
        },
        [Value]
    ),
    MAXX (
        {
            MAX ( 'New Project Table'[First Date] ),
            MAX ( 'New Project Table'[Last Date] )
        },
        [Value]
    )
)

     

    2.Next, I used the following measures as the values of the matrix:

     

    Measure = 
VAR market1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Market"
        )
    )
VAR market2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Market"
        )
    )
VAR Pilot1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Pilot"
        )
    )
VAR Pilot2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Pilot"
        )
    )
VAR National1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "National"
        )
    )
VAR National2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "National"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Fiscal Calendar'[Date] ) >= market1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= market2m, "#FF0000",
        
        MAX ( 'Fiscal Calendar'[Date] ) >= Pilot1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= Pilot2m, "#00FF00",
        
        MAX ( 'Fiscal Calendar'[Date] ) >= National1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= National2m, "#0000FF",
        
        "#FFFFFF" 
    )

     

     

    3.Then, I modified the background colour:

    4.Here's my final result, which I hope meets your requirements.

    5.You may need to note that the matrix's column display has a 100-row limit. For more details, please refer to the documentation:

    Solved: Missing Columns in Matrix - Microsoft Fabric Community

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu