LOOKUP within the same table

[IvanS]

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!

[smpa01]

@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 )
    )

[IvanS]

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).

[smpa01]

@IvanS  can you provide a sample pbix?

[IvanS]

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!

[ValtteriN]

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!

[IvanS]

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.

[ValtteriN · Accepted solution]

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