Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Look up multiple values in powerBI DAX, Need Help

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 employ...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 !!
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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 !!