DAX If Statement with Unrelated Table

[DPCCGF]

Hi all, I have two tables, one called Call for Services and one called Shift.  The Call for Services, each record, has a Department (there are 26 departments).  The Shift table has all the Departments and their shift times (sometimes up to 6 shifts in a department).   What I need to do is bring the "Shift" into the Call for Services table based on the call time.  Something like:  If 'Calls for Services' [Department] = 'Shift' [Department && 'Call for Services' [Call Time] between 'Shift'[Start Time] and 'Shift'[End Time] then 'Shift'[Shift].  I cannot join these (unless there is someway that I do not know about).  I have reviewed how to bring in data from an UNRELATED table but am having problems.  Can someone help?  The time fields are Date/Time fields.

[DPCCGF]

Hi Vanessafvg, were you able to look at my last post and offer any suggestions?  From what I can tell it should be working!

[DPCCGF]

Hi Vanessa..........so I think I got the below working except for one item.  If the Dispatch time is AFTER 10PM and before 6:30AM nothing is returened.  The other times work as they fall within the item in RED below.  Recommended change?

Agency Shift Test =

var agency = SELECTEDVALUE('Call For Services'[Agency])

var dispatchtime = max('Call For Services'[Dispatched Time])

var shift = CALCULATE(max(Shifts[ShifitName]), (Shifts[Agency] = agency)  && (dispatchtime >= Shifts[HourStart] && dispatchtime <= Shifts[HourEnd]))

return shift

[vanessafvg]

I would never be able to tell you without seeing the data please provide the data for those records and the actual shift data that relates to it.    Also what data types you are now using.

[DPCCGF]

I have a PBIX, how can I get it to you?  Do not know how to attach a file here.

[vanessafvg]

you can either upload it to a cloud source and then share that link or you can send it to me in a private message

[vanessafvg]

its always best to provide sample data if you want your questioned answer as this makes it easier to provide a solution.

see pbix attatched if my assumptions are correct

shift =
VAR dept =
SELECTEDVALUE ( calls[dept] )
VAR calltime =
MAX ( calls[call time] )
VAR result =
CALCULATE (
MAX ( shifts[shift] ),
shifts[department] = dept
&& calltime >= shifts[shift start time]
&& calltime <= shifts[shift end time]
)
RETURN
result

[DPCCGF]

Hi Vanessa, is there a way for me to provide a sample to you?  I have a PBIX file to send you.  Maybe a private message?

[DPCCGF]

Vanessa, were you able to see my last post about formatting of fields to all TIME, both from your example and mine?  And yours is working and mine is not.  Could it be that I am doing a Direct Query model?

[DPCCGF]

Hi Vanessa, I think this is the issue.  So when I checked on what is being returned as the dispatched time I am getting the below with 12/30/1899.  You will see the real dispatch time and then the once calculated from the VAR in this formula.

Agency Shift Test 2 =

var agency = SELECTEDVALUE('Call For Services'[Agency])

var dispatchtime = max('Call For Services'[Dispatched Time])

var shift = dispatchtime

return shift

[vanessafvg]

it looks like a date type issue, change it to a time only field

[DPCCGF]

The error, if I return Agency it works.  If I return Dispatch Time it works, if I return Shift I get the error which is shown below.  I really appreciate your help.

[DPCCGF]

So I did the below and am getting this error.  The HourStart and HourEnd are formatted as a Long Time in the data model.

Agency Shift Test =

var agency = SELECTEDVALUE('Call For Services'[Agency])

var dispatchtime = FORMAT(max('Call For Services'[Dispatched Time]), "Long Time")

var shift = CALCULATE(max(Shifts[ShifitName]), (Shifts[Agency] = agency)  && (dispatchtime >= Shifts[HourStart] && dispatchtime <= Shifts[HourEnd]))

return shift

[vanessafvg]

all 3 of those fields need to be formatted the same way in order to use them together, if the datatype is not the same it wont work

in powerquery change the field types to time, it will show as decimal in the front end.

you cannot use a text data type to do the operation you are doing

please compare your data types to the file i gave you.    in the front end they are all decimals.    use the file i gave you to do the same preparation of the data.

[DPCCGF]

So they are all Time.  I could not convert my fields to Duration like you had.  Dispatch Time, HourStart and HourEnd.  So I changed all your fields to Time.  And it worked.  So your shifts.pbix file looks just like mine.  But I get BLANKS.

[vanessafvg]

like i said if one has a date in it, it wont be a match, you have to convert all to time, otherwise it will not find a match for the shift.  because the date+ time does not equal time,. You no longer getting the data type error, but that wont solve the issue.

what was the problem with converting all to duration, you need to fix any data type conversion issues.

However if i misunderstand what you are telling me  can you share your file, as its hard to know whats going wrong without being able to see all the information?

[DPCCGF]

Everything is converted to time.  Both in your file and my file.  Both files look EXACTLY the same.  Except for field names.  I got an error when converting to Duration.  So I just took yours, converted all fields to Time and changed a bit of data to see different shifts and it all worked fine.  Then I went to my model and did the same thing.  Nothing.  Formulas the same.  And when I show yours with RESULT = DISPATCHTIME I get the same as mine.  Which is that 1899 date.  Thought that might be a problem but since yours works no idea.  I used your advice and changed the result for Department, Dispatch Time, etc. and get the same results.  The only difference between your model and mine is that mine is Direct Query.

[vanessafvg]

is it possible to do a screenshot of all your 3 fields?

please put place them in a matrix?  so i can see what all 3 look like?

its really hard to see this issue without the file itself so there must be some other nuance that i am not understanding from the information you are providing.

 Can you try the following?

Make a copy of your file, so keeping your original in direct query, and convert the copy to imported mode, and then see if you get the same error?

what is your back end system that its doing direct query against?   If it's not finding a match because something isn't lining up.    That is either usually down to data type or the values are different.   

[DPCCGF]

Hi Vanessa, how do I attach my PBIX to this thread?

[DPCCGF]

Yes, I can do that.  I will get this all tmorrow morning!  Thanks so much Vanessa.  I will also import a sample of records like you said and go from there.

[DPCCGF]

Getting closer, at least no errors.  But no results.  Maybe I am missing something?  See below, it has a sample of the data fields.  The table on the left is Calls for Service, the table on the right is Shifts.