CODEWITHSUNDEEP
powerbipowerpivotdax
user882670
user882670

Reputation:

CALCULATE with OR condition in two tables

In order to Sum the sales amount of blue products OR products that belong to category shoes, I'm using the following DAX expression:

CALCULATE(

SUM(Table[SalesAmount]),

FILTER(

Table,

Table[Color] = "Blue" ||

Table[Category] = "Shoes")

)

However, this doesn't work with two different tables (Colors and Categories), like:

CALCULATE(

SUM(Table[SalesAmount]),

FILTER(

Table,

Colors[Color] = "Blue" ||

Categories[Category] = "Shoes")

)

Can anyone help?

Thanks!

Upvotes: 3

Views: 2545

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

Searching the web led me to this forum topic. Borrowing from OwenAuger's post, I propose the following formula:

CALCULATE(SUM(Table[SalesAmount]),
          FILTER(SUMMARIZE(Table, Colors[Color], Categories[Category]),
                 Colors[Color] = "Blue" ||
                 Categories[Category] = "Shoes"))

We get around the single table restriction by using SUMMARIZE to create a single table that has all of the pieces we need.

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

This might not be the ideal answer, but one way to do this is to use the inclusion-exclusion principle:

CALCULATE(SUM(Table[SalesAmount]), Colors[Color] = "Blue") + 
CALCULATE(SUM(Table[SalesAmount]), Categories[Category] = "Shoes") - 
CALCULATE(SUM(Table[SalesAmount]), Colors[Color] = "Blue", Categories[Category] = "Shoes")

Upvotes: 0

Related Questions