Multiple filters overlapping

[jwin2424]

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

[jwin2424 · Accepted solution]

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:

[Anonymous]

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.

[jwin2424]

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!

[Ashish_Mathur]

Hi,

To your Table visual, drag the Customer, Territory and the measure.

[jwin2424]

The fomula is on the bar above. HOWEVER, this formula seems to work only as a card. What I want is a table that displays the selected values in the filter. 

The filters by default use a logical AND argument. It will say "Google AND A112 AND A115." I am looking for a way to get the filter to use an OR argument. 

You can see the red boxed values on the left. I would like a table to show ONLY the red boxed valued (which are based on the filters on the right). 

[amitchandak]

@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