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

New Member

## DAX Lookup value in the same table

Hi, I tried lookupvalue and calculate but can't get the result I need.  Any help is appreciated.  Thanks!

Assigned = CALCULATE(FIRSTNONBLANK(Table[Assignee],1),FILTER(ALL(Table[Assignee]),Table[EndDate]=Table[AssigneeEndDate]))

Assigned =
VAR Key =
MIN(Table[ID])
VAR LatestStatusDate =
CALCULATE(
MAX(Table[EndDate]),
Table[ID] = Key
)
VAR LatestAssignee =
LOOKUPVALUE(
Table[Assignee],
Table[Key], Key,
Table[AssigneeEndDate], LatestStatusDate
)
RETURN
LatestAssignee

Table

 ID StartDate EndDate Status AssigneeEndDate Assignee 1 9/1/2023 9/3/2023 Start null null 1 9/4/2023 9/5/2023 In Progress null null 1 9/6/2023 null Complete null null 1 null null null 9/1/2023 null 1 null null null 9/3/2023 A 1 null null null 9/5/2023 B 1 null null null null C

Result

 ID StartDate EndDate Status Assignee 1 9/1/2023 9/3/2023 Start A 1 9/4/2023 9/5/2023 In Progress B 1 9/6/2023 null Complete C
4 REPLIES 4
Community Support

Hi  @AC2023 ,

Here are the steps you can follow：

1. Create measure.

``````Measure =
MAXX(
FILTER(ALL('Table'),
'Table'[EndDate] in SELECTCOLUMNS('Table',"1",[AssigneeEndDate])),[StartDate])``````
``````Measure 2 =
MAXX(
FILTER(ALL('Table'),
[Measure]=MAX('Table'[StartDate])),[Assignee])``````

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

New Member

Thank you!  This works great with the sample data I provided, and it gives the correct desired result.  However, when I applied this to my actual table which is huge, I got the error message "Resources Exceeded.  This visual has exceeded the available resources.  Try filtering to decrease the amount of data displayed."  Any ideas how to fix this error?

Community Champion

It looks like you want to assign the "Assignee" value to each row in your table based on certain conditions. To achieve this, you can use DAX measures and a combination of functions to perform the lookup and assignment. Here's a modified version of your DAX code to achieve the desired result:

Assigned =
VAR Key =
Table[ID]
VAR LatestStatusDate =
CALCULATE(
MAX(Table[EndDate]),
FILTER(Table, Table[ID] = Key)
)
VAR LatestAssignee =
CALCULATE(
MAX(Table[Assignee]),
FILTER(
Table,
Table[ID] = Key &&
Table[AssigneeEndDate] <= LatestStatusDate
)
)
RETURN
LatestAssignee

This code should assign the appropriate "Assignee" value to each row based on the conditions you specified. The key points in this code are:

1. We use a VAR statement to define the "Key" and "LatestStatusDate" variables.
2. For "Key," we simply reference the "ID" column from the table.
3. For "LatestStatusDate," we use the CALCULATE function with a FILTER condition to find the maximum "EndDate" value for rows where the "ID" matches the current row's "ID."
4. In the "LatestAssignee" variable, we use CALCULATE with another FILTER condition to find the maximum "Assignee" value for rows where the "ID" matches the current row's "ID" and the "AssigneeEndDate" is less than or equal to the "LatestStatusDate."
5. Finally, we return the "LatestAssignee" value, which will be assigned to each row in your table.

When you use this measure in your table, it should give you the desired result, as shown in your "Result" section.

New Member

Thank you for the DAX code, but it's not returing the desired result.

This problem can  easily done in SQL query using self left join.

Here's SQL query, the 1st select is the data from the table, the 2nd select is the correct desired result:

Note that in this sample data,  I added the Type field to make it easy to get the correct result.

This is the result in Power BI:

DAX for Assigned:

I get the same result whether I've the extra filter on TempTable[Type] or not.

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.