cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Path and Matrix

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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Highlighted
Super User I
Super User I

Re: Path and Matrix

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!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Path and Matrix

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

Highlighted
Frequent Visitor

Re: Path and Matrix

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

Highlighted
Super User I
Super User I

Re: Path and Matrix

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

Proud to be a Super User!




View solution in original post

Highlighted
Helper I
Helper I

Re: Path and Matrix

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

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors