Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear Community
I have the following issue
There are multiple projects (more than 200) . Each projects has multible discipline and each discipline has diffrent tasks in original data set. Please see sample data below. I would like to summarise data into a table and then a gant chart: Project name, Discipline name, start date, finish date . So table should use the earliset start date as summary start date and the latest finish date as summary finish date what the earliest date and the latest date is per disicpline
I am new in DAX and tried to use this formulat but seems doesn't work:
here is original data:
Project Name | Name | Discipline | Start | Finish | FY |
Ramp A | |||||
Grand Road | Ramp A - Physical work Start | Ramp A | 13-Nov-24 | ||
Grand Road | Ramp A - Physical work Completion | Ramp A | 7-Dec-25 | 2026 | |
Grand Road | Ramp A - Defect Completion | Ramp A | 10-Feb-26 | ||
Grand Road | Ramp A -Operational Readyness | Ramp A | 15-Apr-26 | ||
Access B | |||||
Grand Road | Access B - Physical work Start | Access B | 7-Mar-23 | ||
Grand Road | Access B - Physical work Completion | Access B | 7-Mar-23 | 28-Aug-25 | 2026 |
Grand Road | Access B- Defect Completion | Access B | 16-Nov-25 | ||
Control Unit | |||||
Grand Road | Control Unit - Physical work Start | Control Unit | 22-Aug-24 | ||
Grand Road | Control Unit - Physical work Completion | Control Unit | 22-Aug-24 | 31-Mar-25 | 2025 |
Grand Road | Control Unit - Defect Completion | Control Unit | 1-Nov-24 | 28-Apr-25 | 2026 |
Grand Road | Control Unit -Operational Readyness | Control Unit | 15-Sep-25 | ||
Road Landscape | |||||
Holt Ave | Road Landscape - Physical work Start | Road Landscape | 16-Jan-23 | ||
Holt Ave | Road Landscape- Physical work Completion | Road Landscape | 16-Jan-23 | 11-Aug-23 | 2024 |
Holt Ave | Road Landscape - Defect Completion | Road Landscape | 14-Feb-24 | ||
Holt Ave | Road Landscape -Operational Readyness | Road Landscape | 14-Feb-24 | 14-Feb-24 | |
StormWater Tank | |||||
Holt Ave | StormWater Tank - Physical work Start | StormWater Tank | 1-Mar-22 | ||
Holt Ave | StormWater Tank - Physical work Completion | StormWater Tank | 31-Mar-23 | 2023 | |
Holt Ave | StormWater Tank Defect Completion | StormWater Tank | 31-Dec-24 | ||
Holt Ave | StormWater Tank -Operational Readyness | StormWater Tank | 1-Mar-25 |
Summarised table should be like this
Project Name | Name | Start | Finish | FY |
Grand Road | Ramp A | 13-Nov-24 | 15-Apr-26 | 2027 |
Grand Road | Access B | 7-Mar-23 | 16-Nov-25 | 2026 |
Grand Road | Control Unit | 22-Aug-24 | 15-Sep-25 | 2026 |
Holt Ave | Road Landscape | 16-Jan-23 | 14-Feb-24 | 2024 |
Holt Ave | StormWater Tank | 1-Mar-22 | 1-Mar-25 | 2025 |
And summarise gant visual like this:
Can anyone help me with this?
Solved! Go to Solution.
Hi @Narsis
Thanks for your reply. If you can't create a new calculation table. You can create a measure as follows:
start date =
VAR _table = CALCULATETABLE (
SUMMARIZE (
FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
'Table'[Project Name],
'Table'[Discipline],
"start", CALCULATE ( MIN ( 'Table'[Start] ) ),
"Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
"FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
)
)
VAR _project_name = SELECTEDVALUE('Table'[Project Name])
VAR _discipline = SELECTEDVALUE('Table'[Discipline])
RETURN CALCULATE(
MAX([Start]),
FILTER(_table,
'Table'[Discipline]=_discipline&&'Table'[Project Name]=_project_name
)
)
End date =
VAR _table = CALCULATETABLE (
SUMMARIZE (
FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
'Table'[Project Name],
'Table'[Discipline],
"start", CALCULATE ( MIN ( 'Table'[Start] ) ),
"Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
"FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
)
)
VAR _project_name = SELECTEDVALUE('Table'[Project Name])
VAR _discipline = SELECTEDVALUE('Table'[Discipline])
RETURN CALCULATE(
MAX([Finish]),
FILTER(_table,
'Table'[Discipline]=_discipline&&'Table'[Project Name]=_project_name
)
)
Use these two measures in your Gantt chart:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Name", type text}, {"Discipline", type text}, {"Start", type date}, {"Finish", type date}, {"FY", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Project Name] <> null and [Project Name] <> ""),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Name] <> null and [Name] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Project Name", "Discipline"}, {{"Start", each List.Min([Start]), type nullable datetime}, {"End", each List.Max([Finish]), type nullable datetime}})
in
#"Grouped Rows"
I do not know how to get the FY.
Thanks I will make a try.
for now I created two columns summary start date and summary finish date with project and discipline filter:
Thanks for your Response. I can't create a new Table as I have to add more columns/value from current data set to my visual once early start and late finish summary sortted. And also there is not a unique ID/cpde to make a relationship between data sets or Tables. May have to create multiple columns in my current data set. Any Idea?
I am trying with this DAX :
Hi @Narsis
Thanks for your reply. If you can't create a new calculation table. You can create a measure as follows:
start date =
VAR _table = CALCULATETABLE (
SUMMARIZE (
FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
'Table'[Project Name],
'Table'[Discipline],
"start", CALCULATE ( MIN ( 'Table'[Start] ) ),
"Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
"FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
)
)
VAR _project_name = SELECTEDVALUE('Table'[Project Name])
VAR _discipline = SELECTEDVALUE('Table'[Discipline])
RETURN CALCULATE(
MAX([Start]),
FILTER(_table,
'Table'[Discipline]=_discipline&&'Table'[Project Name]=_project_name
)
)
End date =
VAR _table = CALCULATETABLE (
SUMMARIZE (
FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
'Table'[Project Name],
'Table'[Discipline],
"start", CALCULATE ( MIN ( 'Table'[Start] ) ),
"Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
"FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
)
)
VAR _project_name = SELECTEDVALUE('Table'[Project Name])
VAR _discipline = SELECTEDVALUE('Table'[Discipline])
RETURN CALCULATE(
MAX([Finish]),
FILTER(_table,
'Table'[Discipline]=_discipline&&'Table'[Project Name]=_project_name
)
)
Use these two measures in your Gantt chart:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Narsis
Depending on your description, you can try the following DAX expressions:
Table 2 =
CALCULATETABLE (
SUMMARIZE (
FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
'Table'[Project Name],
'Table'[Discipline],
"start", CALCULATE ( MIN ( 'Table'[Start] ) ),
"Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
"FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
)
)
The summary table is as follows:
You can then use these fields in your gant plot as follows:
You can look for the Gantt chart you want from the visual marketplace:
I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
92 | |
82 | |
63 | |
62 | |
58 |
User | Count |
---|---|
159 | |
115 | |
103 | |
75 | |
66 |