Matching transactions across multiple tables

[Phil-osophy]

Hi  everyone,

ive come across an issue in my dataset that i need help solving. i have 4 different tables:

-travel extract

-intermediary file

-traveler ID number

-traveller card expenses file

travel extract is linked to intermediary file through a many-to-many relationship between traveler names

intemediary file is linked to traveler ID  number through a one-to-many relationship between the user IDs

traveller ID number is linked to traveler card expenses file through a one-to-many relationship between employee ID

what i am trying to do is verify whether the transactions that are in the travel card expenses file took place within the authorized travel dates (listed as trip date from and trip date to) in the travel extract file.

I've come up with a formula that verifies whether transactions took place between two dates however i am not sure how to link it to the specific travellers (at the moment it is not).

here is the formula i have:

transaction in status = IF('traveller card expenses file'[Transaction Date] < MAX('travel extract for PBI forum'[Trip Date From]) && 'traveller card expenses file'[Transaction Date < MAX('travel extract for PBI forum'[Trip Date To]),"In travel status", "Not in travel status").

all 4 files are accessible using the following link in addition to a screenshot of the relationships between the tables:

https://drive.google.com/drive/folders/1ar4Xp2dwNy2qrZz1EQl18-qnVgeZp2RB?usp=sharing

Any help greatly appreciated!

[Ashish_Mathur]

Hi,

Here's the result i got without building any relationships.  Download the Excel file from here.

Hope this helps.

[Phil-osophy]

Hi @Ashish_Mathur,

i dont believe that is the correct excel file link as i dont see any formula included. it seems to be the same excel files i shared.

[Ashish_Mathur]

Hi,

Go to PowerPivot > Manage.

[Phil-osophy]

thank you!

do you know if there is any way to output something similar to what i had in my formula (if the expense took place during travel dates, the output is '' travel status'', otherwise if it took place outside of travel dates the output is ''not in travel status'')?

[Ashish_Mathur · Accepted solution]

Hi,

Enter this formula there

=if(ISNUMBER(CALCULATE(COUNTROWS(travel),FILTER(travel,travel[trip date from]<=EARLIER(card_expenses[Transaction Date])&&travel[trip date to]>=EARLIER(card_expenses[Transaction Date])&&travel[Traveller ID]=EARLIER(card_expenses[Employee id])))),"Travel status","Not in travel status")

[Phil-osophy]

Thanks @Ashish_Mathur!

i'm just curious as to how the last part of the if statement works:

travel[Traveller ID]=EARLIER(card_expenses[Employee id])

what does ''EARLIER'' do in the context of the traveller ID? i understand how it would apply to the trip date from and trip date to but unsure in this context.

[Ashish_Mathur]

Hi,

In simple Engligh that statement says that the ID's in that column should be equal to the ID of the current row.

[Phil-osophy]

Thank you @Ashish_Mathur !

Would you know how to create a formula that would work with the relationships seeing as that is what i have in my actual power BI file because my dataset is significantly larger than what i shared?

thanks!

[Ashish_Mathur]

Hi,

My formula should work even if you build relationships between the tables.