Mastering Power BI PARENT CHILD Hierarchy: A Complete Guide!!!
HTML-код
- Опубликовано: 23 июл 2024
- FLATTEN + HIERARCHY + RLS + PATH FUNCTIONS + ISINSCOPE + SLICERS
Mastering Power BI Parent-Child Hierarchy: A Complete Guide!!!
Unlock the full potential of hierarchical data management with our comprehensive tutorial on mastering parent-child hierarchies in Power BI. This complete guide will take you through every step needed to create, manage, and visualize parent-child relationships within your datasets. You'll learn how to set up hierarchies, seamlessly navigate through various levels of data, and utilize DAX functions to manipulate and analyze hierarchical data effectively. We'll also cover best practices for displaying hierarchical data using Power BI's powerful visuals, such as tree maps and drill-down charts, to ensure your reports are both insightful and visually appealing. Perfect for data analysts, business intelligence professionals, and Power BI enthusiasts, this guide offers detailed instructions, practical examples, and expert tips. By the end of this tutorial, you will have the skills to handle complex data structures and create dynamic, interactive reports that provide deeper insights into your organization's data. Join us and become a master of parent-child hierarchies in Power BI!
Follow along using the same file!
* repo: github.com/powerbibro/powerbibro
* filename: PBI - 20240514 - Parent Child Hierarchy
* direct link to repo file: github.com/powerbibro/powerbi...
Timestamps:
0:00 - Introduction to Parent-Child Hierarchy
1:43 - PBI Report and Data Model Overview
4:02 - Flatten the parent child hierarchy
5:12 - PATH Function & PATHLENGTH Function
8:45 - PATHITEM Function & LOOKUP Function
11:33 - Variable Lookup Column Logic - Flatten out to every level
18:46 - Create a hierarchy and create hierarchy measures
20:47 - BLANK Issue Example
24:00 - MAX Path Depth & MAX Hierarchy Depth via ISINSCOPE Function
29:57 - New Measure which REMOVES Blanks & Returns a CALC
33:10 - SLICER Issue & Resolution + HIERARCHY SLICER Visual
37:21 - Row-level-security (RLS) & PATHCONTAINS Function
43:15 - Marketplace Hierarchy Visuals & Decomposition Tree
45:28 - Outro - Thank you and Subscribe!
Learn how to master the parent-child hierarchy in Power BI with this complete guide. Understand how to create and manage hierarchies for better data visualization and analysis. Perfect for beginners and advanced users alike!
#powerbidesktop #powerbibro #powerbitutorial
#datavisualization #dataanalytics #businessintelligence #microsoftfabric #dax #hierarchy 
Наука
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!!!
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!!!
you saved my day! Thanks!
Awesome!! So happy the hear that the video was helpful!!!
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!!
Amazing, one of my favorite channels
This compliment just made my day!! Honored to be one of your favorite channels!!!
Super interesting 👍🏻 thank you very much for this video !
My pleasure and thank you!!
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!
This is amazing, dude!
Glad you love it and thanks for sharing!!!
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!!!
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!
Thank you Teacher 👍
What a compliment!!! Thank you so much for sharing and happy to hear you loved it!
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!
Wow thank you so much!!! This Super thanks is incredibly appreciated and I am so happy that the video is helpful!!!
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.
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?
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!!!
Nice!
Thank you!
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.