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

 

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 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 !!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 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
      2 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 !!