Forum Discussion

LeonLi's avatar
LeonLi
Regular Visitor
2 years ago

RELATED and LOOKUPVALUE Functions Issues in Limited/Weak Relationship

Hi,

 

I have created a power bi report that imported a table from a semantic model (i.e 'Bills', say table A), plus an import of csv file locally (i.e 'BillApprovalAuditReport', say table B), which basically forms a composite model with limited relationship between two table.

I would like to add a new column in table A by getting "Bill Approved Date Time" column data from table B, based on the same "billid" (refer to underlined marks) in both tables.

 

However, I tried both RELATED() and LOOKUPVALUE() functions, none of them worked.

Error 1

Error 2

 

Does anyone one know there would be an alternative solutions so that I can retrieve a column from another table please?

Thank you in advance for your help!

Need Help
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    LeonLi , Can create new column like

     

     

    New Column in Table 1=

    Sumx(Filter(Table2, Table2[ID] = Table1[ID]  ) , Table2[Value])

     

    or

     

    New Column in Table 1=

    Sumx(Filter(Table2, Table2[ID] = Table1[ID] && Table1[Date]>= Table2[Strat Date] && Table1[Date] <= Table2[End Date] ) , Table2[Value])

     

    refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
    https://www.youtube.com/watch?v=Wu1mWxR23jU
    https://www.youtube.com/watch?v=czNHt7UXIe8

    • LeonLi's avatar
      LeonLi
      Regular Visitor
      2 years ago

      Hi, 

       

      Thank you for your answers, but it seems the same error occurs after I used SUMX function.

       

       

      Since [billid] and [Api Billid] column is a combination of numbers and letters so they're TEXT data type , and [Bill Approved Date Time] column is DATE/TIME data type, I'm not sure if I could use SUMX function to retrieve non-numeric values in this case.