Search code examples
powerbipowerpivotdax

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!

Solution

  • 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.