Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dt_mc
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION

@dt_mc,

 

[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:

1.PNG

 

from:

IDParent_IDEmployeeSalary
12Mike3000
25Sally5000
32Diane3000
45Randy5000
5 Alan6000
61David2000
74Susan3000
85Carrie4000
98Luther3000
105Max5000





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

8 REPLIES 8
lmilitz
Frequent Visitor

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!

 

 

lmilitz_0-1661821173948.png

 

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.

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



This looks perfect! Thank you very much. The IF + ISBLANK() check in each level is definitely more sustainable than the pattern way. Thanks again!

ChrisMendoza
Resident Rockstar
Resident Rockstar

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])





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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

dt_mc
Frequent Visitor

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

@dt_mc,

 

[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:

1.PNG

 

from:

IDParent_IDEmployeeSalary
12Mike3000
25Sally5000
32Diane3000
45Randy5000
5 Alan6000
61David2000
74Susan3000
85Carrie4000
98Luther3000
105Max5000





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

@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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.