topic Re: DAX Lookup value in the same table in DAX Commands and Tips

DAX Lookup value in the same table

AC2023 — Fri, 22 Sep 2023 23:49:45 GMT

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

Re: DAX Lookup value in the same table

123abc — Sat, 23 Sep 2023 08:17:00 GMT

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:

We use a VAR statement to define the "Key" and "LatestStatusDate" variables.
For "Key," we simply reference the "ID" column from the table.
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."
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."
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.

Re: DAX Lookup value in the same table

AC2023 — Mon, 25 Sep 2023 19:11:50 GMT

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.

Re: DAX Lookup value in the same table

Anonymous — Tue, 26 Sep 2023 07:32:59 GMT

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

Re: DAX Lookup value in the same table

AC2023 — Tue, 26 Sep 2023 15:03:00 GMT

Hi Anonymous,

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?