Look up multiple values in powerBI DAX, Need Help

[Anonymous]

I am having two tables 

Table 1

Table 2 

Note : Tier of a employee gets change every 6th days interval , refer to Table 1

And Table 1 holds all the logs of tier change of employee

We have to write a dax to look up of employee ID and the date given in Table 2, 

And need to create 3rd table which includes all column of table 2 and following a below match values as addtional columns.

1. Look up of emp ID and respective given date from table 2 to table 1 , to find out ...on the given date in table 1 whats was the tier in table 1, in case the TIER change did not take place on the given date in table 2 , very earlier date has to be picked along with assigned tier in second addional column in table 2

[v-cgao-msft]

Hi @Anonymous ,

Please try:

Table3 = 
ADDCOLUMNS(
    Table2,
    "Tier on DOA", 
    VAR EmpId = [Employee/Member Id]
    VAR DOA = [DOA]
    VAR EarlierDate = 
        CALCULATE(
            MAX(Table1[ATTENDANCE_DATE]),
            Table1[Emp_Id] = EmpId,
            Table1[ATTENDANCE_DATE] <= DOA
        )
    RETURN
        CALCULATE(
            SELECTEDVALUE(Table1[Tier]),
            Table1[Emp_Id] = EmpId,
            Table1[ATTENDANCE_DATE] = EarlierDate
        )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous · Accepted solution]

Hi ,

Thank you, But however with a self brainstrom I could get the solution ..

I have created 3rd table with the complete Table2 columns and added up another two calculated columns under following parameter ..

No.1 for Attendance Match

ATTENDANCE_DATE_MATCH =

VAR CurrentEmpID = Table3[Emp_ID]

VAR CurrentDOA = Table3[DOA]

VAR MatchingRow =

    FILTER(

        Table1,

        Table1[Emp_Id] = CurrentEmpID &&

        Table1[ATTENDANCE_DATE] = CurrentDOA

    )

RETURN

    IF(

        COUNTROWS(MatchingRow) > 0,

        CurrentDOA,

        CALCULATE(

            MAX(Table1[ATTENDANCE_DATE]),

            FILTER(

                Table1,

                Table1[Emp_Id] = CurrentEmpID &&

                Table1[ATTENDANCE_DATE] <= CurrentDOA

            )

        )

    )

No.2 Tier_Match.

TIER_MATCH = 

VAR CurrentEmpID = Table3[Emp_ID]

VAR CurrentDOA = Table3[DOA]

VAR MatchingRow =

    FILTER(

        Table1,

        Table1[Emp_Id] = CurrentEmpID &&

        Table1[ATTENDANCE_DATE] = CurrentDOA

    )

RETURN

    IF(

        COUNTROWS(MatchingRow) > 0,

        MAX(Table1[Tier]),

        CALCULATE(

            MAX(Table1[Tier]),

            FILTER(

                Table1,

                Table1[Emp_Id] = CurrentEmpID &&

                Table1[ATTENDANCE_DATE] <= CurrentDOA

            ),

            REMOVEFILTERS(Table1)  // This line is essential

        )

    )

Thank you for the help, due to the urgent need of business , I had to deploy a solution.

Thank you again for the effort !!