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...
Need Help
Tutorial Requests
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.