Forum Discussion
Multiple filters overlapping
I can't seem to figure this one out. I am trying to create a bookmark view where I see the data based on two criteria. However, these criteria overlap.
To simplify, suppose I want a view that shows ALL sales for America AND sales for a specific customer globally. When I add a filter for US, I see all sales in the US, but I can't see sales globally. When I filter for a specific customer, I see sales globally for the customer, but I don't see all sales for the US. How to I create a filter for two criteria (Region and Customer) that says "US AND Customer # 12345?"
I found a fantastic article that solved this for me.
Applying filters with OR and XOR conditions in Power BI — Apex Insights: Power BI tips & tricks
It is important to note that the measure goes here:
AND you need to create the tables using VALUES DAX here:
6 Replies
jwin2424 , Create two independent table for Country and customer and use those as per need in filter
example
calculate(Sum(Fact[Value]), filter(Fact, Fact[Customer] in values(Customer[Customer]) ))
and
calculate(Sum(Fact[Value]), filter(Fact, Fact[Country] in values(Country[Country]) ))Another way is to use removefilters or all if they are from the same/related tables
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak
Hi jwin2424 ,
If you want to ingore the Country filter to get global sales, you could use ALLEXECPT() ——
Removes all context filters in the table except filters that have been applied to the specified columns.
globally sales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Customer]))Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello there. I am sorry, I was unable to respond to this in a timely manner and am just now able to check back in. I am not sure who marked the answer as correct, but I was unable to get the formula above to work.
Here is a screenshot of what I am trying to do:
The first Matrix shows all the data in my database. The second matrix filters that data by account name (Google), and the last matrix shows it filtered by the territory ID (A112 and A115).
I want to create a view where I can see the sales for Google AND Territories A112 and A115. Right now, I only know how to do either/or.
Ideally, I would want to see this:Customer Territory Amount Apple A115 $4,297.01 Apple A112 $9,921.39 Google A112 $7,650.20 Google A112 $8,944.28 Google A112 $7,650.20 Google A112 $9,421.05 Google A114 $1,543.36 Google A112 $8,944.28 Google A114 $2,906.24 Google A117 $3,507.63 Google A113 $1,632.03 Google A113 $859.21 Google A118 $3,106.57 Google A112 $9,421.05 Tesla A112 $5,807.01
Here, I can see every Google order AND every order under those two territories. I tried your formula a few different ways, but I am not understanding the logic of it so maybe I am not relating it to my table correctly. The above example is what I am looking for and the fields are the same as mine.
Hopefully this clarifies it better.Thanks!
Hi,
To your Table visual, drag the Customer, Territory and the measure.
I found a fantastic article that solved this for me.
Applying filters with OR and XOR conditions in Power BI — Apex Insights: Power BI tips & tricks
It is important to note that the measure goes here:
AND you need to create the tables using VALUES DAX here:
