Gantt Chart - Slicing Calculated Columns

[forti4040]

Hello All,

I am using the Gantt 1.14.3 visual to display timelines for my end users. Here's the challenge I'm facing and need help with...

The data I'm pulling has start/finish dates for each task across multiple regions (up to 8 regions).

Example:

              Region 1  Start     Region 1  Finish   --   Region 2  Start     Region 2  Finish

Task1        9/1/18                      10/1/18                      9/15/18              9/20/18

Task 2       9/10/18                    10/10/18                    9/11/18              9/21/18

etc...

I've created columns called Earliest Start and Latest Finish to determine the earliest start among all regions and the latest finish among all regions, for each task. These are the columns I'm using in my gantt chart for the task start/finish dates and it is working effectively. The tasks show the absolute longest duration that the task would take and that's what I'm going for.

Works...Happy...except when the users ask, "well what are the dates just for region X?"

I know I can teach them to switch out the columns to reflect Region X Start and Region X Finish, but that's not ideal. What I'd love is to have a slicer on the page that lets them pick the regions they want included in the Gantt chart. But because my Earliest Start and Latest Finish values are in calculated columns I don't know how to filter them with a slicer....Any thoughts?

[Ashish_Mathur · Accepted solution]

Hi

Using the Query Editor, i have transformed your data as shown below.  You may download my PBI file from here.  Build your transformations from here.

Hope this helps.

[forti4040]

@Ashish_Mathur & @v-chuncz-msft,

Thank you both for the help on this. I was able to unpivot and merge several of my sources to ultimately get to the solution needed. I very much appreciate your guidance on this!

Regards,

Eric

[Ashish_Mathur]

You are welcome.

[v-chuncz-msft]

@forti4040,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/Powerbi-Transpose-set-of-columns-as-a-ROWS/td-p/488457

[forti4040]

@v-chuncz-msft, 

Thank you for the response. I'm not following how your link addresses my situation. The functions mentioned in that link seem to combine data from multiple columns into a single column (similar to concatenate?) and in a new table. Unless I'm misunderstanding your suggestion it doesn't seem like that accomplishes what I'm looking for. 

In the below example I'm creating two calculated columns to find MIN and MAX and am using these for my start and finish dates in the Gantt chart. Those calculated columns use columns 1, 2, 3, & 4 in the formulas. My goal is to be able to use a slicer to determine which columns are actually included in those formulas. So my slicer for the below table would include (Col1, Col2, Col3, Col4, All). 

           Column 1, Column2, Column 3, Column4, Column 5 (Min Value), Column 6 (Max Value)

Row1 -     5         ,       7      ,       9      ,      11      ,                   5               ,                11

Row2 -     4         ,       21    ,       8      ,      13      ,                   4               ,                21

Did I misunderstand your suggestion and/or does my above example make sense?

Regards,

Eric

[forti4040]

FYI I found this topic: https://community.powerbi.com/t5/Desktop/DAX-Column-content-based-on-slicer-selection/td-p/378628

that seems like it should address my issue. But when I create a measure for Start Dates and a measure for Finish dates I'm unable to include them both in the Gantt visual. Not sure why...?