Forum Discussion

pui's avatar
pui
Frequent Visitor
3 years ago
Solved

Array formula in DAX

I have some patients data in a hospital with admission date and discharge date.  I need to calculate how many patients are in the hospital on each day.  Sample data and expected output as below, I pr...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    No of patients measure: =
COUNTROWS (
    FILTER (
        Data,
        Data[Admission date (dd/mm/yyyy)] <= MAX ( 'Calendar'[Date] )
            && OR (
                Data[Discharged date (dd/mm/yyyy)] > MIN ( 'Calendar'[Date] ),
                Data[Discharged date (dd/mm/yyyy)] = BLANK ()
            )
    )
)

     

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

 

 

No of patients measure: =
COUNTROWS (
    FILTER (
        Data,
        Data[Admission date (dd/mm/yyyy)] <= MAX ( 'Calendar'[Date] )
            && OR (
                Data[Discharged date (dd/mm/yyyy)] > MIN ( 'Calendar'[Date] ),
                Data[Discharged date (dd/mm/yyyy)] = BLANK ()
            )
    )
)