Using Multiple filters in DAX

0 votes

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?

Dec 22, 2020 in Power BI by Roshni
• 10,480 points
1,099 views

1 answer to this question.

0 votes

CALCULATE is defined as CALCULATE(<expression>,<filter1>,<filter2>…) This means that you can use multiple filters at one time. However, 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"
))
answered Dec 22, 2020 by Gitika
• 65,930 points

Related Questions In Power BI

0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,570 points
558 views
0 votes
1 answer

How to rename a column using DAX in Power BI?

Hi Ramya, I found these two function that ...READ MORE

answered Aug 21, 2019 in Power BI by anonymous
• 32,990 points
10,231 views
0 votes
1 answer

Left Outer Join using DAX in PowerBI.

You can simply write a few measures ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,930 points
586 views
0 votes
1 answer

How do I count rows in one table based on values in another table using DAX?

If the tables are related, this is ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,930 points
6,839 views
0 votes
1 answer

Embed URL-multiple filters in Power BI

With the release of 2.0.0 you can ...READ MORE

answered Oct 16, 2018 in Power BI by Annie97
• 2,200 points
2,364 views
0 votes
1 answer

Applying multiple filters in PBI Embed URL

Well, that is actually a limitation in ...READ MORE

answered Aug 21, 2018 in Power BI by DataKing99
• 8,240 points
2,118 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,220 points

edited Oct 11, 2018 by Kalgi 928 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 52,370 points
374 views
0 votes
1 answer

Calculate GB Using DAX in Power BI

Hi, @Vnk You can go through this regarding ...READ MORE

answered Jun 30, 2020 in Power BI by Gitika
• 65,930 points
840 views
0 votes
1 answer

Using DAX calculation how to calculate monthly budget till today in power bi Desktop?

You can make use of this: MTD Budget ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,930 points
274 views