How to calculate daily population in DAX AND be able to slice it by columns

Question

Updated Problem: I'm trying to calculate the daily population of patients at our center for any given date using the patient's booking date (start date) and their release date -- using DAX and Power Pivot. I'm planning on using it in PowerBI.

I'm using two tables: 1) a main table with the patient data containing columns for booking date, release date, gender, provider, and race and 2) a date table (calendar) and have established a relationship between the two tables. The date table is date dimension.

Based on recommendations for my initial question, I created a calculated column in the calendar table using formula below.

PatientCount :=
SUMX (
    Patients,
    IF (
        Patients[Booking Date] <= 'Calendar'[Date]
            && Patients[Release Date] >= 'Calendar'[Date],
        1,
        0
    )
)

I then created an aggregate measure to compute the average daily population. This works; however, using this method I cannot figure out how to slice it by the patient table columns (e.g. gender, provider, race) since the calculated column is in the calendar table.

You should use those slicers using filter visual or use it to filter in particular visual filter. By selecting Gender in filter will filter out rows to that Gender only. — Mboolean
I have the filters/slicers and they work for all of my other measures and visualizations, except for this one on 'average daily population'. I think it's because it's on the date table?? — Eric

OscarLar OscarLar · Accepted Answer · 2019-05-04T08:23:18

Based on this test data:

And a Calendar table with a 1:* relationship to the 'Patients'[Adm], you can write the following measure:

Active Patients = 
var currentDate = MAX('Calendar'[Date])
return 
MAXX(
    DISTINCT('Calendar');
    CALCULATE(
        COUNTROWS('Patients');
        ALLEXCEPT(Patients; Patients[Gen]); //Add all columns you will slice on
        Patients[Adm] <= currentDate;
        Patients[Rel] >= currentDate
    )+0
)

You can produce this table:

Which also can be sliced by Gender:

How to calculate daily population in DAX AND be able to slice it by columns

1 Answers