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

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.

Reply

Slicers don't work with IfBlank logic - Power BI bug?

Hey Power BI Community,

 

Have another hopefully not-too-tricky question, and I hope I'm just missing something!  I have a very basic Power BI model shown below that looks like this ->

 

dataModel.png

 

which essentially says a state can have multiple cities, if you filter on a city it should filter the state as well, and a city can have multiple records of sales

 

When I want to do a basic Power BI visual of sales by city with a state slicer, I have this below with "Show items with no data" checked to make sure I'm pulling in cities for the state whether they had sales or whether they didn't have sales (in this case just 3 cities for Texas, and San Antonio has no records of sales):

 

salesByState.png

which is honestly great!  But the only piece I'd love to have is for San Antonio to show 0 instead of the blank()/null value it has today -- when I create a basic DAX function of "Sales - No Blanks = if(sum([Sales]) = blank(), 0, sum([Sales])) to try and resolve this minor aesthetic problem, I suddenly get this view:

 

salesByState2.png

which gives me the 0 for San Antonio that I want, but now it's ignoring my slicer of "State Name = Texas" and is instead showing me cities in other states instead of only those cities in Texas which is what I still want to see - I don't want to see cities from any other state in the visual outside of the state(s) I've filtered on from my slicer

 

Outside of a left join in my raw data which will substantially increase my row count, does anyone know if there's a way in Power BI to do this = blank() trick, but to also have Power BI remember your slicer choices instead of showing you everything? 

 

Many thanks in advance!

1 REPLY 1

Friendly bump of this post since it was hidden over the weekend and Friday night!  Hope everyone's off to a great week!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.