Using Multiple filters in DAX

Question

I'm new to DAX.

I'm currently using Power BI and trying to create a total sum of sales that use a few different SKUs (or IDs)

I can use this to filter down to 1 sku ("A1"):

Measure = CALCULATE([Sum of Sales],Table4[SKU]="A1")

but I would like to filter down to five different SKUs.

Example Data:

2      1,050.54
3     43,925.20
4      8,596.00
5      1,630.00
8      3,330.00
A1        45.24
A2       499.87
A3    53,567.05
A4       337.92
A5     4,265.00
AB    12,651.94
ACC    7,634.95
ADV   -1,769.95
ANT        1.60
AUTO   9,655.40
BOOT     268.00

Is this possible?

Solution 1

CALCULATE is defined as CALCULATE(<expression>,<filter1>,<filter2>…) This means that you can use multiple filters at one time. However, the multiple filters will act at the same time. Meaning that the data would have to meet both conditions.

So doing BadSumOfSales:=CALCULATE([Sum of Sales],Table3[SKU]="A1",Table4[SKU]="AB") will not give you what you need. Since the SKU would have to be equal to A1 and AB, it will return blank

Since you have five items you would like to included in your filtering, you should leverage the SWITCH function. This will allow you to accommodate multiple conditions that should return TRUE and then let you return FALSE for anything else

TotalsSumOfSales:=CALCULATE([Sum Of Sales],
SWITCH(Table4[SKU],
    "A1",TRUE(),
    "A2",TRUE(),
    "A3",TRUE(),
    "4" ,TRUE(),
    "5" ,TRUE(),
    "8" ,TRUE(),
      FALSE()  
))

Another way to get around this would be using the OR function. This is a great option, but only really works well when you have two filters at a time. If you have more than two, you will have to do some nesting that can get complex. So for your case I would stick with the SWITCH, but here is an example of how it would look:

OrTotalSumOfSales:=CALCULATE([Sum of Sales],
OR(
    Table4[SKU]="A1",
    Table4[SKU]="A2"
))

Solution 2

The best idea in this case is to use IN Operator in DAX. The Performance is way better than pipe operator or OR and the code becomes more readable.

This is only supported in the latest versions of DAX.

GroupingSales:=CALCULATE([Sum of Sales],Table[SKU] IN {"A1","A2","A3","AB"})

You can also use CONTAINSROW.

More info: https://www.sqlbi.com/articles/the-in-operator-in-dax/

Solution 3

Just a third way alternative to the Or version. You can use the double pipes '||' which acts as an Or statement in the filter.

Measure = CALCULATE([Sum of Sales],Table4[SKU]="A1" || Table4[SKU]="A2" || 
Table4[SKU]="A3" ||Table4[SKU]="A4" || Table4[SKU]="A5")