Forum Discussion
Path and Matrix
Hi
i have 5 columns (last is derived via DAX)
ID | Parent_ID | Employee | Salary | Path
what i would like is to have a recursive roll-up for drill down
so like:
Manager
Assistant Manager 1
Employee 1
Employee 2
Employee 3
Assistant Manager 2
Employee 4
Assistant Manager 3
Assistant Manager 4
Employee 5
etc. I have developed the summation part on my own but need the collapse (or drill down). I have the path available, i am just unsure how to use it. Note that each branch does not have the same length.
I need to display amounts but a tree would work if thats the best approach
[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
8 Replies
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
Hello,
I just took a look at this response and it looks great! However, the linked website no longer exists. How do you bring the higher levels to the lower levels in the matrix?
For example, under employee C level, I want to show:
C 500
C 300
E 200
Linked is a PBIX in a Google Drive. Thank you in advance!
https://drive.google.com/drive/folders/1-OzuBZFwT9hof46SKeRQVvtJSspYADPd?usp=sharing
lmilitz - In my opinion, using a pattern can be a great thing but sometimes it can cause you more grief if you don't understand the why it works and why it was made that way. While I have used this patterns techniques, none of my reporting has been exactly as the pattern.
The main thing I did was add an IF to each of your levels to check if there ISBLANK(Result). In those cases, return the Previous Level.
In doing that, you can just use SUM ( 'Sheet 2'[Sales] ). Notice though that it returns all of the sublevels and not just C & E. I also modified [Total Base] if you're really just wanting to use that style of measure.
In short. to accomplish the look/feel you're after you'll really have to come up with the logic that produces your desired output in that context. Probably the most difficult thing with DAX, again in my opinion.
This looks perfect! Thank you very much. The IF + ISBLANK() check in each level is definitely more sustainable than the pattern way. Thanks again!
