Slicer that filters rows for the selected value and also rows with a null value

[JThompson001]

Hello, I'm still very new to Power BI and teaching myself with small projects.

Currently I am trying to create a report to look at an application configuration table. I've included a simplified generized version here:

Category 1	Category 2	Attribute Type	Attribute Value
A	U	Attribute 1	156
A	U	Attribute 2	250
A	W	Attribute 1	134
A		Attribute 1	121
B		Attribute 2	210
B		Attribute 3	3300
B		Attribute 4	175
B		Attribute 5	720
C		Attribute 1	132
D		Attribute 3	3500
E		Attribute 4	56000
E		Attribute 7	1500
		Attribute 1	100
		Attribute 2	200
		Attribute 3	2500
		Attribute 4	134
		Attribute 5	550
		Attribute 6	44000
		Attribute 7	1200

Category 1

A

B

C

D

E

F

G

Category 2

T

U

V

W

X

Y

Z

• The way this table works is that it defines the values of 7 attributes for various combinations of Category 1 and Category 2.
• The rows at the bottom with null values in both category columns are the "Defaults". The rows with Category 1 or Category 1 and Category 2 populated are the "Overrides"
• For any given Category 1 value or Category 1 / Category  2 combination, the system will use the default values for the attributes UNLESS there is a row with matching Category values, in which case those rows will be used to determin the attribute value, thus "overriding" the default.
• I want to create a report with dropdown slicers for category 1, category 2 and Attribute that will filter by returning rows with matching column values as well as an null values. The only rows to be excluded would be rows with non-matching values in that column.
• I can create the functionality by using vertical list slicers and selecting two values at once, the desired value plus the (Blank) selection, which returns all the matchign rows plus the null. But what I want is to be able to do this in a single select dropdown, such that selecting A would return rows with A as well as null values.
• So here is a sample with no selections
  • 
• By selecting A in the Category 1 dropdown I would get all rows with "A" in Category 1 as well as rows with NULL in Category 1
  • 
• By selecting "U" in Category 2, I would get all rows with "U" in Category 2, plus rows that are NULL. It doesn't return the rows with "W" for instance.
  • 
• Conversely, if I select "W" in Category 2, I wouldn't get the rows with "U" in Category 2, just rows with "W" or Null
  • 
• Thanks for you time and help. I'm hoping there is a way to do this without getting into heavy Dax, but if it's required then be aware I am just beginning to learn it, and most of what I've done so far is just copy/pasting other people's examples. 

[JThompson001]

Thank you! Perfect tutorial to learn the underlying principles. I laughed at how close my own examples were to the ones the author uses. Thanks again!

[vicky_ · Accepted solution]

I think this tutorial is a good start: https://apexinsights.net/blog/or-xor-slicing

Basically, you'll need to create some new tables (to use in the slicers) and use DAX to return the rows meeting your conditions. In your case, you might need to use the ISFILTERED() to include blank values.