Forum Discussion

msingh2019's avatar
msingh2019
Helper II
7 years ago
Solved

Need help with conditional data aggregation

My source data format (after unpivoting) looks like this -  PatientID | Status | Date p234 | Active | 01-Oct-2018 p234 | Discharged | 12-Dec-2018  p234 | Dropped | 15-Feb-2019 p235 | Active | 11...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    This is the result i got.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

This is the result i get.  I do not quite agree with yoru results in Feb 2019.  I think for Feb 2019, discharged should be 0 and dropped should be 1.  I have shown patient ID's there to prove my point.  Are you OK with this result?

  • msingh2019's avatar
    msingh2019
    Helper II
    7 years ago

    Hi Ashish_Mathur,

    I agree with your result till Jan-2019 (there were some mistakes in my previous Desired Output table). So, for Jan-2019 - Active = 0, Discharged = 2 (p234 & p235), Dropped = 1 (p236) and this is correct. But in Feb-2019, p234 gets dropped so will get added to Dropped count for Feb-2019. And therefore for Feb-2019 - Active = 0, Discharged = 1 (p235 is carried forward from Jan-2019) and Dropped = 2 (p236 & p234). I hope this makes sense.

     

    I have corrected the PBIX file, link is here.

     

    Thanks

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      This is the result i got.

      • msingh2019's avatar
        msingh2019
        Helper II
        7 years ago

        Thanks Ashish_Mathur! This is the correct result. Please do share the PBIX file.