cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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

3 ACCEPTED SOLUTIONS
BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

LaurentCouartou
Solution Supplier
Solution Supplier

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/

View solution in original post

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"

View solution in original post

11 REPLIES 11
LaurentCouartou
Solution Supplier
Solution Supplier

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/

View solution in original post

Anonymous
Not applicable

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

Anonymous
Not applicable

It works!  Thanks Guys!

Anonymous
Not applicable

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
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

Anonymous
Not applicable

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

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

 

Smiley Wink

 

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

Anonymous
Not applicable

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?

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"

View solution in original post

Anonymous
Not applicable

Thank you very much for the help laurent

Anonymous
Not applicable

The join colums is [adnumbr].

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!