Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi folks
I have managed to create a parent-child hierarchy. My problem is that I want to show the names in the NAME column instead of the MANAGERID. The persons with managerid 0 are level 1 and the persons with managerid 1 report to the manager on lebvel 1. In the ParentChild column I vant to display the names instead of the numbers.
BR
Ole
Solved! Go to Solution.
Hi,@Olea .Thank you for your reply.
You can use matrix visual in Power BI to present your data and ensure that [Manager] appears only at the first level in the data presentation.
How to Create a Hierarchy in Power BI (Explained Simply)
The premise is that your data must exist in a column that correctly reflects the hierarchy, split the data in the [path] column into multiple columns, and the name of each column is a hierarchy.
Like this:
ID |
Managers |
Salespersons |
Trainees |
Revenue |
1 |
Fina Tellwright |
1001 |
Apple |
100 |
2 |
Fina Tellwright |
1001 |
Banana |
200 |
3 |
Fina Tellwright |
1002 |
Strawberry |
150 |
4 |
Fina Tellwright |
1003 |
Grape |
260 |
5 |
Justen Cartwright |
2001 |
Watermelon |
100 |
6 |
Justen Cartwright |
2002 |
Pineapple |
300 |
7 |
Justen Cartwright |
2002 |
Cherry |
560 |
8 |
Parvaiz Bradshaw |
3001 |
Mango |
420 |
9 |
Parvaiz Bradshaw |
3001 |
Lemon |
450 |
10 |
Parvaiz Bradshaw |
3001 |
Mangosteen |
320 |
All the hierarchical columns displayed in the hierarchy need to be real, not just displayed as hierarchical markers all in the same column, which requires you to modify your data source data appropriately, to transform it.
This requires you to modify your data source data appropriately, transform it, and I recommend modifying your data source appropriately (if it doesn't have actual hierarchical fields).
Once the data becomes hierarchical, using a matrix to display the data can work well for your needs.
Create a matrix visual in Power BI - Power BI | Microsoft Learn
Understanding them will help to better handle your data
Pivot columns feature description - Power Query | Microsoft Learn
Unpivot columns - Power Query | Microsoft Learn
Using the Path Dax function makes it easier for you to understand the hierarchy (create calculated column).
PATH function (DAX) - DAX | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,govind_021,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello, @Olea .I am glad to help you.
Like this?
If you want to use Name instead of a created ID, then you need to group the data using the [SALES_ID] with the [MANAGERID] column.
this is my calculate column
ManagerName =
VAR _managerID = 'hierarchyTable'[MANAGERID]
VAR _superiorName =
CALCULATE (
MAX ( 'hierarchyTable'[NAME] ),
FILTER ( ALL ( hierarchyTable ), 'hierarchyTable'[SALES_ID] = _managerID )
)
RETURN
_superiorName
ParentChildName =
VAR _name = 'hierarchyTable'[NAME]
RETURN
IF (
[ManagerName] = BLANK (),
'hierarchyTable'[NAME],
[ManagerName] & "|" & _name
)
Using the index columns to label the data is a very good choice, and they are present in your raw data ([SALES_ID] and [TheManager] column ), I hope my suggestion helps.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Carson Jian
Thank you for your answer. I will try it out. I'm new to PowerBi and dax and am curious to know if can hide the Manager Name in all but the first record when you use the hierarchy in a report, like you can in a MOLAP database? This is a sample from another BI tool.
br
Ole
Hi,@Olea .Thank you for your reply.
You can use matrix visual in Power BI to present your data and ensure that [Manager] appears only at the first level in the data presentation.
How to Create a Hierarchy in Power BI (Explained Simply)
The premise is that your data must exist in a column that correctly reflects the hierarchy, split the data in the [path] column into multiple columns, and the name of each column is a hierarchy.
Like this:
ID |
Managers |
Salespersons |
Trainees |
Revenue |
1 |
Fina Tellwright |
1001 |
Apple |
100 |
2 |
Fina Tellwright |
1001 |
Banana |
200 |
3 |
Fina Tellwright |
1002 |
Strawberry |
150 |
4 |
Fina Tellwright |
1003 |
Grape |
260 |
5 |
Justen Cartwright |
2001 |
Watermelon |
100 |
6 |
Justen Cartwright |
2002 |
Pineapple |
300 |
7 |
Justen Cartwright |
2002 |
Cherry |
560 |
8 |
Parvaiz Bradshaw |
3001 |
Mango |
420 |
9 |
Parvaiz Bradshaw |
3001 |
Lemon |
450 |
10 |
Parvaiz Bradshaw |
3001 |
Mangosteen |
320 |
All the hierarchical columns displayed in the hierarchy need to be real, not just displayed as hierarchical markers all in the same column, which requires you to modify your data source data appropriately, to transform it.
This requires you to modify your data source data appropriately, transform it, and I recommend modifying your data source appropriately (if it doesn't have actual hierarchical fields).
Once the data becomes hierarchical, using a matrix to display the data can work well for your needs.
Create a matrix visual in Power BI - Power BI | Microsoft Learn
Understanding them will help to better handle your data
Pivot columns feature description - Power Query | Microsoft Learn
Unpivot columns - Power Query | Microsoft Learn
Using the Path Dax function makes it easier for you to understand the hierarchy (create calculated column).
PATH function (DAX) - DAX | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
try using Lookup value function
User | Count |
---|---|
84 | |
80 | |
69 | |
46 | |
46 |
User | Count |
---|---|
106 | |
50 | |
42 | |
39 | |
39 |