cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DAXHeadroom
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. 

1 REPLY 1
daxer-almighty
Solution Specialist
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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors