cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 2
Community Support

Hi @Anonymous ,

``````Table3 =
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
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors