Exactly what I needed help with, and much more! Thank you for explaining the necessity of each step. Many videos are watching someone simply type up some DAX which leaves this self-taught-during-free-time-Wannabe :) wondering "but why?". This video cleared up a few lingering questions as well as completely resolved my current need. Subscribing!
This was THE game changer video! Very much appreciated 😊 two subsequent issues I’ve had even before watching the video is listed below. 1. I use slicers in my model and to simply filter out the blanks does not work for me as it causes some child nodes to disappear. Is there a better solution because this is related to my second issue. 2. I’ve tried the hierarchy slicer as a work around to the above and used the selection for empty. But, it has performance issues that is not sustainable causing my model to freeze possible due to large dataset, no clue. Hoping one might have some ideas, pointers in right direction because, I need to get this all working for a slicer. Thanks again for sharing this video and in advance for any further insight to my issues.
This is a very good question and an issue I also experienced after filming this video. I found a fix and it may be applicable for you. I found that the "blank" child records were actually associated with the "parent" of that specific group. So if I filtered blanks, my numbers incorrectly dropped, as the "parents" values were being excluded. To fix this, i created another layer of logic during the PATH process. I populated the first null path/level column with the previous named value, this would capture the manager record one level before where the manager actually resides, so the manager shows up under his own name for the records he is associated with. let me know if this helps!
@ thank you for your reply, I am trying the logic presented and to no avail 😩 can you share your dax code for this logic so I can see where I’m going wrong.
I watched this video from my other id where i was not signed up on youtube for a certain reason, so i am leaving a comment from this id, "Thank you! this video made my day", please make more videos like these which are ususally not available on internet with such explanation.
This is excellent information!!! Is there a way to use Path function if the child has more than one parent? I keep getting and error stating item from parent is not found in child column.
Thanks for sharing and happy to hear you find it valuable!! For that error, the function does need a single parent to work properly. You’ll need to adjust your dataset removing the multiple values.
Amazing video, this is the exactly what i am looking for. Thankyou so much for the video and the way you explain is awesome 😊 Jut need 1 help how to handle if child is mapped to skip level directly Example: level 3 directly reporting to level 1
So happy to hear this is exactly what you are looking for! Appreciate your support and thank you for sharing! Here's my thoughts on your question. If a level 3 has a level 1 set as the parent then technically it is actually a level 2, so we'll need to update the data to force a break/space. It will require data manipulation, but to solve this, I would create a dummy record called 'level 2 empty' and have this roll up to the level 1, then I would change the level 2s (who need to be level threes) to be the child of this new dummy level 2 record. This should create the gap. I hope this helps, please let me know how you end up solving this one!!
Great video and well presented. As per another comment, is there really no way to dynamically scale to the hierarchy depth in the data? Given that we know the Max Depth it seems we know the required depth. How would you scale this if you had a multi parent scenario? A child can have two or more parents. In this scenario I am only trying to visualize the relationship, there is no calculation associated with this. In my data the child is repeated for each valid parent ID.
Thank you for sharing and I'm happy to hear to enjoy the content! To my knowledge, I have yet to find a way to dynamically scale the path hierarchy creation to create a column for every level automatically. If you find something please do let me know! For this path function, it works on a discrete hierarchy, so one child to one parent. If you're wanting to visual the relationships of multiparent hierarchies, you may find value is searching the PBI marketplace for a hierarchy visual specifically tailored to that need. Hope this helps, please do share what you decide to go with!
HI, I was looking for something like this for quite some time and I finally found it as a complete guide, thank you for your work. Quick question/mini-scenario: - I configured two measures to work with the hierarchy - count of people and sales (like in your example) - I "bucketed" the Sales into Min, Med, Max (based on a arbitrary value) - a simple Switch statement - if I display a matrix with Hierarchy and Sales then whenever I select a bucket then the hierarchy nicely displays only the people in the bucket (not showing the other people) - if, to the above case, I add the count then the hierarchy will the selected bucket value but also empty rows. This makes sense since the count measure is also in that matrix and it does have a value - is there any way to "make" the Sales measure take precedence?
What a great question and thank you for your support! My first thought is to understand more about the switch statement which buckets the values, is this a measure or calculated column? I would suggest doing an experiment where this is converted to a calculated column, which is little more complex and rigid as you'll need to write DAX which predetermines the granularity for which the sales are bucketed (week, month, year etc..). But then, as that is now a column within the data, I'd be curious to hear how that impacts your end results.
@@PowerBIBro The bucket can be anything that makes sense in the data context. For example, Sales under 10.000 USD are Min, Sales over 20.000 Max and Med is in the middle. A simple formula could be: Sales range = SWITCH(TRUE(),[Sales]20000,"Max","Med") Any other measure would basically do like region, performance, count of Orders etc. I hope it makes sense.
I'm facing an interesting issue after following all of your steps. I've applied this approach to a financial account hierarchy situation. When I add my measure that takes into consideration the ISINSCOPE function, I'm still getting blanks. Then when I try to filter accounts at the lower levels (meaning filtering out the blanks), it is filtering at the higher levels as well and I'm losing those data points. Have you faced this issue before?
Thank you for sharing this question! Offhand I'm having difficulty envisioning the details of the issue, but my initial thought is to check both the hierarchy structure and the isinscope function which adds up all the levels. If the row are still appearing, try deconstructing the measure, returning each part, and ensuring that the values which the > function are returning appropriately. This is my first thought, please let me know if you're able to resolve the issue. It's difficult to troubleshoot this one without seeing your actual data structure. Appreciate this question!
Amazing video. How would you go about creating a card based on the selected value of a leader in the hierarchy slicer since it selects multiple levels? I am looking to show information like the selected leader's job title and level. Also I noticed the actual leader doesn't show as their own row in the matrix. Is there a workaround to show them? For example, I'd like to show vacation hours accrued for leaders and their direct reports.
Thank you for the compliment and thank you for these questions! I'm happy to hear that you found the video helpful. Both your questions are great, and both are potentially complicated :) Offhand, I'll need to do some research into how to pull the desired results for both examples, but I love both use cases. In the meantime, please let me know if you find a solution!
Hi, This was a great video. Very helpful. I'm trying to use this logic for creating a hierarchy for ADO workitems. Can we integrate string columns like Title, State of workitems into this matrix visual?
Love this question and leveraging this method for reporting ADO work items is completely possible. I have created multiple data models utilizing this process to create hierarchies for Epics/Features/Stories/Tasks etc.. you can leverage this same methodology and return items like title/state etc. I'll need to research a method to return these files in the same matrix visual though, I'll look into it! If you come up with something please do share!
thanks for this, it sorted half of my problem, but there is a slight error that actually shows in your video as well in minute 33:12. if you add up the headcount under Juwihu it equals 57 not 58 and the same with the sales the number is less. the same in minute 39:55 under Cykaja, it is still including the manager in the total number. I have the same issue in my data set as well. did you figure out a resolution for this?
This is an excellent question and thank you for reaching out! You are correct, using the Juwihu example, the total is 58 because it's taking 57 + 1 for Juwihu themselves. So the total lines are adding everything below, plus 1 for the person at the top of the hierarchy. There is a way you can evolve this and it will be within the path function process/steps. Essentially, you need to create an expression which check for the first blank, then moves the person's name into one path below where they actually are. This will allow you to pull out the values of the person themselves, having them showing in their own hierarchy vs being nested in the totals. This explanation is tricky to do in written form as you're correct that it's a fairly complex solution. I hope this help direct you, please let me know if you are able to work something and which path you go down to solve the problem!
Love the question and thank you for reaching out! For this process, you'll need to proactively build out empty layers for future growth. To my knowledge there isn't a dynamic way to do this using the methods via PATH functions creating columns. Let me know if you find something different!
Thanks so much for this video, super helpful and detailed!! My dataset is from a filesystem and I am building reporting on the files within that system. The challenge is that there is currently 32 levels in the max depth and could be more next time its refreshed. Any alternative suggestions for dealing with such deep hierarchies?
Thank you for sharing and I’m happy to hear you think the video is helpful and detailed! What a great question, 32 levels is huge! Offhand I’m not able to recall a method to dynamically create levels but I wouldn’t be surprised if one exists. I would probably go brute force and just manually build out a hierarchy 50+ levels to account for future growth and then be set. Thanks for our support and please share with others!
@sanchowitfurrows1561 excellent question! The goal for this tutorial was to leverage the PATH functions specifically and I don’t believe those are available in power query. Please let me know if you have any power query transforming suggestions!
Hi Power BI Bro, Thank you very much for this great video. I actually got really far with my solution using your video. Thank you! I do have an issue with the blanks when I am using xViz Performance flow visual in Power BI. I am using it to create an organization hierarchy. It is such that if the lower level of the hierarchy has no value than the upper level will not get shown either. I.e. the highest level CLT has OPR and CIT as immediate child nodes. CIT has no further children. So when the hierarchy is extended till level 2 you do see CIT and OPR. But if the hierarchy is extended to 3, you only see OPR. I would like to keep seeing OPR. I do have an excel file with the structure and all the calculated columns done. If you kindly reply then I can showcase it. Thank you for your knowledge sharing! Best Regards Exactitude (Rehman)
Thank you for both the question and compliment, I'm happy to hear you found the video to be great! I appreciate the summarized description of the problem, unfortunately offhand I'm not deeply familiar with the xViz Performance Flow component. I'm unfortunately unsure how to solve your xViz issue without a deep dive.
Thank you for the tutorial however I have a question, Each time i hit the '+' button all my information just slides into the deepest level of my hierarchy and i have no information on my row, i tried multiple things but nothing worked so far. (some information just in case : I'm working on a database already flattened, hierarchy has been built thanks to column names, I foud a way to re-create the 2 mesures of depth like in the video and hide the blanks.)
Thanks for your question and I appreciate you sharing! These types of issues are typically associated with the steps performed at the point in which the hierarchy itself is created (right clicking and adding fields). It may be beneficial to go back and watch the below step, ensuring you add/build the layers in your hierarchy field utilizing the correct order as the order in which fields are added define the order in which they drill to. 18:46 - Create a hierarchy and create hierarchy measures
Great vid! I have a problem though, I want to show text fields in my matrix. So when doing the step from min 30:00 onwards, I cannot format my measure correctly due to the conflicting data types. Is there a way to achieve this?
Amazing video. Question. The Data set i'm working with is a ragged multi-parent hierarchy. IE, One child can have multiple parents. As far as I know, the PATH function has issues in this regard. How can I overcome this?
Thanks for the update! I was looking into a solution for you, please share how you solved it and what the work around is. Interested to see how you solved it!!!
@@PowerBIBro I appreciate that you were looking into it!. I unfortunately had to compromise by turning it into a single parent hierarchy by making the children that belonged to multiple parents, unique. So, where they exist in multiple places, the dax formula would just append a "-" followed by a number starting from 1, continuing upwards for every occurrence of that ID. It's a cheap workaround but oh well :D
Love this question! Thank you for asking it and I appreciate your support! Yes, offhand this definitely seems possible and it will require some more advanced DAX manipulation. Essentially, if the goal is to return Name and Title in the same value, I would evolve the path functions to return both of these values independently, then concatenate them together as the result in what is being returned. I hope this helps point you in the correct direction, please let me know how you end up solving this one!
Thanks for your question! A role type of hierarchy (employee/manager/director/etc…) would have to be treated differently. In your example, taking the roles out, the manager is a direct report to CEO technically. The entire PATH function system is based on child/parent IDs, so you’d need to create your own new data set which has the manager linking to a blank director who then links to CEO. It’s all about the parent/child links in your dataset :)
Question: around 33:05, you add the Sales to your values and use the same formula to eliminate blanks, which has worked for me. However, it seems the my numbers are affected, as it's counting blanks as well. I've read online to change the datatype to "Whole numbers" for that column, but it doesn't seem to help. Any solution?
Figured it out! Instead of using the DISTINCTCOUNTNOBLANKS for my numbers column (in my case, quantities), i used MIN, which for my case, returned the exact values without adding blanks.
Great video, BRO!!! This has been extremely helpful. I'm working with a data set where parents have unique values from the children. How would you isolate the parent values from children without incorporating the children into the totals? I'm creating a chokepoint analysis tool that drills down from the strategic level to the direct level. 45:52
Thank you for this question and your support!!! I've been pondering your question and I'm not yet fully clear on your end goal, can you elaborate in a little more detail? Maybe provide some data examples if possible or an end goal mockup? If you like, you can email me additional details at powerbibro.rob@gmail.com and I'll be happy to look into it!!!
Thanks for sharing this question! To help confirm your ask, are you stating that parents without children are not showing up? If so, that should not be happening. As an example, in the file provided, go to the tab [Hierarchy] and see the person 'Qa Dibivo' under 'Fufe Be', this person has no children but is showing up. Hopefully this helps, if I'm missing the question please let me know and I'll be happy to help!
@@PowerBIBro Hi. i have the same problem. 6 level hiearachy. only one root has 6 level other 3,4,5. if i am filtering Not Blank it hides all roots except root with 6 level depth. because other root has value Blanck in level_06 that is logicaly. how it works in your case) magic?
@@PowerBIBro Maybe it is outside the scope of the video's subject, but I'd like to ask: How would one go about configuring e-mail accounts that are outside the sales hierarchy to still see all data? Would one have to add them to the path as a level above the highest? (Like a director that wants to see all data, but isn't on the sales hierarchy)
Totally applicable to this scenario and super easy to setup :) You'll want to another another role to RLS model, call it "See Everything" or whatever you prefer. For this role, we'll use it as a means to bypass the security hierarchy, in the Dax editor for this role simply put 1 = 1, or any expression that is always true. Then when you test this role, if you add a director to the permission of this role, they'll see everything and bypass the RLS hierarchy.
Totally correct and thanks for sharing your thoughts! A best practice is to build beyond your current level structure. Essentially returning nulls as placeholders for future path levels.
Exactly what I needed help with, and much more! Thank you for explaining the necessity of each step. Many videos are watching someone simply type up some DAX which leaves this
self-taught-during-free-time-Wannabe :) wondering "but why?". This video cleared up a few lingering questions as well as completely resolved my current need. Subscribing!
Wow I love this and thank you so much for sharing!!!
I am so happy to hear the video cleared up questions and resolved your current need!!!
This was THE game changer video! Very much appreciated 😊 two subsequent issues I’ve had even before watching the video is listed below.
1. I use slicers in my model and to simply filter out the blanks does not work for me as it causes some child nodes to disappear. Is there a better solution because this is related to my second issue.
2. I’ve tried the hierarchy slicer as a work around to the above and used the selection for empty. But, it has performance issues that is not sustainable causing my model to freeze possible due to large dataset, no clue.
Hoping one might have some ideas, pointers in right direction because, I need to get this all working for a slicer.
Thanks again for sharing this video and in advance for any further insight to my issues.
This is a very good question and an issue I also experienced after filming this video. I found a fix and it may be applicable for you. I found that the "blank" child records were actually associated with the "parent" of that specific group. So if I filtered blanks, my numbers incorrectly dropped, as the "parents" values were being excluded. To fix this, i created another layer of logic during the PATH process. I populated the first null path/level column with the previous named value, this would capture the manager record one level before where the manager actually resides, so the manager shows up under his own name for the records he is associated with.
let me know if this helps!
@ thank you for your reply, I am trying the logic presented and to no avail 😩 can you share your dax code for this logic so I can see where I’m going wrong.
Thanks alot ,this has given alot of insight, i can now use it for creating projects WBS and enhance analytics
My pleasure and thank you for sharing this compliment!!
I watched this video from my other id where i was not signed up on youtube for a certain reason, so i am leaving a comment from this id, "Thank you! this video made my day", please make more videos like these which are ususally not available on internet with such explanation.
Awesome!! Thank you so much for sharing this great feedback and I'm excited to hear the video made your day!!!
This is excellent information!!! Is there a way to use Path function if the child has more than one parent? I keep getting and error stating item from parent is not found in child column.
Thanks for sharing and happy to hear you find it valuable!!
For that error, the function does need a single parent to work properly. You’ll need to adjust your dataset removing the multiple values.
Amazing video, this is the exactly what i am looking for. Thankyou so much for the video and the way you explain is awesome 😊
Jut need 1 help how to handle if child is mapped to skip level directly
Example: level 3 directly reporting to level 1
So happy to hear this is exactly what you are looking for! Appreciate your support and thank you for sharing!
Here's my thoughts on your question. If a level 3 has a level 1 set as the parent then technically it is actually a level 2, so we'll need to update the data to force a break/space. It will require data manipulation, but to solve this, I would create a dummy record called 'level 2 empty' and have this roll up to the level 1, then I would change the level 2s (who need to be level threes) to be the child of this new dummy level 2 record. This should create the gap.
I hope this helps, please let me know how you end up solving this one!!
Great video and well presented. As per another comment, is there really no way to dynamically scale to the hierarchy depth in the data? Given that we know the Max Depth it seems we know the required depth.
How would you scale this if you had a multi parent scenario? A child can have two or more parents. In this scenario I am only trying to visualize the relationship, there is no calculation associated with this. In my data the child is repeated for each valid parent ID.
Thank you for sharing and I'm happy to hear to enjoy the content!
To my knowledge, I have yet to find a way to dynamically scale the path hierarchy creation to create a column for every level automatically. If you find something please do let me know!
For this path function, it works on a discrete hierarchy, so one child to one parent. If you're wanting to visual the relationships of multiparent hierarchies, you may find value is searching the PBI marketplace for a hierarchy visual specifically tailored to that need.
Hope this helps, please do share what you decide to go with!
HI, I was looking for something like this for quite some time and I finally found it as a complete guide, thank you for your work.
Quick question/mini-scenario:
- I configured two measures to work with the hierarchy - count of people and sales (like in your example)
- I "bucketed" the Sales into Min, Med, Max (based on a arbitrary value) - a simple Switch statement
- if I display a matrix with Hierarchy and Sales then whenever I select a bucket then the hierarchy nicely displays only the people in the bucket (not showing the other people)
- if, to the above case, I add the count then the hierarchy will the selected bucket value but also empty rows. This makes sense since the count measure is also in that matrix and it does have a value
- is there any way to "make" the Sales measure take precedence?
What a great question and thank you for your support! My first thought is to understand more about the switch statement which buckets the values, is this a measure or calculated column? I would suggest doing an experiment where this is converted to a calculated column, which is little more complex and rigid as you'll need to write DAX which predetermines the granularity for which the sales are bucketed (week, month, year etc..). But then, as that is now a column within the data, I'd be curious to hear how that impacts your end results.
@@PowerBIBro The bucket can be anything that makes sense in the data context. For example, Sales under 10.000 USD are Min, Sales over 20.000 Max and Med is in the middle. A simple formula could be:
Sales range = SWITCH(TRUE(),[Sales]20000,"Max","Med")
Any other measure would basically do like region, performance, count of Orders etc.
I hope it makes sense.
I'm facing an interesting issue after following all of your steps. I've applied this approach to a financial account hierarchy situation. When I add my measure that takes into consideration the ISINSCOPE function, I'm still getting blanks. Then when I try to filter accounts at the lower levels (meaning filtering out the blanks), it is filtering at the higher levels as well and I'm losing those data points. Have you faced this issue before?
Thank you for sharing this question! Offhand I'm having difficulty envisioning the details of the issue, but my initial thought is to check both the hierarchy structure and the isinscope function which adds up all the levels. If the row are still appearing, try deconstructing the measure, returning each part, and ensuring that the values which the > function are returning appropriately.
This is my first thought, please let me know if you're able to resolve the issue. It's difficult to troubleshoot this one without seeing your actual data structure.
Appreciate this question!
Amazing video.
How would you go about creating a card based on the selected value of a leader in the hierarchy slicer since it selects multiple levels? I am looking to show information like the selected leader's job title and level.
Also I noticed the actual leader doesn't show as their own row in the matrix. Is there a workaround to show them? For example, I'd like to show vacation hours accrued for leaders and their direct reports.
Thank you for the compliment and thank you for these questions! I'm happy to hear that you found the video helpful.
Both your questions are great, and both are potentially complicated :) Offhand, I'll need to do some research into how to pull the desired results for both examples, but I love both use cases. In the meantime, please let me know if you find a solution!
Hi, This was a great video. Very helpful. I'm trying to use this logic for creating a hierarchy for ADO workitems. Can we integrate string columns like Title, State of workitems into this matrix visual?
Love this question and leveraging this method for reporting ADO work items is completely possible. I have created multiple data models utilizing this process to create hierarchies for Epics/Features/Stories/Tasks etc.. you can leverage this same methodology and return items like title/state etc. I'll need to research a method to return these files in the same matrix visual though, I'll look into it!
If you come up with something please do share!
Thanks for the quick response. I'm trying it out. Will share what I come up with.
thanks for this, it sorted half of my problem, but there is a slight error that actually shows in your video as well in minute 33:12. if you add up the headcount under Juwihu it equals 57 not 58 and the same with the sales the number is less. the same in minute 39:55 under Cykaja, it is still including the manager in the total number. I have the same issue in my data set as well. did you figure out a resolution for this?
This is an excellent question and thank you for reaching out! You are correct, using the Juwihu example, the total is 58 because it's taking 57 + 1 for Juwihu themselves. So the total lines are adding everything below, plus 1 for the person at the top of the hierarchy. There is a way you can evolve this and it will be within the path function process/steps. Essentially, you need to create an expression which check for the first blank, then moves the person's name into one path below where they actually are. This will allow you to pull out the values of the person themselves, having them showing in their own hierarchy vs being nested in the totals.
This explanation is tricky to do in written form as you're correct that it's a fairly complex solution. I hope this help direct you, please let me know if you are able to work something and which path you go down to solve the problem!
Thank you for amazing video
How can we do it when we add a new level? When I want to do this dynamically rather than statically, it gives an error. :/
Love the question and thank you for reaching out! For this process, you'll need to proactively build out empty layers for future growth. To my knowledge there isn't a dynamic way to do this using the methods via PATH functions creating columns. Let me know if you find something different!
Thanks so much for this video, super helpful and detailed!! My dataset is from a filesystem and I am building reporting on the files within that system. The challenge is that there is currently 32 levels in the max depth and could be more next time its refreshed. Any alternative suggestions for dealing with such deep hierarchies?
Thank you for sharing and I’m happy to hear you think the video is helpful and detailed!
What a great question, 32 levels is huge! Offhand I’m not able to recall a method to dynamically create levels but I wouldn’t be surprised if one exists. I would probably go brute force and just manually build out a hierarchy 50+ levels to account for future growth and then be set.
Thanks for our support and please share with others!
did you consider to flatten and pivot columns using Power Query?
@sanchowitfurrows1561 excellent question!
The goal for this tutorial was to leverage the PATH functions specifically and I don’t believe those are available in power query. Please let me know if you have any power query transforming suggestions!
Outstanding video! Cannot wait to implement this! Soooo many use cases on our end! Thank you very much for this!
Awesome!!! Thank you for sharing and glad you love it!!
Hi Power BI Bro,
Thank you very much for this great video. I actually got really far with my solution using your video. Thank you!
I do have an issue with the blanks when I am using xViz Performance flow visual in Power BI. I am using it to create an organization hierarchy. It is such that if the lower level of the hierarchy has no value than the upper level will not get shown either. I.e. the highest level CLT has OPR and CIT as immediate child nodes. CIT has no further children. So when the hierarchy is extended till level 2 you do see CIT and OPR. But if the hierarchy is extended to 3, you only see OPR. I would like to keep seeing OPR.
I do have an excel file with the structure and all the calculated columns done. If you kindly reply then I can showcase it.
Thank you for your knowledge sharing!
Best Regards
Exactitude (Rehman)
Thank you for both the question and compliment, I'm happy to hear you found the video to be great!
I appreciate the summarized description of the problem, unfortunately offhand I'm not deeply familiar with the xViz Performance Flow component. I'm unfortunately unsure how to solve your xViz issue without a deep dive.
@@PowerBIBro I appreciate you getting back to me. I did get the issue resolved. Will it be ok if I link to the solution video from here?
@wajiharehman9011 that’ll be great!
Thank you for the tutorial however I have a question,
Each time i hit the '+' button all my information just slides into the deepest level of my hierarchy and i have no information on my row, i tried multiple things but nothing worked so far.
(some information just in case :
I'm working on a database already flattened, hierarchy has been built thanks to column names,
I foud a way to re-create the 2 mesures of depth like in the video and hide the blanks.)
Thanks for your question and I appreciate you sharing! These types of issues are typically associated with the steps performed at the point in which the hierarchy itself is created (right clicking and adding fields).
It may be beneficial to go back and watch the below step, ensuring you add/build the layers in your hierarchy field utilizing the correct order as the order in which fields are added define the order in which they drill to.
18:46 - Create a hierarchy and create hierarchy measures
Great vid! I have a problem though, I want to show text fields in my matrix. So when doing the step from min 30:00 onwards, I cannot format my measure correctly due to the conflicting data types. Is there a way to achieve this?
Great question and thank you for asking. I’ll make a note to look into this
Amazing video. Question. The Data set i'm working with is a ragged multi-parent hierarchy. IE, One child can have multiple parents. As far as I know, the PATH function has issues in this regard. How can I overcome this?
I figured out a way around it! Amazing video!! Helps a lot.
Thanks for the update! I was looking into a solution for you, please share how you solved it and what the work around is. Interested to see how you solved it!!!
@@PowerBIBro I appreciate that you were looking into it!. I unfortunately had to compromise by turning it into a single parent hierarchy by making the children that belonged to multiple parents, unique. So, where they exist in multiple places, the dax formula would just append a "-" followed by a number starting from 1, continuing upwards for every occurrence of that ID. It's a cheap workaround but oh well :D
@tameshdoobay6058 genius and I love it
Can you please share the solution/dax for this?
Is there a way to have the level 01-08 pull multiple variables like name AND title so they both show up in the decomposition tree?
Love this question! Thank you for asking it and I appreciate your support!
Yes, offhand this definitely seems possible and it will require some more advanced DAX manipulation. Essentially, if the goal is to return Name and Title in the same value, I would evolve the path functions to return both of these values independently, then concatenate them together as the result in what is being returned.
I hope this helps point you in the correct direction, please let me know how you end up solving this one!
Amazing, one of my favorite channels
This compliment just made my day!! Honored to be one of your favorite channels!!!
How would you handle an org structure that skips levels? Like if a manager reported directly to the CEO and didn't have a director over them.
Thanks for your question!
A role type of hierarchy (employee/manager/director/etc…) would have to be treated differently.
In your example, taking the roles out, the manager is a direct report to CEO technically. The entire PATH function system is based on child/parent IDs, so you’d need to create your own new data set which has the manager linking to a blank director who then links to CEO.
It’s all about the parent/child links in your dataset :)
Question: around 33:05, you add the Sales to your values and use the same formula to eliminate blanks, which has worked for me. However, it seems the my numbers are affected, as it's counting blanks as well. I've read online to change the datatype to "Whole numbers" for that column, but it doesn't seem to help. Any solution?
Figured it out! Instead of using the DISTINCTCOUNTNOBLANKS for my numbers column (in my case, quantities), i used MIN, which for my case, returned the exact values without adding blanks.
Love how you're solving your problems so quickly!!! Great job!!!
Great video, BRO!!! This has been extremely helpful.
I'm working with a data set where parents have unique values from the children. How would you isolate the parent values from children without incorporating the children into the totals?
I'm creating a chokepoint analysis tool that drills down from the strategic level to the direct level.
45:52
Thank you for this question and your support!!! I've been pondering your question and I'm not yet fully clear on your end goal, can you elaborate in a little more detail? Maybe provide some data examples if possible or an end goal mockup? If you like, you can email me additional details at powerbibro.rob@gmail.com and I'll be happy to look into it!!!
Super interesting 👍🏻 thank you very much for this video !
My pleasure and thank you!!
This is amazing, dude!
Glad you love it and thanks for sharing!!!
you saved my day! Thanks!
Awesome!! So happy the hear that the video was helpful!!!
When filtering the blanks out in the slicer it filters out some of the first level attributes as in my dataset some only has a parent and nothing more
Thanks for sharing this question! To help confirm your ask, are you stating that parents without children are not showing up? If so, that should not be happening. As an example, in the file provided, go to the tab [Hierarchy] and see the person 'Qa Dibivo' under 'Fufe Be', this person has no children but is showing up.
Hopefully this helps, if I'm missing the question please let me know and I'll be happy to help!
@@PowerBIBro Hi. i have the same problem. 6 level hiearachy. only one root has 6 level other 3,4,5. if i am filtering Not Blank it hides all roots except root with 6 level depth. because other root has value Blanck in level_06 that is logicaly. how it works in your case) magic?
Thank you Teacher 👍
What a compliment!!! Thank you so much for sharing and happy to hear you loved it!
Amazing video, thank you very much!
Thanks for sharing!!!
@@PowerBIBro Maybe it is outside the scope of the video's subject, but I'd like to ask:
How would one go about configuring e-mail accounts that are outside the sales hierarchy to still see all data?
Would one have to add them to the path as a level above the highest? (Like a director that wants to see all data, but isn't on the sales hierarchy)
Totally applicable to this scenario and super easy to setup :) You'll want to another another role to RLS model, call it "See Everything" or whatever you prefer. For this role, we'll use it as a means to bypass the security hierarchy, in the Dax editor for this role simply put 1 = 1, or any expression that is always true. Then when you test this role, if you add a director to the permission of this role, they'll see everything and bypass the RLS hierarchy.
This is great, but it requires that your number of levels does not increase, otherwise this breaks down.
Totally correct and thanks for sharing your thoughts! A best practice is to build beyond your current level structure. Essentially returning nulls as placeholders for future path levels.
Thanks!
Wow thank you so much!!! This Super thanks is incredibly appreciated and I am so happy that the video is helpful!!!
Nice!
Thank you!