Forum Discussion
How to link data from two table with Formula requirement
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 ?
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
2 Replies
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 errorHi,
Thank you very much for your quick answer ! You solved my problem :)
