Measure to return 0(zero) if distinct count is blank

[Anonymous]

Need some help from the community. 

I am trying to get the count of programs for each month (date as lastdayofthemonth) with target as above. However if for any month, there are no program with target as 'Above' then the measure should return 0. 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Program]),'Table'[Target (Below/Above)]="Above")

In the "Month wise Above" visual want see "Friday January 31" as 0,  

Sample data

MonthYear	Program	Location	Project	Target (Below/Above)	LastdayofMonth
1/1/2020	ABC	Hyd	p1	below	1/31/2020
1/1/2020	XYZ	Hyd	p2	below	1/31/2020
1/1/2020	TEST1	Hyd	p1	below	1/31/2020
1/1/2020	ABC	Hyd	p2	below	1/31/2020
1/1/2020	XYZ	Hyd	p2	below	1/31/2020
1/1/2020	cb1	blr	p2	within	1/31/2020
1/1/2020	dum	blr	p2	within	1/31/2020
1/1/2020	vix	blr	p2	within	1/31/2020
1/1/2020	bull	blr	p2	within	1/31/2020
12/1/2019	ABC	Hyd	p1	below	12/31/2019
12/1/2019	XYZ	Hyd	p2	below	12/31/2019
12/1/2019	TEST1	Hyd	p1	above	12/31/2019
12/1/2019	ABC	Hyd	p2	below	12/31/2019
12/1/2019	XYZ	Hyd	p2	below	12/31/2019
12/1/2019	cb1	blr	p2	within	12/31/2019
12/1/2019	dum	blr	p2	below	12/31/2019
12/1/2019	vix	blr	p2	within	12/31/2019
12/1/2019	bull	blr	p2	within	12/31/2019
11/1/2019	ABC	Hyd	p1	below	11/30/2019
11/1/2019	XYZ	Hyd	p2	below	11/30/2019
11/1/2019	TEST1	Hyd	p1	within	11/30/2019
11/1/2019	ABC	Hyd	p2	above	11/30/2019
11/1/2019	XYZ	Hyd	p2	below	11/30/2019
11/1/2019	cb1	blr	p2	within	11/30/2019
11/1/2019	dum	blr	p2	below	11/30/2019
11/1/2019	vix	blr	p2	within	11/30/2019
11/1/2019	bull	blr	p2	within	11/30/2019

[Anonymous]

@amitchandak why you have used +0 at the end of Distinctcount?

[Anonymous]

Thank You @edhans for quick solution. 

However the measure works unitl the Target is not added to the table visual.

Below in the highlighted Visual count for "Above" is missng for January. 

Is there a way to fix this.

Thank Again. 

[edhans · Accepted solution]

Your source data doesn't have an "Above" record for Jan 31, 2020.  The measure will return "0" with no issue, but your data table doesn't have a row with "Above" on it to provide any filter context for the measure to work.

You may want to create a Dimension table with Above, Below, and Within, and use that table to populate your visuals, creating a One-To-Many to your Fact table, then everything will have an Above/Below/Within, and the measure would then work.

[Anonymous]

Thank You @edhans it works..

[edhans]

Great @Anonymous. Glad it works for you.

[amitchandak]

try

Measure = CALCULATE(DISTINCTCOUNT('Table'[Program]),'Table'[Target (Below/Above)]="Above")+0

[MarkTR]

What does the + zero do at the end?

[edhans]

It converts a blank to a zero. See Handling BLANK in DAX - SQLBI - the example there is BLANK()+4 = 4, but BLANK()+0 equals 0.
Another way to do it is to use the COALESCE() function. 

COALESCE([Some Measure or Expression], 0) would return the first non-blank value. If the measure is blank, it returns zero.

[edhans]

If your measure is correctly returning what you want, and the issue is if it returns blank you want it to return 0 instead, use this:

Measure =
VAR ProgramCount =
    CALCULATE(
        DISTINCTCOUNT( 'Table'[Program] ),
        'Table'[Target (Below/Above)] = "Above"
    )
RETURN
    IF(
        ProgramCount = BLANK(),
        0,
        ProgramCount
    )

It simply says if ProgramCount (which is your measure in the VAR statement) is blank, return 0, otherwise the value of ProgramCount.