Working with Project Online lookup table multivalue hierarchical structure

[Ximo]

Hi all,

I am working on a Power BI slicer to filter projects depending on the department. The Lookup table from Project Online is:

For this Departments field (not the OOB Department from Project Online), a Project can be in one ore more than one Department, it is a multivalue field:

Therefore, the values we can have for each Project can be like:

ProjectId	ProjectName	Departments
1	Project1	Dept1/Dept2/Dept3,Dept1/Dept5/Dept6/Dept8,Dept1/Dept5/Dept15/Dept17
2	Project2	Dept1/Dept2
3	Project3	Dept1/Dept5,Dept1/Dept5/Dept10
4	Project4	Dept1/Dept2/Dept3
5	Project5	Dept1/Dept5/Dept6/Dept8,Dept1/Dept5/Dept6/Dept9
6	Project6	Dept1
7	Project7	Dept1/Dept5/Dept6,Dept1/Dept5/Dept10/Dept13
8	Project8	Dept1/Dept19,Dept1/Dept5/Dept15/Dept16
9	Project9	Dept1/Dept5/Dept10

How can I build a slicer to filter by Departments? I tried the split and hierarchy option but it gives lots of blanks values. Then the other issue is that a project can belong to one or more than one department. Ideally the slicer would be required to filter all projects for a specific department.

Thank you

[Ximo]

Hi @AlexisOlson 

Do you know why there are some blanks in some departments? Any tip to get rid of them?

Thank you!

[AlexisOlson]

It's because there aren't any deeper levels but something has to be in the level 4 column. I just left them unexpanded. I don't think you can get rid of them without fundamentally changing the dimension structure.

[Ximo]

Yeap... unfortunately this seems to be pretty impossible at the moment, unless the default slicer visual is updated with the option to hide blanks, there are some UserVoice posts already on this.

I thought that if it was possible to hide blanks on a matrix table with the ISINSCOPE approach (Dealing with Blanks Ragged Hierarchies in PowerBI (ISINSCOPE)) maybe there was a possibility for the slicer, but not working so far.

Thanks again!

[AlexisOlson · Accepted solution]

I tried tinkering with this some. See if the attached helps at all.

[Ximo]

You got it!!

I added the bidirectional on Projects/Fact and Departments/Fact relationships:

And now it is filtering the projects with that department:

Thanks so much @AlexisOlson!! 💪💪💪