Comparing a field from one table with a field from another table. ie IF(X=Y,"true","false")

[MarcUrdang]

Please can you advise how to compare a filed in one table to a field in another table. ie If(x=y,"true","false"

[kentyler]

The simplest way is to create a realtionship between the tables. Have you tried that ? Then you can write your forumla in a calculated column.

[MarcUrdang]

Hi .. thank you for your response.... sure I have .. I use fieds from there in a number of calcs. 

I could attach the workbook for you to see? 

thanks

Marc

[MarcUrdang]

actually not in calcs .. just in the views

[MarcUrdang]

I'm just starting to build so now much to see .. basically there is a field called Names Saiing Day in the Carrier Info table and a calculated field called 'Weekly FLL ETD ATD' in the Consol Inbound Tabl ...  which I need to compare with the Names Sailing Day from the Carrier Ino Table

https://drive.google.com/open?id=1XBPsyU1Vt3bZLTALw-1_3jtqLFTjTv1U

[MarcUrdang]

Sorry please use this link:

https://drive.google.com/open?id=1RIXxBw5SVvwbMGa4x5GQpftFLJGNpvPS

[kentyler]

I found 2 things.

One is you have a many to many relationship between the tables. That might be confusing things. I deleted the existing relationship and was able to recreate it as one to many

The other thing I notice is that when i try to include the names sailing day field in a measure it was not available in the intelisense list

so it looks like there is something funny with that column, since it should be available

because i don't have the underlying data i can't see the data type in the query editor but look at this posting which suggests that an inappropriate data type can block the column from being used. https://community.powerbi.com/t5/Desktop/Relationship-with-date-table-not-working/td-p/149052

[MarcUrdang]

Hi .. I have attached the underlying data .. and will look at the data type as you suggested. Does the underlying data highlight anything?

https://drive.google.com/open?id=1THu5ngP3bWG3tYztjC8QnWNnA1HUOaWR

tx

Marc

[kentyler]

I think I've gone as far as I can. The issue on the interface level is that the calculated column does not show up as available for either other calculated columns or measures. I tried calculating a numeric day of the week instead of a string and that didn't help. I don't think I can go farther over email. If you want to invest the time to do a screen share we could have a conversation, but I can't promise any results. At this point I would fall back and try loading a simpler table that had only some identifying fields and the day of the week. Create a relationship to it and see if the day of the week field shows up in the intellisense. If it does, then gradually expand the "simple" version out to be the full table you want to load, checking as you go to see where it "dissappears". Thanks for the responses, it's been interesting.

[MarcUrdang]

Hi Ken

thanks so much for your help thus far. I'm new to Power BI so I'm not really sure how all this works .. ie you getting remunerated for your time. However I am quite desparate and would appreciate a Skype call or the like that we can. 

thanks so much

Marc

[kentyler]

OK, how about 1:00 Pacific Standard Time

We can try skype. on skype I'm ken@8thfold.com.

To add you to my contacts I'll need your email address.

[MarcUrdang]

I'm unfortunately way asleep at that time 🙂 .. that is 10pm my time ... I get up at 4am each morning for crossfit training so already asleep by then.

I don't want to inconvenience .. I will keep at it from my side. Such a pity not as easy as Tableau but still have to get this right.

thanks for your help thus far.

[kentyler]

OK. I got the file.

So. First question. It looks like the CONSOL Inbound describes one or more events happening for a cargo of some kind that is shipped on multiple carriers. What kind of report are you aiming at ? Is there more than one event per row in the CONSOL Inbound table. If so, which fields belong to which events. Or do you consider the whole shipping process to be just one event.

Another way to approach this would be to make a mock up of the report you want to end up with in excel, and send me that file.

[MarcUrdang]

Hi ... yes agreed. I am a seasoned Tableau user. I understand well the points you are making. I don't mean to sound arrogant 🙂 but all I am trying to do is see if the DAY of the Named Sailing Day and the DAY of the Weekday calc is the SAME or DIFFERENT. But can't seem to get this to work. I haven't started to build too much as this premise is key to a lot of my builds.

tx

Marc

[MarcUrdang]

I guess all I'm trying to do is compare if the WEEKDAY of the one is the same as the WEEKDAY of the other

=IF(WEEKDAY('Carrier Info'[Named Sailing Day] = WEEKDAY('Consol Inbound'[Weekday LLD ETD ATD],"same","different")

[kentyler]

don't understand that...

I'm going out for coffee. If you could share the file on one drive or google drive I'll take a look when I get back.

[kentyler]

I'd be glad to look at it.

[MarcUrdang]

wow .. thanks .. I'm an old Tableau user converting to PBI so learning the ropes again.

Just wondering where to attach the .pbix

[MarcUrdang]

We are blocked by our work to add outsiders to One Drive .. is there any other way I can send to you?