Converting Project WBS to Parent/Child Heirarchy

[k-giles]

Hi all,

I'm looking to build a dax to convert the WBS code column into two columns to show the parent/child heirarchy and build a path. This will then be later on used to develop a gantt chart in PBI and show the tasks in a table in correct heirarchical order.

I've split out the wbs structure into 4 columns

Now I'm trying to write a dax that will calcualte the child tasks using the following formula:

WBS Child = IF(ISBLANK([WBS Level.4]),[WBS Level.3],(if(ISBLANK([WBS Level.3]),Tasks[WBS Level.2],(Tasks[WBS Level.2]))))

However it is only returning the level 4 or 3 value not returning level 2 values or presenting an error so not sure where I've gone wrong or if this is event the right solution to do this.

Thanks in advance

[Anonymous · Accepted solution]

Hi @k-giles 

I would recommend you to use SWITCH() instead of IF() .

WBS Child = SWITCH(TRUE(),Tasks[WBS.4]<>BLANK(),Tasks[WBS.4],
                          Tasks[WBS.4]=BLANK() && Tasks[WBS.3]<>BLANK(),Tasks[WBS.3],
                          Tasks[WBS.4]=BLANK() && Tasks[WBS.3]=BLANK() && Tasks[WBS.2]<>BLANK(),Tasks[WBS.2])

The result is as shown :

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.