Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JThompson001
Regular Visitor

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

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 1Category 2Attribute TypeAttribute Value
AUAttribute 1156
AUAttribute 2250
AWAttribute 1134
A Attribute 1121
B Attribute 2210
B Attribute 33300
B Attribute 4175
B Attribute 5720
C Attribute 1132
D Attribute 33500
E Attribute 456000
E Attribute 71500
  Attribute 1100
  Attribute 2200
  Attribute 32500
  Attribute 4134
  Attribute 5550
  Attribute 644000
  Attribute 71200

 

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
    • JThompson001_0-1685569964297.png

       

  • 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
    • JThompson001_2-1685570048975.png

       

  • 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.
    • JThompson001_3-1685570107562.png

       

  • 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
    • JThompson001_4-1685570192994.png

       

  • 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. 
1 ACCEPTED SOLUTION
vicky_
Super User
Super User

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.

View solution in original post

2 REPLIES 2
JThompson001
Regular Visitor

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_
Super User
Super User

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.