Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
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...
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |