Replacing Missing values with 0, after joining tables

[handwepg]

I have two tables:

Table A

ID	State	Score
1234	NC	114
2345	PA	214
3456	NM	145
4567	NC	111
5678	PA	236
6789	PA	204

Table B

State	Cases	Date
NC	4,367	4/21/20
PA	32,664	4/21/20
NC	7,202	4/28/20
PA	12,544	4/28/20

Currently, I have the relationship between A and B as joining on State with

• Cardinality: Many to Many
• Cross Filter direction: Single (A filters B)

What I believe I am currently getting is

ID	State	Score	Cases	Date
1234	NC	114	4,367	4/21/2020
1234	NC	114	7,202	4/28/2020
2345	PA	214	32,664	4/21/2020
2345	PA	214	12,544	4/28/2020
3456	NM	145
4567	NC	111	4,367	4/21/2020
4567	NC	111	7,202	4/28/2020
5678	PA	236	32,664	4/21/2020
5678	PA	236	12,544	4/28/2020
6789	PA	204	32,664	4/21/2020
6789	PA	204	12,544	4/28/2020

And that's good.  I'm fine with that.

However, when I add a slicer on Cases to the page and filter  Cases <=5,000 I'm getting...

ID	State	Score	Cases	Date
1234	NC	114	4,367	4/21/2020
3456	NM	145
4567	NC	111	4,367	4/21/2020

It's keeping the record from NM where Cases is missing. 

Is there a way to get 0 put in for NM Cases after joining tables A & B, so when I apply the filter I would get the following?

ID	State	Score	Cases	Date
1234	NC	114	4,367	4/21/2020
3456	NM	145	0
4567	NC	111	4,367	4/21/2020

And don't worry about insulting my intelligence.  Be as remedial as possible in your explanation as I am a Power BI rookie.

Thanks

Phil

[v-alq-msft]

Hi, @handwepg 

As is suggested by @parry2k , It is unsupported to put a measure in a slicer. I'd like to suggest you use the measure 'Sum Cases' in the table visual to display the data and use the 'Cases' column as slicers to filter the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@handwepg I would add a measure and then use that for the filter

Sum Cases = SUM ( TableB[Cases] ) + 0

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[handwepg]

I created the new measure.  And now 0's show up as desired in the table.  However, when I try to drag Sum_Cases into the Field "Add data fields here" box of the slicer, the variable doesn't stay.  In other words, a new slicer is never created.

What am I doing wrong?

[parry2k]

@handwepg you have to use it in a visual level filter, you cannot user measure in slicer.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[handwepg]

I'm sorry.  I wasn't more clear.

I want the slicer so my users can select the range of cases that are most meaningful to them.  That is why I want a slicer on the dashboard.

Any ideas?

Thanks