Forum Discussion

Ximo's avatar
Ximo
Frequent Visitor
4 years ago
Solved

Working with Project Online lookup table multivalue hierarchical structure

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

 

 

 

Need Help

5 Replies

    • Ximo's avatar
      Ximo
      Frequent Visitor
      4 years ago

      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!! 💪💪💪

  • Ximo's avatar
    Ximo
    Frequent Visitor
    4 years ago

    Hi AlexisOlson 

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

    Thank you!

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Ximo
        Frequent Visitor
        4 years ago

        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!