How to sum cells which are also present in mapping table?

[Anonymous]

Hi all,

How do I create a measure that does the following:

Sum table 1[column A] for all rows where table 1[column B] is equal to a value in table 2[column C]

Right now I am writing it as:

Measure 2 = Calculate(SUM(Table1[ColumnA]), Table1[ColumnB]=Table2[ColumnC])

But PowerBI is returning an error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

In Excel, I would write this as:

SUMIF(ColumnB, Table2'ColumnC', ColumnA)

Please help.

Thanks!

[amitchandak]

@Anonymous , if they have one to many relation

Measure 2 = Calculate(SUM(Table1[ColumnA]), filter(Table1,Table1[ColumnB]=related(Table2[ColumnC])))
or
Measure 2 = Calculate(SUM(Table1[ColumnA]), filter(Table2,Table2[ColumnB]=related(Table1[ColumnC])))

Measure 2 = Calculate(SUM(Table1[ColumnA]), filter(Table1,Table1[ColumnB]=max(Table2[ColumnC])))
for some row context // group by

Measure 2 = Calculate(SUMX(value(Table1[ID]), Table1[ColumnA]), filter(Table1,Table1[ColumnB]=max(Table2[ColumnC])))

[Anonymous]

When I use either one of the two first options, PowerBI tells me it is unable to find Table2.  This is strange as there exists a join between Table2 and Table1.  I am unable to make any changes in the model view due to permissions.  Do you know what could be the cause for the measure not working?