Forum Discussion
Dax Measurement Assistance
I am in need of assistance. What is the proper dax measurement to obtain the total daily inmate count (number of inmates currently incarcerated) from current date and 5yrs. back, per day. For example...
RossEdwards,
I ended up creating the following measurement on my data table:
ActiveInmatesCount =VAR CurrentDate = MAX('DimDate'[Date])RETURNCALCULATE(DISTINCTCOUNT('PrevFiveYrsJailing'[Booking#]),FILTER('PrevFiveYrsJailing','PrevFiveYrsJailing'[BookingDate] <= CurrentDate &&(ISBLANK('PrevFiveYrsJailing'[ReleaseDate]) ||'PrevFiveYrsJailing'[ReleaseDate] > CurrentDate)))I was getting the same result where the amounts were not as close to the actual numbers I have. I ended up linking this data table to a date table and making that relationship inactive. Once I made the relationship inactive between the two tables, the data appears to more accurate. I'm not sure why the inactive relationship worked, but it did. I wanted to thank you for your time and assistance, much appreciated!Alvina
RossEdwards,
I ended up creating the following measurement on my data table:
ActiveInmatesCount =
VAR CurrentDate = MAX('DimDate'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('PrevFiveYrsJailing'[Booking#]),
FILTER(
'PrevFiveYrsJailing',
'PrevFiveYrsJailing'[BookingDate] <= CurrentDate &&
(
ISBLANK('PrevFiveYrsJailing'[ReleaseDate]) ||
'PrevFiveYrsJailing'[ReleaseDate] > CurrentDate
)
)
)
I was getting the same result where the amounts were not as close to the actual numbers I have. I ended up linking this data table to a date table and making that relationship inactive. Once I made the relationship inactive between the two tables, the data appears to more accurate. I'm not sure why the inactive relationship worked, but it did. I wanted to thank you for your time and assistance, much appreciated!
Alvina