Forum Discussion

johnmelbourne's avatar
johnmelbourne
Icon for Helper V rankHelper V
7 years ago
Solved

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

 

 

 

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

 

Need Help
  • hnguy71's avatar
    hnguy71
    7 years ago

    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

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    7 years ago

    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