Wanting to sum a field based on a field in another table

[Anonymous]

Hi,

I am wanting to calculate what our national advertising is based on the sourcecode.  The following code I have is 

National = CALCULATE(sum(insertBK[InsertNetCost]),adtrans[SourceCode] = "BA430", adtrans[SourceCode] = "CA148",adtrans[SourceCode] = "HX355",adtrans[SourceCode] = "MD301")

However it is returning blank in the table, Any help appreciated.

Thanks

Chris

[BhaveshPatel · Accepted solution]

Hi Chris,

Filter Arguments in CALCULATE are evaluated with AND operator. If you would like to evaluate the conditons in OR operator, You can either use || operator or OR.

[LaurentCouartou · Accepted solution]

You provide several filter conditions based on the same column. Those are contradictory.

Basically, your expression reads as :

Calculate SUM(insertBK[InsertNetCost]) WHERE adtrans[SourceCode] = "BA430" AND adtrans[SourceCode] = "CA148" AND adtrans[SourceCode] = ...

Obviously, this will not work.

You probably intended to write something like:

Calculate SUM(insertBK[InsertNetCost]) WHERE adtrans[SourceCode] = "BA430" OR adtrans[SourceCode] = "CA148" OR adtrans[SourceCode] = ...

You could write this, this way :

CALCULATE( SUM(insertBK[InsertNetCost]
, adtrans[SourceCode] = "BA430" 
|| adtrans[SourceCode] = "CA148" 
|| adtrans[SourceCode] = ...
)

Alternatively, you may want to look at the IN operator:

https://pbidax.wordpress.com/2016/11/07/dax-in-operator/

[LaurentCouartou · Accepted solution]

You provide several filter conditions based on the same column. Those are contradictory.

Basically, your expression reads as :

Calculate SUM(insertBK[InsertNetCost]) WHERE adtrans[SourceCode] = "BA430" AND adtrans[SourceCode] = "CA148" AND adtrans[SourceCode] = ...

Obviously, this will not work.

You probably intended to write something like:

Calculate SUM(insertBK[InsertNetCost]) WHERE adtrans[SourceCode] = "BA430" OR adtrans[SourceCode] = "CA148" OR adtrans[SourceCode] = ...

You could write this, this way :

CALCULATE( SUM(insertBK[InsertNetCost]
, adtrans[SourceCode] = "BA430" 
|| adtrans[SourceCode] = "CA148" 
|| adtrans[SourceCode] = ...
)

Alternatively, you may want to look at the IN operator:

https://pbidax.wordpress.com/2016/11/07/dax-in-operator/

[Anonymous]

Ah think I get what you mean, wil try it out.

[Anonymous]

It works!  Thanks Guys!

[Anonymous]

Thanks Laurent, 

I wanting it to accumativley add them as they are all the national sourcecodes, so eventually I am wanting to put a measure in my table which gives me the national split.

Thanks

Chris

[BhaveshPatel · Accepted solution]

Hi Chris,

Filter Arguments in CALCULATE are evaluated with AND operator. If you would like to evaluate the conditons in OR operator, You can either use || operator or OR.

[Anonymous]

Hi Bhavesh

Thanks for replying I am wanting to accumativley add them so looking for an AND.

It just seems to be returning 0.

Thanks

Chris

[LaurentCouartou]

If you want to see items with a sourcecode BA430 and items with a sourcecode CA148, you actually want to use OR.

You want to see all items with either a sourcecode BA430 or a sourcecode CA148 or ...

[Anonymous]

If I am wanting the opposite I have put

Local Split = CALCULATE( SUM(insertBK[InsertNetCost]), adtrans[SourceCode] <> "BA430" 
|| adtrans[SourceCode] <> "CA148" 
|| adtrans[SourceCode] <> "HX355" || adtrans[SourceCode] <> "MD301"
)

but that is giving the same value as SUM(insertBK[InsertNetCost])  with them in.. any idea?

[LaurentCouartou · Accepted solution]

In that case, you want to see items where [SourceCode] <> "BA430" AND adtrans[SourceCode] <> "CA148" , AND ...

The && operator is what you need.

adtrans[SourceCode] <> "BA430" 
&& adtrans[SourceCode] <> "CA148" 
&& adtrans[SourceCode] <> "HX355"
&& adtrans[SourceCode] <> "MD301"

[Anonymous]

Thank you very much for the help laurent

[Anonymous]

The join colums is [adnumbr].