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

 

pdoucette's avatar
pdoucette
Frequent Visitor
5 years ago

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.  

  • 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