Return a value from another tabel based on multiple conditions

[htsvhwave]

Hi

I have to tables, in my first tabel i want to return a value from the second tabel. I need to look up the amount of vacation left for december. In this example i have only shown one user however in my real dataset i have mulitple users. Inmy column the code need to match the following

tabel1 date =  tabel 

Tabel 1

User ID	Date	Vacation left
AKLK	31-12-2023

Tabel 2

USer ID	Date	Types of overtime	Amount
AKLK	31-12-2023	Vacation	4
AKLK	31-12-2023	Paidovertime	6
AKLK	30-11-2023	Vacation	2
AKLK	30-11-2023	Paidovertime	5

[Anonymous · Accepted solution]

Hi @htsvhwave ,

You can follow the steps below to get it:

1. Create the relationship between Table 1 and Table 2 base on the field [User ID]

2. Create a calculated column as below in Table 1 to get it

Vacation left =
CALCULATE (
    SUM ( 'Table 2'[Amount] ),
    FILTER ( 'Table 2', 'Table 2'[Date] = EARLIER ( 'Table 1'[Date] ) )
)

Best Regards

[zenisekd]

To me, the easiest way would be to merge the two tables in Power Query, based on two key columns - User ID and Date (when in merge, hold CTR and select columns in the table, which are used for merge). Power BI modeling doesn't allow relationship, based on 2 columns.

Or you could also create a new key column, which would be made of USER ID and Date combination in both tables. Then you could use this new column for relationship.