Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thanks for that information, I have now changed it to the Date of the Date Table. With the same result.  I appreciate the assistance you've provided thus far!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thats really odd.  Lets test if its unexpected behaviour caused by the filter function. Try this version:

Daily Count = var contextDate = MAX('DateTable'[Date])
var output = CALCULATE(
	COUNTROWS('Inmates'),
	ALL('Inmates'),	
	'Inmates'[Booking Date] <= contextDate,
	'Inmates'[Release Date] >= contextDate	
)
RETURN
output
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Seems like that fixed it?

       

      I'd also recommend making sure those different statements are on different rows in that measure (Shift + Enter).  Future you will really appreciate it being easier to read 😉

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        I'm fairly new to PowerBi, so I apologize in advance for the questions.  March 24th is still showing only 3 active inmates, when there were approximately 350.  Am I missing another step?  Yes, thank you for that bit of advice regarding the shift+enter!