Help to build a Gantt chart in Power BI

[quincy_p]

I have several fields that I am trying to combine into a single Gantt chart to track my teams activities - is it possible to achieve this via a single measure?

I currently have the below which does not work properly, what I want it to do is assign the value 1 to all dates in the range but currently it keeps only choosing the first one:

Days Booked = 
VAR SelectedDate = SELECTEDVALUE(Calendar[Date])
RETURN
IF (
    CALCULATE (
        COUNTROWS('Technician Time OFF'),
        'Technician Time OFF'[SVMXC__StartDateTime__c] <= SelectedDate &&
        'Technician Time OFF'[SVMXC__EndDateTime__c] >= SelectedDate
    ) > 0,
    1,
    BLANK() 
)

But I want to add another level to this (or initially a seperate measure if easier that will check a different table/field 'Work Orders' [Scheduled_Date_Time].

So in theory if there is a time off day it would return 1, if there is a Work Order Schedule Date then 2, and if they overlap 3.

is this possible? 

[quincy_p]

Works perfectly - thanks!

[DataNinja777 · Accepted solution]

Hi @quincy_p ,

To build a Gantt-style chart in Power BI that reflects technician activities across dates, you can use a single DAX measure that evaluates whether a given date is marked as time off, scheduled work, or both. The issue with your current measure is that it's only returning 1 for the first match and ignoring overlapping conditions or additional tables. What you want is a prioritization system where time off returns 1, scheduled work returns 2, and if both happen on the same day, it returns 3. You can achieve this using the SWITCH(TRUE()) pattern to layer the logic and return the appropriate value.

Here is the corrected DAX measure:

Activity Type = 
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
VAR IsTimeOff = 
    CALCULATE(
        COUNTROWS('Technician Time OFF'),
        'Technician Time OFF'[SVMXC__StartDateTime__c] <= SelectedDate &&
        'Technician Time OFF'[SVMXC__EndDateTime__c] >= SelectedDate
    ) > 0
VAR IsScheduledWork = 
    CALCULATE(
        COUNTROWS('Work Orders'),
        'Work Orders'[Scheduled_Date_Time] = SelectedDate
    ) > 0
RETURN
    SWITCH(
        TRUE(),
        IsTimeOff && IsScheduledWork, 3,
        IsScheduledWork, 2,
        IsTimeOff, 1,
        BLANK()
    )

This measure checks each date against both conditions using CALCULATE and logical operators, then returns the value based on the overlap. You can place the Calendar[Date] on the X-axis and the technician or team member on the Y-axis in a matrix or bar chart, using this measure as the value. If the scheduled work date includes time, you should convert it to a pure date format to ensure it matches your calendar granularity. This will allow you to visualize team availability and conflicts in a clear, color-coded way.

Best regards,