CALCULATE with OR condition in two tables

[webportal]

Hello,

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!

[Phil_Seamark · Accepted solution]

Hi @webportal

I got this working in a calculated measure.  You may just need to add the RELATED function to the OR condition

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

[webportal]

Sure, it works pretty well, thank you!
I suppose the "theory" behind this solution is to "bring" the Colors and Categories tables to the Table, treating them as one.
Really cool!

[Phil_Seamark]

Yeah, having relationships is effectly giving you a logical table that includes all the columns from the table on the 1 side of the relationship on the table from the many side.

Good to hear it works!