topic Re: LOOKUP within the same table in DAX Commands and Tips

LOOKUP within the same table

IvanS — Mon, 13 Dec 2021 15:37:35 GMT

Hey guys,

I browsed several sources about how to lookup value within the same table, however any of those met my requirements.

I have one huge agile table that consists of stories (sub-tasks) and epics (main tasks/milestones). Every story is assigned under epic using the Epic Link reference. I would like to create calculated column/measure that will state the name of Epic on the story level. So basically, when the Epic Link value matches with Issue_Unique Key, the Epic name value will be copied into Story Epic Name column.

Example below:

Issue_Unique Key	Issue_Type	Issue Summary	Epic Name	Epic Link	Story Epic Name (desired result using DAX)
DEV-1	Epic	Epic 1 Main task	Epic 1 Name
DEV-2	Story	Story sub-task		DEV-1	Epic 1 Name
DEV-3	Story	Story sub-task		DEV-1	Epic 1 Name
DEV-4	Epic	Epic 2 Main task	Epic 2 Name
DEV-5	Story	Story sub-task		DEV-4	Epic 2 Name

Any suggestions are much appreciated!

Re: LOOKUP within the same table

ValtteriN — Mon, 13 Dec 2021 15:49:18 GMT

Hi,

Like you suggested Lookup is the way to go here is my DAX:

Epic = LOOKUPVALUE(Epics[Epic Name],Epics[ssue_Unique Key],Epics[Epic Link])

I am not sure if I am missing something, but the column returned matches your desired outcome:

I hope this helps to solve your issue and if it does consider accepting this as a solution!

Re: LOOKUP within the same table

IvanS — Mon, 13 Dec 2021 16:11:11 GMT

Hi ValtteriN ,

yes, this is pulling the data into table - yet I need to add it into Matrix - for hiding the data on aggregated level in matrix I am using measure that consists of ISFILTERED.

IF(ISFILTERED(Issues[ISSUE_KEY]), SELECTEDVALUE(Issues[ISSUE_CURRENT_TYPE_NAME]), BLANK())

When creating your function as column, Power BI is automatically setting First with options Last, Count & Distinct count. Is there any way how to hide this information (or at least to write your function as measure)?

The main issue with measures is that they cannot work with duplicate values and giving the below error.

Re: LOOKUP within the same table

ValtteriN — Mon, 13 Dec 2021 16:20:20 GMT

I see, for a measure MAX() or SELECTEDVALUE() should solve the issue:

Re: LOOKUP within the same table

smpa01 — Mon, 13 Dec 2021 16:34:51 GMT

IvanS you can use this measure

Measure = VAR _epicName = MAX ( 'Table 1'[Epic Link] ) RETURN CALCULATE ( MAX ( 'Table 1'[Epic Name] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[Issue_Unique Key] = _epicName ) )

Re: LOOKUP within the same table

IvanS — Mon, 13 Dec 2021 17:34:51 GMT

Hi smpa01

for some reason, the function is not working in measure. Please find below - the names in official file are a bit different. You can see that calculated column is getting the Epic name however measure is blank (except one line from the list).

Re: LOOKUP within the same table

smpa01 — Mon, 13 Dec 2021 18:01:08 GMT

IvanS can you provide a sample pbix?

Re: LOOKUP within the same table

IvanS — Tue, 14 Dec 2021 09:00:44 GMT

I found the root cause of the issue - there was bilateral relationship between 2 tables which are part of matrix table. After change to sigle filterring, the both measures are working.

I will mark measure from ValtteriN as solution as it is not that much performance consuming. However thank you too for help!