Forum Discussion

htsvhwave's avatar
htsvhwave
Icon for Helper II rankHelper II
2 years ago
Solved

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 howev...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
zenisekd
Icon for Super User rankSuper User
2 years ago

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.