Distinct Count of Dates with Multiple Values - Part II

[DAXHeadroom]

Thank you to the community for the previous assistance with this topic. 

I have applied the suggested measures and they work well. However, I am now having a similar problem which requiers further filtering and I'm stuck.

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 only once, and Flight Days take precedence,  

my desired output is:

Flight Days Away = 3

Non-Flight Days Away = 0

How can I ensure the dates in the above sample table are only counted once?

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

Any help would be very much appreciated. 

[technolog]

The reason you're getting the wrong count for "Non-Flight Days Away" is because of the way you've structured your DAX measure. The measure is counting days where the sum of 'Leg Hours' is 0, but it's not considering days that might also have 'Leg Hours' greater than 0. This is why you're seeing an overlap between "Flight Days Away" and "Non-Flight Days Away".

To ensure that a date is counted only once and that "Flight Days" take precedence, you need to modify the "Non-Flight Days Away" measure to exclude any days that are already counted in the "Flight Days Away" measure.

Here's a way to do it:

First, let's create a measure that gives us the distinct days where there was a flight:

Flight Days =
CALCULATETABLE(
VALUES('Appended Metrics'[Dept Date Local]),
'Appended Metrics'[Leg Hours] > 0,
'Appended Metrics'[Ver] = "-V-",
'Appended Metrics'[Local SAV Flights] = 0
)
Now, let's modify the "Non-Flight Days Away" measure to exclude the days from the above measure:

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",
NOT('Appended Metrics'[Dept Date Local] IN Flight Days)
)
The key change here is the line NOT('Appended Metrics'[Dept Date Local] IN Flight Days). This ensures that any date that's already counted in "Flight Days Away" is excluded from "Non-Flight Days Away".

Regarding your confusion about the count being 2 versus 3: The measure you provided counts days where the sum of 'Leg Hours' is 0, but it doesn't consider days that might also have 'Leg Hours' greater than 0. So, for the date 9/21/2020, even though there's a flight with 1.6 hours, there's also a flight with 0 hours. Your measure counts this day as a "Non-Flight Day", which is why you're seeing the overlap.

By implementing the changes I've suggested, you should get the desired output:

Flight Days Away = 3

Non-Flight Days Away = 0

[daxer-almighty]

I'll give you a piece of good advice: Never, ever use SUMMARIZE for anything else than getting the distinct combinations from a table. There is an article on www.sqlbi.com by Marco Russo and Alberto Ferrari that discusses the dangers of violating this rule. The SUMMARIZE function does not work properly and can't be fixed due to technical reasons, therefore please refrain from calculating anything under it. Use it just for grouping, nothing else. I'm not saying that you've been bitten by this very issue but it well might be and it'll be very hard to debug if it has. Just change the code to use the combination SUMMARIZE/ADDCOLUMNS as the article @SQLBI prescribes.

I wish I could help you more but... I don't know the model and can't see any reasonable data on which you could demonstrate "in slow motion" what you want the code to do.