Slicer Filter Isn't working to filter a grid when I include a measure

[Anonymous]

My data model is setup similar to this... 

Sales Rep Table

Sales Rep ID Sales Rep Name

1	Brad
2	Lisa

Customer Table

Company ID Company Name Sales Rep ID

1	ABC Company	2
2	XYZ Company	1
3	DEF Company	2
4	GHK Company	1

Sales Table

SalesId Company ID Amount

1	4	$200
2	2	$150
3	1	$50
4	2	$175
5	1	$200
6	1	$100
7	1	$25

My relationship between Sales Rep and Company is a 1 -> Many based on Sales Rep ID and between Company and Sales is a 1 -> Many based on Company ID

On the Sales table I have two measures defined:

Total Sales Amount = SUM('Sales'[Amount])

Total Sales Count = COUNTROWS('Sales')

Now I have a grid visual where I list the Company Information and the two sales measures.. so the grid looks like this

Company ID Company Name Sales Count Sales Total

1	ABC Company	4	$375
2	XYZ Company	2	$325
3	DEF Company
4	GHK Company	1	$200

And I have a Slicer with the Sales Rep's Name so if I select a Sales Rep, it filters and just shows the Sales for the Companies that are assigned to the selected Sales Rep

In the Grid, for Company ID 3 it leaves the Sales Count and Sales Total blank... because there are no Sales... but I want it to show zeros in those columns instead of blanks... so I updated the 'Total Sales Count' and 'Total Sales Amount' measures to look like this... 

Total Sales Amount =  var amount = var amount = SUM('Sales'[Amount]) return IF(amount = BLANKS(), 0, 1)

Total Sales Count = var count = COUNTROWS('Sales') return IF(count = BLANKS(), 0, 1)

But now, when I select 'Brad' in my Sales Rep Slicer... this is what I get

Company ID Company Name Sales Count Sales Total

1	ABC Company	0	$0
2	XYZ Company	2	$325
3	DEF Company	0	$0
4	GHK Company	1	$200

And when I select 'Lisa' I get....

Company ID Company Name Sales Count Sales Total

1	ABC Company	4	$375
2	XYZ Company	0	$0
3	DEF Company	0	$0
4	GHK Company	0	$0

the grid shows All companies (it doesn't filter to show just the companies assigned to the selected Sales Rep) but what for companies not assigned to the selected Sales Rep, it shows zeros for the Sales Count and Sales Amount... it only shows the Sales Count and Sales Amounts for the Companies assigned to the selected Sales Rep...

it works exactly how I want it to when the measures return blanks when there aren't any records.. 

How can I get this to work... and have my measures return zeros instead of Blanks... I'm not understanding what is happening here.

[PaulDBrown · Accepted solution]

See if this works

The model:

The measures:

Sales + 0 =
SUMX (
    ADDCOLUMNS (
        VALUES ( 'Company Table'[Company ID] ),
        "Val", SUM ( 'Sales Table'[Amount] ) + 0
    ),
    [Val]
)

Count + 0 = IF(
    [Sales + 0] == 0, 0, COUNTROWS('Sales Table'))

I've attached the sample PBIX file

[jdbuchanan71]

@Anonymous 

Take a look at this article that discusses the topic you are asking about.

https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax/

The topic is a bit more complex than it seems at first glance.