Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mezga
Helper I
Helper I

Gantt Chart Visualisation - "Parent Field"

Hello,

 

Does anyone know if it's possible to have the "Parent" field on the Gantt Chart visualisation change dynamically?

 

E.g. if I had two or more fields that I wanted to use as the parent, is there a way to allow the user to switch between each option, using a slicer or any other method?

 

Only one field/column can be added to Parent. I've tried creating a few measures that I thought might solve this, however the visual doesn't allow me to add the measures onto the Parent parameter. Not sure if this is because the measure is returning an invalid result, or if the visual doesn't allow measures for this parameter.

 

Any ideas or work-arounds would be greatly appreciated.

1 ACCEPTED SOLUTION

Hi @Mezga ,

 

I have made a smal sample with the following setup:

MFelix_0-1640257576868.png

 

I also created the following table:

Brands & Countries = UNION(
                        SELECTCOLUMNS('Table', "Department", 'Table'[Department], "ID", 'Table'[ID], "Axis", "Department"),
                        SELECTCOLUMNS('Table', "Supervisor", 'Table'[Supervisor], "ID", 'Table'[ID], "Axis", "Supervisor")
               )

 

MFelix_1-1640257715507.png

Add a relationship between the IDs and then setup you gantt;

MFelix_2-1640257747268.png

MFelix_3-1640258379318.png

 

Check PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

Hi @Mezga ,

 

Using a similar approach to this one you can achieve what you need.

 

https://towardsdatascience.com/dynamic-axis-in-power-bi-daxis-72ecb22c119f


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

Thanks for your response. I've given that suggestion a good attempt, however still not entirely sure how to apply to my scenario.

 

The example you provided requires creation of a measure as per below:

 

Sales Amt TREATAS = 
   IF(
       HASONEVALUE('Brands & Countries'[Axis]),
          SWITCH(VALUES('Brands & Countries'[Axis])
                 ,"Countries", CALCULATE(SUM('Online Sales'[SalesAmount])
                                      ,TREATAS(VALUES('Brands & Countries'[Value])
                                      ,Geography[RegionCountryName]))
                 ,"Brands",    CALCULATE(SUM('Online Sales'[SalesAmount])
                                      ,TREATAS(VALUES('Brands & Countries'[Value])
                                      ,'Product'[BrandName]))
)
)

However in my case, the fields I want to switch between are all text, so the "SUM" part of the measure is irrelevant.

 

I tried removing that part of the measure, however it always seems to throw an error.

Hi @Mezga ,

 

In this case instead of the SUM you can use SELECTEDVALUE for example or MAX/MIN.

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks @MFelix , I gave that a shot but still can't get it to work. Unfortunately I can't really share the data, but I can describe further below:

 

Basically I'm working with a personnel roster for a project.

 

One column in the Roster table is named "Department". Each person on the roster resides within a department, e.g. Engineering, Construction, and so on.

 

Another column in the table is named "Supervisor". Again, each person has a supervisor that they report to.

 

The Gantt visual allows me to place only one these columns at a time onto the "Parent" field. Both fields can be placed, and work fine, but only one can be placed at a time.

 

I want to give users the option of using a slicer to select between Department or Supervisor.

 

So basically I need a measure that looks at the selection of the slicer, and points to either the Department or Supervisor column.

Hi @Mezga ,

 

I have made a smal sample with the following setup:

MFelix_0-1640257576868.png

 

I also created the following table:

Brands & Countries = UNION(
                        SELECTCOLUMNS('Table', "Department", 'Table'[Department], "ID", 'Table'[ID], "Axis", "Department"),
                        SELECTCOLUMNS('Table', "Supervisor", 'Table'[Supervisor], "ID", 'Table'[ID], "Axis", "Supervisor")
               )

 

MFelix_1-1640257715507.png

Add a relationship between the IDs and then setup you gantt;

MFelix_2-1640257747268.png

MFelix_3-1640258379318.png

 

Check PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hey @MFelix ,

 

Thanks mate, you're a legend! That has done the trick.

 

Whilst attempting to apply your solution, I uncovered that I couldn't create the required relationship. 

 

With the way that the data is provided to me, I have four sets of "Start Date" and "End Date" columns (for different locations on the project). I had created an appended table that has multiple instances of the same person, so that it would show all of their start and end dates on one row of the gantt chart.

 

I think these duplications of the "ID" was therefore preventing me from establishing the relationship between ID of the original table, and ID of the UNION table.

 

So I reverted to a non-appended version of the table and it worked. However now I'm going to need to work out how to change my data model to allow for multiple start and end dates, whilst still providing the same functionality!

If you can change my example into the one you have with the multiple dates I can try and get you a solution. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hey @MFelix,

 

Back from the Christmas break...looks like I managed to solve the multiple start/end dates issue I mentioned, though it may not be the "right" way.

 

If for example the original roster looked something like the below:

 

IDDepartmentSupervisorStart Date - Location 1End Date - Location 1Start Date - Location 2End Date - Location 2
1AAA01/01/2131/12/2101/06/2231/12/22
2BBB01/01/2131/12/2101/06/2231/12/22
3CAA01/01/2131/12/2101/06/2231/12/22

 

I have transformed/copied/appended the above to look like:

 

IDLocationStart DateEnd Date
1Location 101/01/2131/12/21
1Location 201/06/2231/12/22
2Location 101/01/2131/12/21
2Location 201/06/2231/12/22
3Location 101/01/2131/12/21
3Location 201/06/2231/12/22

 

And then created a relationship between ID of the second table above, and the ID of the custom table as per your instructions.

 

Not sure if this is the most efficient method though, given that the original roster has about 1,300 individual people and five locations (not two as per above example). So the final table has 1300 x 5 = 6,500 rows.

 

Thoughts?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.