Forum Discussion
Multiple Tables to lookup dates
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.
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)!