Including Blank/Null Values in Slicer/Filter

[Anonymous]

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

[Duia]

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

[MahyarTF]

Hi,

Please watch the below video :
https://www.youtube.com/watch?v=pEAwvMkpA8I