Split by Delimiter - Dynamic

[LucaB72]

Dear all,

Following “AntrikshSharma” solution to “michellepace” query “Split by delimiter in dax: value 1 - value 2 - value 3”, I would like to take the opportunity to ask for your help.

I have similar problem to “michellepace”, but I do not know priory the number of level I do need to break down the text.

Attached the file excel of a template, showing a project Work Breakdown Structure, that should be split by parent-child relationship on the “WBS Code” field, so I can later on use as pivoting items in my Power Bi analysis of the project schedule.

To add a further complexity to the problem, the codes from the “WBS Code” structure has a hard coded structure, the meaning of the last child level, each row does represent, is stored in the “WBS Name” field.

Your support would be highly appreciated.

PS

• Not all parent/child relationship for the various items have homogeneous level, but all converge to the project (level 0);
• In the attached file, the “LEVEL” field I added, is not coming as part of the standard report, so may be would be better to use as parameter if needed;

Thanks

LucaProject WBS Structure.xlsx (233.7 KB)

[AntrikshSharma]

@LucaB72 I think you need to first define Parent and Child and then you can refer to this

Dynamically flatten Parent-Child Hierarchies in DAX and PowerBI – The BIccountant

[LucaB72]

Will try to apply it

the Parent-Child can be define at the occurence of the “.”

I can use your code

RawData =
VAR Temp =
ADDCOLUMNS (
ADDCOLUMNS ( WBStbl, “@Parent”, SUBSTITUTE ( WBStbl[WBS Code], “.”, “|” ) ),
“@NodeDepth”, LEN ( [@Parent] ) - LEN ( SUBSTITUTE ( [@Parent], “|”, “” ) )
)
RETURN
Temp

where WBStbl is the table with the Work Breakdown Structure imported into Power BI model and the “.” i convert in “|” to estabblish the parent-child relationship

Thanks
Luca

[LucaB72]

Sorry, I had a look at the shared file, that may be i am not fully understand.
I would need to apply and get as many columns as the levels defined.
I attached the excel file tab “Values” with example for the first few rows.

I also attached the Power Bi file with the table imported and modified with the WBS table imported and the parent child relationship defined “RawData” table

Power BI File:
ProjectWBS.pbix (341.9 KB)

Excel File:
Project WBS Structure.xlsx (1.5 MB)

[LucaB72]

forgot to say that would need to sort in DAX rather than M language / Power Query.
as i do have multiple entrance for each schedule update.
Been the planners not diligent and continuosly modify the attributes (in this case the WBS) i do need to have for each entry

[AntrikshSharma]

@LucaB72 What will the end result look like?

[LucaB72]

Hi
shoudl look like from the tab “Values” of the attached Excel file. I manually done for the first few rows.

After have split the levels from “WBS Code” for the level description need to pick from the “WBS Name”
is then a two step exercise, first divide the level parent-child and the pick the level description.

Thanks
Luca

Excel File (end risuts in “Values” tab, first few rows):
Project WBS Structure.xlsx (1.5 MB)

[Rajesh]

@LucaB72

I just want to understand the final output

We’ve to create all these levels based on hierarchy right ?

[LucaB72]

Yes, correct

[Rajesh]

@LucaB72

Created Calculated columns. Can you please test attached PBIX ?

Here the Max Pathlength is 10, so we’ve to create 11 fields.

Parent Child.pbix (400.4 KB)

[LucaB72]

Excellent, exactly what is needed.

One last question, as this would need to be used accross multiple projects.
Some have up to 5 levels, other even more than 10, is there an way to make the levelling dynamic or need to put a hard constrain on the level that can be extracted with the hard coding for example up to 10.

Thanks
luca

[Rajesh]

@LucaB72

Using DAX we can’t create columns dynamically.
While designing the report only you’ve to decide how many levels you want to extract.

Are these levels static for each project ?

If it is static its only onetime job. If not every time a new level is added you’ve to create new column.

You can create an alert to get the notification if it cross the Levels.

Hope this helps.

[LucaB72]

Yes it is very informative and the idea of having an alert if projects have more than a pre-establoshed number of level it sound good.

so far we have not been lucky enough to enforce a common structure ccross the porjects.
but we are working on it, it is an issue of stakeholder management and Client.

We are also working on have a set of attroute costant accross the organization, but it is a long journey, however it is needed in order to been able to identify area of the Works that are not performing as needed and find solutions.

Thanks
Luca

[EnterpriseDNA]

Hi @LucaB72, did the responses provided help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!