topic Re: IF Formular ( 2 Tables) in DAX Commands and Tips

IF Formular ( 2 Tables)

SN1900 — Fri, 23 Aug 2019 12:09:16 GMT

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

Re: IF Formular ( 2 Tables)

Anonymous — Fri, 23 Aug 2019 12:13:41 GMT

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.

Re: IF Formular ( 2 Tables)

SN1900 — Fri, 23 Aug 2019 12:47:33 GMT

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.