Forum Discussion

DPCCGF's avatar
DPCCGF
Icon for Helper IV rankHelper IV
2 years ago

DAX If Statement with Unrelated Table

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 Department...
Need Help
Show and Tell
DPCCGF's avatar
DPCCGF
Icon for Helper IV rankHelper IV
2 years ago
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's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    DPCCGF
    Icon for Helper IV rankHelper IV
    2 years ago

    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's avatar
      vanessafvg
      Icon for Community Champion rankCommunity Champion
      2 years ago

      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's avatar
        DPCCGF
        Icon for Helper IV rankHelper IV
        2 years ago

        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.