DAX - using if formula in filter

Question

I am getting the error: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used in a table filter expression.

SALES (TOTAL CAT):=
CALCULATE([SALES],
  ALLEXCEPT(data,products[Category]),
  IF(
    geography[Country]="USA",
    data[Market]="Total USA",
    data[Market]="Total Australia"
   )
)

I can't use RELATED since that throws an error as well.

Answer 1

Try it like this:

SALES (TOTAL CAT):=
 var mrkt = IF(MAX(geography[Country]) = "USA", "Total USA", "Total Australia")
 return CALCULATE(
  [SALES],
  ALLEXCEPT(data, data[Category]),
  data[Market] = mrkt
)