Forum Discussion
Distinct Count Measure
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.
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
5 Replies
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
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.
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
