Forum Discussion

IvanS's avatar
IvanS
Helper V
4 years ago
Solved

LOOKUP within the same table

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 KeyIssue_TypeIssue SummaryEpic NameEpic LinkStory Epic Name (desired result using DAX)
DEV-1EpicEpic 1 Main taskEpic 1 Name  
DEV-2StoryStory sub-task DEV-1Epic 1 Name
DEV-3StoryStory sub-task DEV-1Epic 1 Name
DEV-4EpicEpic 2 Main taskEpic 2 Name  
DEV-5StoryStory sub-task DEV-4 Epic 2 Name

 

Any suggestions are much appreciated!

Need Help
  • ValtteriN's avatar
    ValtteriN
    4 years ago

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

     

7 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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's avatar
      IvanS
      Helper V
      4 years ago

      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's avatar
        ValtteriN
        Community Champion
        4 years ago

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

         

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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's avatar
      IvanS
      Helper V
      4 years ago

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