cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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
Helper I
Helper I

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors