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.
Hello,
I have 2 tables in my data model: Employess and Projects that has a 1 to many relationship. I was wondering if there is a way to filter null values in my model(ie. the relationship in my data model produces a null value, not a null/blank in my actual datasets).
For example, in the below sample tables, i would like a table with just the Employees table(to exclude duplicates employees rows due to multiple projects they are involved in), but add a slicer to be able to filter projects to see which employees are involved in them. However, the projects slicer only includes A and B. I have tried to include the filter Project Category is Blank as well as Project Category is Empty, but am unable to to filter out employees 2 and 6 who are not in the Project Table(note adding the Project Category column and checking off "show items with no data" does allow me ot see them but I would prefer a way in PowerBI to include them without having to export to excel to to the filtering there). Also, the filters may include multiple project categories(for example, I may want to see employees part of Project A and those that do not have projects in the same list)
Thanks in advance for your help!
Employees Table
Employee ID | Employee Name |
1 | Bob |
2 | Glen |
3 | Stuart |
4 | Jennifer |
5 | Sheldon |
6 | Kate |
Projects Table
Employee ID | Project Category |
1 | A |
3 | B |
4 | A |
4 | A |
5 | B |
5 | B |
Hi @Anonymous ,
Here are the steps you can follow:
1. Create measure.
Flag =
var _select=SELECTEDVALUE('Projects Table'[Project Category])
var _123=
SELECTCOLUMNS(
FILTER('Projects Table','Projects Table'[Project Category] = _select),"1",'Projects Table'[Employee ID])
return
IF(
MAX('Employees Table'[Employee ID]) in _123,1,0)
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |