Forum Discussion
Multiple Tables to lookup dates
Been trying to learn DAX / M by creating my own problems (no pun intended) and trying to solve... no success here for the last few days with this problem.
Trying to find the difference between first appointment date and when patient was created, excluding weekends.
I have to find the min appointment date for each chart number. Then lookup the date in the date dimension and subtract the date key (?) for whatever date the patient was created, but then subtract any weekends.
Make sense? Best with DAX or M?
Layout:
Date Dimension Table:
Patient Table:
Appointment Table:
Do it in DAX. It is easy if you first create a “weekday” column. Write a calc column in your calendar table that returns 1 for weekday and 0 for weekend. You can then simply add this column after the filter is applied.
I would have thought your patient table should be a lookup table of your appointment table (and also calendar is a lookup table of appointment). No relationship between patient table and calendar table.
With the above structure, you could be able to put paitent[id] (and name) onto a matrix, and write a measure something like this
= VAR createDate = selectedvalue(paitent[create date])
VAR firstAppt = min(appts[date])
RETURN CALCULATE(SUM(calendar[weekday]),FILTER(Calendar,Calendar[date]>=createDate && Calendar[Date]<=firstAppt))
it May need some tweaking as I haven’t tested it, but that is how I would approach it.
2 Replies
Do it in DAX. It is easy if you first create a “weekday” column. Write a calc column in your calendar table that returns 1 for weekday and 0 for weekend. You can then simply add this column after the filter is applied.
I would have thought your patient table should be a lookup table of your appointment table (and also calendar is a lookup table of appointment). No relationship between patient table and calendar table.
With the above structure, you could be able to put paitent[id] (and name) onto a matrix, and write a measure something like this
= VAR createDate = selectedvalue(paitent[create date])
VAR firstAppt = min(appts[date])
RETURN CALCULATE(SUM(calendar[weekday]),FILTER(Calendar,Calendar[date]>=createDate && Calendar[Date]<=firstAppt))
it May need some tweaking as I haven’t tested it, but that is how I would approach it.
Thanks MattAllington ! When I saw your post I almost couldn't believe it. I have two of your books I bought last week, and I'm trying to absorb everything at once which is difficult. Thanks for the help (and books)!