Forum Discussion

pdoucette's avatar
pdoucette
Frequent Visitor
5 years ago
Solved

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...
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    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

dedelman_clng's avatar
dedelman_clng
Community Champion
5 years ago

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

pdoucette's avatar
pdoucette
Frequent Visitor
5 years ago

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

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    5 years ago

    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