Forum Discussion
Path and Matrix
[mPath] was created in your DAX calculated column or is that in your original dataset?
If it was in DAX, then why not continue with the article's example model?
If it is in your original dataset then I may not be able to assist further other than to say transform [mPath] to look like the article's model using a method like https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/ to create [lvl1], [lvl2], etc.I created a sample from scratch and followed the example model to produce:
from:
ID Parent_ID Employee Salary 1 2 Mike 3000 2 5 Sally 5000 3 2 Diane 3000 4 5 Randy 5000 5 Alan 6000 6 1 David 2000 7 4 Susan 3000 8 5 Carrie 4000 9 8 Luther 3000 10 5 Max 5000
hello dt_mc,
Have you taken a look at https://www.daxpatterns.com/parent-child-hierarchies/?
Also note that the measures for [BrowseDepth] & [MaxDepth] are in the comments as:
BrowseDepth =
ISFILTERED(TableName[Lvl1])
+ ISFILTERED(TableName[lvl2])
+ ISFILTERED(TableName[lvl3])
MaxDepth = MAX(TableName[Depth])
Hi -
i did but i guess i'm still a bit confused on how to implement this as a visualization with-in BI. the hierarchy portion in particular is whats driving my problem
Here is a sample piped dataset that my query creats. only leaves (leafs?) have a base amount in 'Sales'.
all above the leaf are based on the roll-up. i will derive the amounts on my own via a common table expression query in SQL Server. mPath, level, isLeaf are derived by a hierarchicalid datatype but it seems similar enough to what DAX would create. all i really want is to put this data set with-in PowerBI and have the appropriate drill down hierarchy. So like Employees 1/2 are beneath District Manager 1, who is beneath the CEO, etc.
Title - Name|mPath|level| Sales |isLeaf
CEO|/1/|1| 30,519,775.60 |0
District Manager 1|/1/1/|2| 18,287,424.13 |0
Employee 1|/1/1/15/|3| 818,005.29 |1
Employee 2|/1/1/9/|3| 17,469,418.84 |1
District Manager 2|/1/10/|2| 12,232,351.46 |0
Manager 1|/1/10/2/|3| 5,698,074.85 |0
Employee 3|/1/10/2/1/|4| 59,938.89 |1
Employee 4|/1/10/2/2/|4| 90,141.55 |1
Employee 5|/1/10/2/3/|4| 5,547,994.41 |1
Manager 2|/1/10/3/|3| 34,337,200.89 |0
Employee 6|/1/10/3/1/|4| 8,940,660.22 |1
Employee 7|/1/10/3/10/|4| 90,949.38 |1
Employee 8|/1/10/3/11/|4| 30,456.30 |1
Employee 9|/1/10/3/14/|4| 67,492.01 |1
Employee 10|/1/10/3/3/|4| 4,303,050.47 |1
Employee 11|/1/10/3/4/|4| 5,379,091.62 |1
Employee 12|/1/10/3/6/|4| 1,698,628.77 |1
Employee 13|/1/10/3/7/|4| 9,738.37 |1
Employee 14|/1/10/3/8/|4| 13,204,253.86 |1
Employee 15|/1/10/3/9/|4| 612,879.90 |1
Manager 3|/1/10/4/|3| (27,802,924.27)|0
Employee 16|/1/10/4/10/|4| (527,758.16)|1
Employee 17|/1/10/4/11/|4| (5,441.56)|1
Employee 18|/1/10/4/12/|4| (3,236,413.59)|1
Employee 19|/1/10/4/13/|4| (16,816.30)|1
Employee 20|/1/10/4/2/|4| (6,435,647.28)|1
Employee 21|/1/10/4/4/|4| (3,316,987.18)|1
Employee 22|/1/10/4/5/|4| (4,198,366.86)|1
Employee 23|/1/10/4/7/|4| (1,261,178.32)|1
Employee 24|/1/10/4/8/|4| (4,047.10)|1
Employee 25|/1/10/4/9/|4| (8,800,267.93)|1[mPath] was created in your DAX calculated column or is that in your original dataset?
If it was in DAX, then why not continue with the article's example model?
If it is in your original dataset then I may not be able to assist further other than to say transform [mPath] to look like the article's model using a method like https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/ to create [lvl1], [lvl2], etc.I created a sample from scratch and followed the example model to produce:
from:
ID Parent_ID Employee Salary 1 2 Mike 3000 2 5 Sally 5000 3 2 Diane 3000 4 5 Randy 5000 5 Alan 6000 6 1 David 2000 7 4 Susan 3000 8 5 Carrie 4000 9 8 Luther 3000 10 5 Max 5000 
ChrisMendoza I found your example here to be a real good complement to Marco Russo's parent child hierarchy article in the dax pattern website https://www.daxpatterns.com/parent-child-hierarchies/