cancel
Showing results for
Did you mean:
Frequent Visitor

## Distinct Count of Dates with Multiple Values - Part II

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.

Solution Specialist

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.

Announcements