Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rithikas
Regular Visitor

Suggest a chart in PowerBI to create an organizational chart which displays the team wise.

 Hello Team.

Given the data in the Excel sheet, Employee ID, Employee Name, Employee Team, Manager ID, and Team ID. Task is to create an org chart that displays like a team and when we click on one of the team members it navigates to that particular team. Here one employee falls under two teams. Team ID: Enterprise Leadership :1 Corporate Development:2

Employee ID Name Job Title Location Manager ID Team Name

1RobertChief Executive OfficerNYC Enterprise Leadership
2EmmaExecutive Assistant Office ManagerNYC1Enterprise Leadership
3AdamGroup Head of Corp Development & StrategyNYC1Enterprise Leadership, Corporate Development
4DeeGroup Head of Corp Development & StrategyNYC1Enterprise Leadership
5AngelGroup Head of Corp Development & StrategyNYC1Enterprise Leadership
6WolfStrategyNYC3Enterprise Leadership
7JohnHead of Corp Development & StrategyNYC3Corporate Development

Thank you

1 ACCEPTED SOLUTION

Hi @Rithikas ,

Given that you have more than 1,000 Employees, the visual objects that come with Power BI Desktop do not currently have visual objects that clearly show the structure of such a large number of people. So I suggest you can use DAX + Table visual for this. For example:
Here is the dataset:

vjunyantmsft_0-1713835717879.png

And here I add a table which contains all of the Names of employees for slicer without any relationship:

vjunyantmsft_1-1713835788628.png

vjunyantmsft_7-1713836410059.png

 

Then use this DAX to create a measure:

 

Measure = 
VAR _Name = SELECTEDVALUE('Slicer'[Name])
VAR _TeamName = MAX('Table'[Team Name])
VAR _CurrentTeam = 
CALCULATE(
    MAX('Table'[Team Name]),
    FILTER(
        ALL('Table'),
        'Table'[Name] = _Name
    )
)
RETURN
IF(
    NOT ISBLANK(_Name) &&
    NOT ISBLANK(_TeamName) &&
    IFERROR(FIND(_TeamName, _CurrentTeam), -1) <> -1,
    1,
    IF(
        IFERROR(FIND(_CurrentTeam, _TeamName), -1) <> -1,
        1,
        0
    )
)

 

Then please set it according to the following screenshot:

vjunyantmsft_2-1713836007524.png

The final output is as below:
If I choose nothing, it will show all of the employees:

vjunyantmsft_3-1713836058237.png

If I choose John, it will show employees from team Corporate Development:

vjunyantmsft_4-1713836125145.png

 

If I choose Angel, it will show employees from team Enterprise Leadership:

vjunyantmsft_5-1713836187695.png

If I choose Adam, it will show employees both from team Enterprise Leadership and team Corporate Development:

vjunyantmsft_6-1713836235379.png

 

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-junyant-msft
Community Support
Community Support

Hi @Rithikas ,

After my testing, Power BI Desktop itself does not contain visual objects that would fulfill your requirements, and the form in which your data itself is recorded may also cause problems with visual objects:

vjunyantmsft_0-1713751977593.png

It is recommended that you record in multiple columns or rows.

In addition, I found some visual objects in custom visual that may be able to realize your effect, but since custom visual is a third-party-developed visual object for users and other third parties, I can't help you find the relevant documents or provide more detailed technical support, so please try it out yourself, thank you!

vjunyantmsft_1-1713752122350.png
vjunyantmsft_2-1713752140226.png

vjunyantmsft_3-1713752195946.png


Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-junyant-msft . Thank you for working on this.  Are there any visuals other than custom visuals you suggest for the best general Organizational chart of more than 1000 Employees in PowerBI?

Hi @Rithikas ,

Given that you have more than 1,000 Employees, the visual objects that come with Power BI Desktop do not currently have visual objects that clearly show the structure of such a large number of people. So I suggest you can use DAX + Table visual for this. For example:
Here is the dataset:

vjunyantmsft_0-1713835717879.png

And here I add a table which contains all of the Names of employees for slicer without any relationship:

vjunyantmsft_1-1713835788628.png

vjunyantmsft_7-1713836410059.png

 

Then use this DAX to create a measure:

 

Measure = 
VAR _Name = SELECTEDVALUE('Slicer'[Name])
VAR _TeamName = MAX('Table'[Team Name])
VAR _CurrentTeam = 
CALCULATE(
    MAX('Table'[Team Name]),
    FILTER(
        ALL('Table'),
        'Table'[Name] = _Name
    )
)
RETURN
IF(
    NOT ISBLANK(_Name) &&
    NOT ISBLANK(_TeamName) &&
    IFERROR(FIND(_TeamName, _CurrentTeam), -1) <> -1,
    1,
    IF(
        IFERROR(FIND(_CurrentTeam, _TeamName), -1) <> -1,
        1,
        0
    )
)

 

Then please set it according to the following screenshot:

vjunyantmsft_2-1713836007524.png

The final output is as below:
If I choose nothing, it will show all of the employees:

vjunyantmsft_3-1713836058237.png

If I choose John, it will show employees from team Corporate Development:

vjunyantmsft_4-1713836125145.png

 

If I choose Angel, it will show employees from team Enterprise Leadership:

vjunyantmsft_5-1713836187695.png

If I choose Adam, it will show employees both from team Enterprise Leadership and team Corporate Development:

vjunyantmsft_6-1713836235379.png

 

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.