Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Lookup value from one row and return value from a different row

Hello Everyone,

 

This is my first post so apologies if I am posting this in the wrong place. I am also very new to PowerBI so I will try to explain this to the best of my ability ðŸ™‚

 

Currently I am trying to build a PBI report that uses 2 SQL tables mainly. 

 

Table 1 has a title column where I have job references and streets, however if you want to find let's say what streets belong to project X, you have a Parentid and cascadeid that connects them. The parentID and cascade are saved separately on table2 which I have been able to lookup them with 2 custom columns on table 1 using the CALCULATE function.

 

Where I am stuck right now is that I want to create another column that looks at the ParentID, finds the same CascadeID on a different line and returns the title, so I can have both the job ref and street on the same row. 

 

This is an example:

These streets all have the Parent ID 720266:

And the Job Reference match on the cascadeid is this:

And I want a calculated column that everytime the ParentID check matches the Cascade Check, regardless of the line, returns the Title value of the matched line. It would look a bit like this:

 

Thanks in advance for all your help

 

Diogo.

Need Help
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    Table1:

    Table2:

    Create a calculated column in Table1:

    Job Reference Check =
LOOKUPVALUE (
    'Table2'[Title],
    'Table2'[Cascade Check], 'Table1'[ParentID Check]
)

    Get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    Table1:

    Table2:

    Create a calculated column in Table1:

    Job Reference Check =
LOOKUPVALUE (
    'Table2'[Title],
    'Table2'[Cascade Check], 'Table1'[ParentID Check]
)

    Get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • petrawiggin's avatar
      petrawiggin
      Icon for Helper I rankHelper I
      2 years ago

      Hello! 

      How would this be done if all the data were in the same table? Example:

      KeyYearVendorOutwardReferenceInwardReference
      12342020A7890 
      23452021A6789 
      67892024A 2345
      78902024B 1234
      99822024B  
      10012020A2002 
      20022020A 1001

      I want to look up each OutwardReference in the Key column and then return the year and vendor of the row where it is found.

      EDIT

      Actually, I realize I also need an IF statement in there: I only want the later year and later status IFF the later year is different from the year. (This is data from JIRA and I only want to know about the status of a cloned ticket if it was cloned into a project with a different year, not if it was cloned into a project in the same year it was created itself.)

      So, the results would look like this:

      KeyYearStatusOutwardReferenceInwardReferenceLaterYearLaterStatus
      12342020A7890 2024B
      23452021A6789 2024A
      67892024A 2345  
      78902024B 1234  
      99822024B    
      10012020A2002   
      20022020A 1001  

      What is the best way? Thank you.

  • petrawiggin's avatar
    petrawiggin
    Icon for Helper I rankHelper I
    2 years ago

    Hello! 

    How would this be done if all the data were in the same table? Example:

    KeyYearVendorOutwardReferenceInwardReference
    12342020A7890 
    23452021A6789 
    67892024A 2345
    78902024B 1234
    99822024B  

    I want to look up each OutwardReference in the Key column and then return the year and vendor of the row where it is found. So, the results would look like this:

    KeyYearStatusOutwardReferenceInwardReferenceLaterYearLaterStatus
    12342020A7890 2024B
    23452021A6789 2024A
    67892024A 2345  
    78902024B 1234  
    99822024B    

    What is the best way? Thank you.

    SECOND EDIT

    The way I figured out to do it is to

    1. merge the table against itself
    2. expand the columns I want to compare and to show (year and status)
    3. create conditional columns that...
      1. IF the expanded year column is null returns a null (where the outward reference doesn't match the key of another row, the new columns shouldn't try to do math and return an error)
      2. Otherwise compares the original year column to the expanded year column from the merge and if the expanded one is later then put in that year as the later year
    4. same thing but if the year is later put in that status as the later status
    5. remove the expanded columns and just leave the custom ones

    Is there a more efficient way to do this?