Gantt Chart Visualisation - "Parent Field"

[Mezga]

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.

[MFelix]

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

[Mezga]

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.

[MFelix]

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.

[Mezga]

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.

[MFelix · Accepted solution]

Hi @Mezga ,

I have made a smal sample with the following setup:

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")
               )

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

Check PBIX file attach.

[Mezga]

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!

[MFelix]

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

[Mezga]

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:

ID	Department	Supervisor	Start Date - Location 1	End Date - Location 1	Start Date - Location 2	End Date - Location 2
1	A	AA	01/01/21	31/12/21	01/06/22	31/12/22
2	B	BB	01/01/21	31/12/21	01/06/22	31/12/22
3	C	AA	01/01/21	31/12/21	01/06/22	31/12/22

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

ID	Location	Start Date	End Date
1	Location 1	01/01/21	31/12/21
1	Location 2	01/06/22	31/12/22
2	Location 1	01/01/21	31/12/21
2	Location 2	01/06/22	31/12/22
3	Location 1	01/01/21	31/12/21
3	Location 2	01/06/22	31/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?