Forum Discussion

kkaeding's avatar
kkaeding
Regular Visitor
9 years ago

Lookup in a table based on multiple critera

I have two tables imported into Power BI Desktop

  • A list of record IDs and their status on a specific date
  • The ownership history of the records, along with the date that the change was made, and the old and new values

I need to add a column to the first table that will show the owner of the record on the given date.

 

I've tried a couple fo DAX formulas, including LOOKUPVALUE and various IF statements, but keep running into circular references.

 

If I were in Excel, I would do a vba loop to cycle through, but I'm sure there has to be a way to do it in Power BI.

 

Any help would be greatly appreciated

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi kkaeding

    You can achieve this in Power Query aka GetData.

    But to answer your question in detail, it would be great to have at least dummy data or pictures of the dataset.

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    Hi kkaeding,

     

    How did you create relationship between those two tables? Please share same data and picture about expected results.

     

    Best Regards,
    Qiuyun Yu

    • MarcelBeug's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Sort your Owners table on RecID and Date (descending)

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Owners"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RecID", Int64.Type}, {"Date", type date}, {"OldOwner", type text}, {"NewOwner", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"RecID", Order.Ascending}, {"Date", Order.Descending}})
in
    #"Sorted Rows"

       

      Now merge the tables and select the first owner with a Date <= RecDate.

      With the code below, if no owner exist for the RecID with a Date <= RecDate, Owner will be null.

       

      let
    Source = Table.NestedJoin(Records,{"RecID"},Owners,{"RecID"},"OwnerTable",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Owner", (CurrentRecord) => List.First(Table.SelectRows(CurrentRecord[OwnerTable], each [Date] <= CurrentRecord[RecDate])[NewOwner])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"OwnerTable"})
in
    #"Removed Columns"

       

      In the example below (with Records - Owners - Result), RecID 2 has no owner yet on 3/1/2017 and RecID 3 has no owner at all (yet).