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?

Can you edit your question to format your example data into a table. This way we can get a better idea of how your data looks. Also, is Sum of Sales a calculated column that you have added to your powerpivot model? — dotNetE
Typically you would create your Measure to filter on SKU, then use the User Interface to filter for the SKUs you want (via slicer or filter) — guitarthrower

dotNetE dotNetE · Accepted Answer · 2015-05-19T21:30:38

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"
))

Using Multiple filters in DAX

4 Answers