topic Re: How to sum cells which are also present in mapping table? in DAX Commands and Tips

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

Anonymous — Mon, 07 Dec 2020 10:39:32 GMT

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!

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

amitchandak — Mon, 07 Dec 2020 12:52:20 GMT

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])))

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

Anonymous — Tue, 08 Dec 2020 02:50:27 GMT

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?