Use name column in parent-child hierarchy

[Olea]

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

[Anonymous]

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.

[Olea]

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

[Anonymous · Accepted 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.

[govind_021]

try using Lookup value function