Help with data model format of hierarchical data

[SignorSoprano]

Hi, all,

I'm just seeking advise on a best practice pertaining to the structure of tables in the data model, as it relates to slicers and hierarchical data.

For example, I have an employee table similar to the following:

Employee ID	Employee Name	Supervisor ID	Region	Market
101	This Guy
204	That Guy	201	Northeast	Environmental
105	Other Guy	101	West	Design
201	Another Guy	101	Northeast	Environmental

And say I want slicers in my report for 'Region', 'Market' and 'Supervisor', such that if I choose a specific 'Region', the 'Market' and 'Supervisor' slicers will update to show only those employees in that specific 'Region'. And so on for the other slicers.

However, in the 'Supervisor' slicer, I want to show the supervisor's name and not his/her ID. What is the best way to handle this? Should I create a separate supervisor table like such:

Supervisor Key	Supervisor Name
1	This Guy
2	Another Guy

And use the 'Supervisor Key' in the first table instead of 'Supervisor ID' and use this 'Supervisor Name' as the data source for the 'Supervisor' slicer?

This would remove the hierarchical structure of the original table, however, and I would like to preserve it. In that case, should I just add an additional column in the first table and have it be the 'Supervisor Key' and relate it to the 'Supervisor Key' in this separate supervisor table?

Thanks for your advice.

[v-yingjl · Accepted solution]

Hi @SignorSoprano ，

A straightforward way to solve this problem is to create a calulated column to save supervisor name and use it as the slicer:

Supervisor Name = 
SWITCH(
    TRUE(),
    'Table'[Supervisor ID] = 101, "This Guy",
    'Table'[Supervisor ID] = 201, "Another Guy",
    'Table'[Supervisor ID] = 105, "Other Guy",
    'Table'[Supervisor ID] = 204, "That Guy", 
    "Others"
)

Best Regards,
Yingjie Li

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

[SignorSoprano]

In short, I should just add another column in the original table named 'Supervisor Name'? This will work. I just didn't know what was best practice.

However, the DAX code with the switch statement is not feasible as the list of supervisors numbers in the hundreds. But, I can just add the 'Supervisor Name' column in my data source.

Thanks, @v-yingjl 

[v-yingjl]

Hi @SignorSoprano ,

You can use lookupvalue() function to create a calculated column:

Column = 
LOOKUPVALUE('Table'[Employee Name],'Table'[Employee ID],'Table'[Supervisor ID])

Best Regards,
Yingjie Li

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

[SignorSoprano]

Yep, I sure can. Thanks for your advice.