Clever Geek Handbook

Using multiple filters in DAX

I am new to DAX.

I am currently using Power BI and am trying to generate a total of sales that use several different SKUs (or IDs)

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

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

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

Sample 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 it possible?

+3


source to share


4 answers


CALCULATE is defined as CALCULATE(<expression>,<filter1>,<filter2>…)

This means that you can use multiple filters at the same time. However, several filters will work at the same time . This means that the data must meet both conditions.

Thus, BadSumOfSales:=CALCULATE([Sum of Sales],Table3[SKU]="A1",Table4[SKU]="AB")

it will not give you what you need. Since the SKU must be equal to A1 and AB, it will return empty

Since you have five elements that you would like to include in your filtering, you must use the SWITCH function . This will allow you to place multiple conditions that must return TRUE and then allow you to 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 to use the OR function . This is a great option, but it only really works well when you have two filters at the same time. If you have more than two, you will need to do some nesting, which can get tricky. So for your case, I would stick with SWITCH , but here's an example of how it would look:

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


source


I think that based on your request to filter up to 5 different SKUs, you shouldn't actually be using DAX to solve your problem.

If you are just pasting a pivot table in Excel, you can add SKU to Rows and Sales to Values. If you need, you can customize the aggregate (but I think it defaults to SUM), so you already have the data you want. The only thing that remains is that you need to filter it only for the SKUs you need. You can do the same in Power View by simply creating a table. Then you can add SKU to filter panel and select 5 skins you want.



Check out using Power BI, -Lukasz

+1


source


The best idea in this case is to use the operator IN

in DAX. The performance is better than the pipe operator or OR

, and the code becomes more readable.

This is only supported in the latest DAX versions.

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

You can also use CONTAINSROW

.

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

+1


source


Just an alternative to the third version. You can use double pipes '||' which acts like the Or operator in the filter.

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


source






More articles:


All Articles