How to link data from two table with Formula requirement

[NowaBI]

Hi,

Any help will be greatly appreciated.I have tried to figure it out by myself but with no result 😞

I have two different table Travel and Expense. The only relationship is the Employee Unique ID.

Travel ID	Departure Date	Return Date	Employee ID
XXXXX1	13/04/2019	17/04/2019	E1
XXXXX2	15/05/2019	15/05/2019	E2
XXXXX3	15/05/2019	15/05/2019	E3
XXXXX4	26/06/2019	26/06/2019	E4
XXXXX5	27/06/2019	27/06/2019	E5

Employee ID	Expense Date	Nature
E1	15/04/2019	Taxi
E1	15/04/2019	Taxi
E1	15/04/2019	Restaurant
E3	15/05/2019	Taxi
E3	15/05/2019	Hotel

What I want to do is linked all expense to a Travel.

For Example, for a travel from 1st October to 10 October named "Travel1". I want to link all expanses made by the employee that has been made from 1st October to 10 October (Taxi, Hotel, Restaurant, etc...). So for that I need to compare the date of the expense to the date of the travel and write in the Expense table the Travel Name.

Is it possible ?

[sturlaws · Accepted solution]

Hi,

you can do this with both Power Query and Dax.

Power query:
Merge the Expense-table with the Travel-table on Employee ID. Create a column which is equal to 1 when Expense Date between Departure date and Return date, and filter this column on 1. Remove all columns except Employee ID, Expense Date, Nature and Travel ID

Dax:
New column the Expense-table:

TravelIdDax =
CALCULATE (
    SELECTEDVALUE ( Travel[Travel ID] );
    FILTER (
        Travel;
        Expanse[Employee ID] = Travel[Employee ID]
            && Expense[Expense Date] >= Travel[Departure Date]
            && Expense[Expense Date] <= Travel[Return Date]
    )
)

If you want to create a relationship between the tables on Travel ID, you should use power query, otherwise you will get a circular dependency error

[NowaBI]

Hi,

Thank you very much for your quick answer ! You solved my problem 🙂