Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LeonLi
Regular Visitor

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.

Screenshot (15).png

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

Screenshot (16).png

Error 2

Screenshot (18).png

 

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!

2 REPLIES 2
amitchandak
Super User
Super User

@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

Hi, 

 

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

 

Screenshot (19).png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.