Datetime columnns, Calculating difference between 2 time stamps etc

DAX DAX Calculations
1

In my attached file- I have 2 questions

  1. how can I get my slicer of age groups to be sorted properly
  2. I am trying to create a measure to calculate the time difference between the time when the call is received and the time when the ambulance is dispatched, but it’s not letting me select the date or time columns - have I done something wrong somewhere?

Thanks in advance
jpbix.pbix (436.1 KB)

2

Hey @jps

I went through your PBIX. Your date format (year) is wrong in almost all column except Date of call.

Also, give more context/scenario brief for " 1. how can I get my slicer of age groups to be sorted properly".

Regards,
Hemant

1 Like
3

I’m guessing for 1 you mean in numerical order, I’ve added another calculated column as so:

Age Group Sort = SWITCH(TRUE(),
Patient[Patient Age]<=5, 1,
Patient[Patient Age]<=15, 2,
Patient[Patient Age]<=30, 3,
Patient[Patient Age]<=50, 4,
Patient[Patient Age]<=75, 5,
Patient[Patient Age]<=100, 6)

It’s the same as your Age Group column, but putting numerical values instead of the bands. I then sorted your Age Group column by this new column, putting the slicer in numerical order.

There may be a more elegant solution, as now if the bands change you must remember to change it in 2 places.

jpbix - 060721.pbix (437.5 KB)

1 Like
4

@Hemantsingh ,

Thanks for pointing that out.

I have split all the date/time columns. I now have created a lookup date table and lookup time table

and they are not functioning properly.

  1. If you look, I currently have 2 slicers, “Dateofcall” - this is coming from the “calls” table, the second one is the “Date” Slicer, which is coming from the “Dates” lookup table. - This is the slicer I am looking to use.
  2. there is a measure I am trying to create “Time to Dispatch”, which I hoped would allow me to select time columns so I can calculate the difference - but unable to do that.

I am not sure if I am taking the correct approach, and I couldn’t find much on the internet with a similar case.

@jamie.bryan - Thank you - the age group slicer is now sorted. :slight_smile:

My current pbix file Attached
jpbix.pbix (489.6 KB)

5

Hey, Just coming back to update.

I just managed to figure out both the questions.

While the first was an issue of formatting happening due to some locale settings. The solution that I found for second one is to do this in power query and then use dax as required.

Thank you.

6

Hello @jps! Good to hear that you found the solution :slight_smile:
Kindly mark as solution the answer that solved your query so other users with the same problem can see the solution faster.

Thanks!