cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

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

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 @Prakash_Relianc ,

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

Frequent Visitor

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors