Forum Discussion
Return a value from another tabel based on multiple conditions
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 |
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
2 Replies
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
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.
