Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pdoucette
Frequent Visitor

Distinct Count Measure

Hi All, I have the following table:

 

ClientAdmission DateDischarge 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.  

1 ACCEPTED SOLUTION

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

 

2020-09-24 18_00_25-Window.png

 

 

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

View solution in original post

5 REPLIES 5
dedelman_clng
Community Champion
Community Champion

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|
Paul7/1/197/30/19
Jen7/1/19 
Paul8/30/19 
Steve9/3/194/1/20
Shannon9/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?

 

2020-09-24 16_30_59-Window.png

 

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

 

2020-09-24 18_00_25-Window.png

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors