Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @AlexisOlson
Do you know why there are some blanks in some departments? Any tip to get rid of them?
Thank you!
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.
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!
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!! 💪💪💪
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |