topic Re: Distinct Count Measure in DAX Commands and Tips

Distinct Count Measure

pdoucette — Thu, 24 Sep 2020 13:24:45 GMT

Hi All, I have the following table:

Client

Admission Date

Discharge Date

My goal is to get the distinct count of clients who were active during a given date range 7/1/19 - 6/30/20. I'm relatively beginger on this so hopefully I'm not way off

Definition of Active = Admission Date on or before 6/30/2020 and Discharge is blank or on or after 7/1/19.

Measure I wrote in hopes to obtain this: FY20 = CALCULATE(DISTINCTCOUNT(ActiveOpen[Client]), FILTER(ActiveOpen, ActiveOpen[Admission Date]<=DATE(2020, 6, 30)&&ISBLANK(ActiveOpen[Discharge Date])||ActiveOpen[Admission Date]<=date(2020, 6, 30)&&ActiveOpen[Discharge Date]>=DATE(2019, 7, 1)))

I was able to get this formula to work when substituting Distinct count of Client with Count of Admission date but not in the way i had hoped....Often times a client can be active more than once during a time frame if they were

Admitted--->Discharged---->Readmitted. My hope is to count distinct client names of active clients during the time frame, so a repeaat would be counted once.

Re: Distinct Count Measure

dedelman_clng — Thu, 24 Sep 2020 18:21:19 GMT

Hi pdoucette - based on the formula you put in your post, you are missing parenthesis to group the pairs of conditions.

Yours

ActiveOpen[Admission Date] <= DATE ( 2020, 6, 30 ) && ISBLANK ( ActiveOpen[Discharge Date] ) || ActiveOpen[Admission Date] <= DATE ( 2020, 6, 30 ) && ActiveOpen[Discharge Date] >= DATE ( 2019, 7, 1 )

Corrected

( ActiveOpen[Admission Date] <= DATE ( 2020, 6, 30 ) && ISBLANK ( ActiveOpen[Discharge Date] ) ) || ( ActiveOpen[Admission Date] <= DATE ( 2020, 6, 30 ) && ActiveOpen[Discharge Date] >= DATE ( 2019, 7, 1 ) )

If that is not the issue, can you give some sample data or your pbix with sensitive data removed?

Hope this helps

David

Re: Distinct Count Measure

pdoucette — Thu, 24 Sep 2020 21:21:29 GMT

Thank You David, the formula comes up with the same results when entered both ways so I don't think this is the issue.

\|Client \|	\|Admission Date\|	\|Discharge Date\|
Paul	7/1/19	7/30/19
Jen	7/1/19
Paul	8/30/19
Steve	9/3/19	4/1/20
Shannon	9/5/19

I put some sample data above....my hope would be to have the report negate that Paul is on here twice, and only count him once.....This would be 4 Distinct Individuals Served During the Time frame.....The formula I am using counts Paul Twice.

Re: Distinct Count Measure

dedelman_clng — Thu, 24 Sep 2020 21:32:03 GMT

Hi pdoucette - your formula, as written, with the proper grouping, is giving me 4 as it should. I realize that this is "sanitized" data, but perhaps you left something out of your sample data or your table structure/model?

Can you share your pbix with sensitive data removed?

David

Re: Distinct Count Measure

pdoucette — Thu, 24 Sep 2020 22:30:06 GMT

Thanks again dedelman_clng I really can't understand why it's not working as expected. Was checking up agains my dataset and for some reason it is counting clients without admission dates/discharge dates as well.....wish I could send you the PBIX, maybe i can work tomorrow to try and clean some data, there's a lot of sensitive information in there so would be tough.

I can rewrite the formula and have it count Admission dates rather than individuals and it works (but duplicates some individuals who have more than 1 admission date in the fiscal year) but can't seem to get it to count Distinct Names....Grrr...

Re: Distinct Count Measure

dedelman_clng — Thu, 24 Sep 2020 23:02:49 GMT

That is just a matter of adding another test in the FILTER statement

FY20 = CALCULATE ( DISTINCTCOUNT ( ActiveOpen[Client] ), FILTER ( ActiveOpen, ( ( ActiveOpen[Admission Date] <= DATE ( 2020, 6, 30 ) && ISBLANK ( ActiveOpen[Discharge Date] ) ) || ( ActiveOpen[Admission Date] <= DATE ( 2020, 6, 30 ) && ActiveOpen[Discharge Date] >= DATE ( 2019, 7, 1 ) ) ) && NOT(ISBLANK(ActiveOpen[Admission Date]) && ISBLANK(ActiveOpen[Discharge Date])) ) )

Make sure the NOT surrounds both ISBLANK statements in the last line.

Hope this helps

David