Distinct Count of Dates with Multiple Values

[DAXHeadroom]

Greetings. 

Using the following table as an example, I am using two separate measures to make a Distinct Count of the Date column when the Hours column is = 0 (Measure 1) and when the Hours column > 0 (Measure 2). These two measures are subtotals and need to be displayed independently. However, I am overstating the total count by one because November 12 has values that are both = 0 and > 0. A given date should only be counted once. How can I ensure the output counts November 12 only once in these instances? 

Current output for Measure 1 = 2

Current output for Measure 2 = 3

Total = 5

Desired output for Measure 1 = 1

Desired output for Measure 2 = 3

Total = 4

ID	Date	Hours
63813-1	Monday, November 11, 2019	1.7
63813-2	Tuesday, November 12, 2019	0
63813-3	Tuesday, November 12, 2019	1.2
63813-4	Tuesday, November 12, 2019	2.1
63935-1	Sunday, November 17, 2019	1.9
63935-2	Sunday, November 17, 2019	1.8
63935-3	Sunday, November 17, 2019	1.4
63935-4	Monday, November 18, 2019	0

Many thanks for the assistance. 

[amitchandak · Accepted solution]

@DAXHeadroom , Try measures like

countx(filter(summarize(Table, Table[Date], "_1", sum(Table[Hours])),[_1]>0),[Date])

or

countx(filter(summarize(Table, Table[Date], "_1", sum(Table[Hours])),[_1]=0),[Date])

[DAXHeadroom]

Hello @amitchandak

Your solution above worked well - thank you! Now, I am having a similar problem when making additional DISTINCTCOUNTS of other columns. Specifically, I am employing the following two Measures to the below sample data table.

Flight Days Away = CALCULATE(DISTINCTCOUNT('Appended Metrics'[Dept Date Local]),'Appended Metrics'[Leg Hours]>0,'Appended Metrics'[Ver]="-V-",'Appended Metrics'[Local SAV Flights]=0)

Non-Flight Days Away = CALCULATE(COUNTAX(FILTER(SUMMARIZE('Appended Metrics', 'Appended Metrics'[Dept Date Local], "_1", SUM('Appended Metrics'[Leg Hours])),[_1]=0),[Dept Date Local]),'Appended Metrics'[Ver]="-V-", 'Appended Metrics'[Dept AP]<>"KSAV",'Appended Metrics'[Arrive AP]<>"KSAV")

 
Dept Date Local Dept AP Arrv AP Leg Hours Ver
9/21/2020 KSAV KMMU 1.6 -V-
9/21/2020 KMMU KMMU 0 -V-
9/22/2020 KMMU KMMU 0 -V-
9/22/2020 KMMU KHPN 0.3 -V-
9/23/2020 KHPN KHPN 0 -V-
9/23/2020 KHPN KSAV 1.8 -V-

My output is:

Flight Days Away = 3

Non-Flight Days Away = 2*

Since any given day should be counted once, and Flight Days take precedence,  

my desired output is:

Flight Days Away = 3

Non-Flight Days Away = 0

*I'm also confused as to why this is 2 versus 3. Seems odd to me. 

Any help woudl be very much appreciated. 

Regards,

@DAXHeadroom

[AlB]

Hi @DAXHeadroom 

Measure 1 = 
COUNTROWS( FILTER(DISTINCT(Table1[Date]), CALCULATE(SUM(Table1[Hours])) = 0))

Measure 2 = 
COUNTROWS( FILTER(DISTINCT(Table1[Date]), CALCULATE(SUM(Table1[Hours])) > 0))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers