cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnmelbourne Regular Visitor
Regular Visitor

Return values from related tables

Hi, I have three related tables in a rather big ERD, and I want to do a test of values in two related tables, and return a value from a third related table,

The best I can explain it is

IF Customer.Name = "Apple Inc" and Location.State = "Kansas" Then Return the value from AnotherRelatedTable.Field

 

OR in psuedo SQL

Select AnotherRelatedTable.Field Where Customer.Name = "Apple Inc" and Location.State = "Kansas" 

 

OR

 

IF TableA.ColumnA = TABLEC.ColumnA and TABLEB.ColumnA = TABLEC.ColumnB then return TABLEC.ColumnC

 

 

Capture.PNG

 

I am new to PBI and am looking around at FILTER, RELATED, CALCULATE, CALCULATETABLE, and am getting abit lost as to how I could effect this.

 

Any assistance would be appreciated

Regards

John

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
hnguy71 Member
Member

Re: Return values from related tables

Try this:

 

LookMeUp = 
//var LookUpVersion1 = LOOKUPVALUE(Table3[Column2], Table3[Column1], Table1[Column1])
var LookUpVersion2 = CALCULATE(VALUES(Table3[Column2]), FILTER(Table3, Table3[Column1] = Table1[Column1]))
return
CALCULATE(VALUES(Table3[Column3]), FILTER(Table3, Table3[Column2] = LookUpVersion2))

You can use either version 1 or version 2 to get the same result. I'm sure there's a better way to accomplish this

1 REPLY 1
Highlighted
hnguy71 Member
Member

Re: Return values from related tables

Try this:

 

LookMeUp = 
//var LookUpVersion1 = LOOKUPVALUE(Table3[Column2], Table3[Column1], Table1[Column1])
var LookUpVersion2 = CALCULATE(VALUES(Table3[Column2]), FILTER(Table3, Table3[Column1] = Table1[Column1]))
return
CALCULATE(VALUES(Table3[Column3]), FILTER(Table3, Table3[Column2] = LookUpVersion2))

You can use either version 1 or version 2 to get the same result. I'm sure there's a better way to accomplish this