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!
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.
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!!
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!
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!
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, 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!
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
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.
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!
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. 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!
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!
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.
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!
@@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.
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 :)
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!
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.
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!!!
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.
