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
2,129 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,970 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
662 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 22, 2019 in Power BI by anonymous
• 33,010 points

edited Dec 31, 2021 by Soumya 13,151 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,970 points
731 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,970 points
10,088 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,551 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,254 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 1,108 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
439 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,970 points
1,388 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,970 points
447 views
webinar REGISTER FOR FREE WEBINAR
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP