Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Including Blank/Null Values in Slicer/Filter

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 IDEmployee Name
1Bob
2Glen
3Stuart
4Jennifer
5Sheldon
6Kate

 

Projects Table

Employee IDProject Category
1A
3B
4A
4A
5B
5B
Need Help

2 Replies

  • Duia's avatar
    Duia
    Icon for Resolver II rankResolver II
    4 years ago

    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