Using a hierchial Path in Dax to split out levels

[vanessafvg]

I have gotten the hierarchial Path in dax,  from 2 columns called assetnum and parent ie. some of the levels go up to 8 some only have one, how can i now further split this out in DAX, is there a way to split the levels so i can build a hierachy from it.  In other words like in the query editor split by delimiter but in dax because there doesn't seem to be  way to push that back to the query editor (the path calculated column)

[v-yuta-msft · Accepted solution]

Hi vanessafvg ,

In this senario, you can use SUBSTITUTE and PATHITEM function to achieve split columns in dax, please refer to this blog:

https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/.

Regards,

Jimmy Tao

[v-yuta-msft]

Hi vanessafvg,

So your requirement is to split one column which contains hierarchial Path data into several sub columns, right? You should use "Split Columns" in query editor because dax can't shape and transform data because both measure and calculate column are in report level which can't affect data model.

Regards,

Jimmy Tao

[vanessafvg]

@v-yuta-msft thats the whole point i cant use query editor because the path data gets created in dax