Slicers don't work when using blank()/ifblank() DAX functions

[AndrewLGoldman]

This is a complete copy of the post here: https://community.powerbi.com/t5/Desktop/Slicers-don-t-work-with-IfBlank-logic-Power-BI-bug/td-p/115... - since no one was able to respond, wondering if this might be a Power BI bug?  Thanks in advance!

---

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

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):

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:

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!

[v-yuta-msft]

@AndrewLGoldman ,

How about modifying the measure like pattern below:

Sales - No Blanks =
IF (
    CALCULATE ( SUM ( [Sales] ), KEEPFILTERS ( State[State Name] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( [Sales] ), KEEPFILTERS ( State[State Name] ) )
)

However, if this issue persists, please share some sample data for further analysis.

Regards,

Jimmy Tao 

[AndrewLGoldman]

Hey Jimmy,

Thanks for your response - this is the error I got in the calculation after applying your formula:

MdxScript(Model) (5, 48): Calculation error in measure 'Sales'[Sales (No Blank)]: Cannot convert value 'New York' of type Text to type True/False

Not sure how to attach physical files, otherwise I'd definitely attach my .pbix file to show the data I'm working with!  At a glance, here's what each of the tables in the model look like:

State:

State IDState NameState Acronym

1	New York	NY
2	California	CA
3	Texas	TX

City:

City IDCity NameState ID

1	Houston	3
2	Dallas	3
3	San Antonio	3
4	New York	1
5	San Francisco	2
6	Los Angeles	2
7	Sacramento	2

Sales:

City IDSales

1	10
2	15
4	15
5	10
6	25

[AndrewLGoldman]

Forgot to tag you - sorry about that, Jimmy! @v-yuta-msft