Skip to main content
cancel
Showing results for 
Search instead 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

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

IDStartDateEndDateStatusAssigneeEndDateAssignee
19/1/20239/3/2023 Startnullnull
19/4/20239/5/2023In Progressnullnull
19/6/2023nullCompletenullnull
1nullnullnull9/1/2023null
1nullnullnull9/3/2023A
1nullnullnull9/5/2023B
1nullnullnullnullC

 

Result

IDStartDateEndDateStatusAssignee
19/1/20239/3/2023 StartA
19/4/20239/5/2023In ProgressB
19/6/2023nullCompleteC
4 REPLIES 4
v-yangliu-msft
Community Support
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:

vyangliumsft_0-1695713526181.png

 

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

Hi @v-yangliu-msft,

 

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?

123abc
Community Champion
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.

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:

Capture.PNG

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:

Capture2.PNG
DAX for Assigned:

Capture3.PNG

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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