Forum Discussion

SN1900's avatar
SN1900
Frequent Visitor
7 years ago

IF Formular ( 2 Tables)

Hi all, 

 

Actually I am starting to create my first Power BI Reports and therefore I need an if formular. 

Unfortunately it is not working as I can only select the data from one of the two tables.

 

Let me shortly explain what I want to do. 

 

I have two tables. 

In both tables is one column with a time which I need for my formular as I want to compare both times.

 

I created the formular already in excel and there it is working. 

 

If(Table1[Time1]-Table2[Time2]>=0;Text(Time1[Time1]-Table2[Time2];"[hh]:mm");TEXT(-(Table1[Time1]-Table2[Time2]);"-[hh]:mm"))

 

The formular should compare the both times and show me the difference. 

I used the formular text because otherwise Excel can not create "negative time"

 

The formular is working in Power BI as well but only if the two times are in the same table. 

 

My only question is how I can create this formular in PowerBI  by using different tables.

 

Thanks in advance and best regards 

 

Niclas 

 

 

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    With the info you've given it's not possible to tell you what to do. We have to know the structure of your model first of all...

    Best
    D.
    • SN1900's avatar
      SN1900
      Frequent Visitor
      7 years ago

      Hi darlove, 

       

      please find attached some screenshoots

       

      So what I wanted to do is to compare the event date with the delivery appointment time. 

      The relationship for both tables is the Shipment ID. 

       

      The Shipment ID and the Delivery Appointment Time is coming from the table OTM Shipment analysis. 

      The Event time is coming from the OTM Event analysis.

       

      I tried the formular with time values from one table and then it is working.

      (IF(OTM_EventStatus_Analysis[EVENT DATE TIME UTC]-OTM_EventStatus_Analysis[EVENT DATE TIME]>=0;FORMAT(OTM_EventStatus_Analysis[EVENT DATE TIME UTC]-OTM_EventStatus_Analysis[EVENT DATE TIME];"hh:mm:ss");FORMAT(-(OTM_EventStatus_Analysis[EVENT DATE TIME UTC]-OTM_EventStatus_Analysis[EVENT DATE TIME]);"-hh:mm:ss")))
       
      But now I want to replace the "Event date time utc "with the "delivery Appointment time" but this is not working as the Delivery Appointment time is in another table.