Return values from related tables

[johnmelbourne]

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

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

[hnguy71 · Accepted solution]

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