Filter Results by Multiple Tables

[DoesNotCompute]

I have a data set like the below and am building a report in Power Pivot. Thanks to previous help HERE, I was able to make a pivot table that excluded any citites from the pivot table that were not in both retailers. Or to say, both retailers must be in the City/State for that City/State to remain in the pivot table.

• For this example, when I pull a pivot table with the new measure "Both Retailers," Phoenix AZ and Springfield ID are excluded.

I'm now trying to take this one step further and again hitting a wall. There are two fixture types: "Shelf" and "Endcap." My goal is to also exclude any cities from the pivot table that have a Shelf.

• In this instance, the pivot table should now also exclude Los Angeles, CA

My current method that is failing:

1. Create a measure to count City/State locations with a shelf fixture. This yeilds a value of "1" as only Los Angeles CA has a store with a shelf fixture:

   1. HasShelf:=CALCULATE(

      DISTINCTCOUNT('Dataset'[Fixture]),

      FILTER(Fixture,Fixture[Fixture] = "Shelf")

      )

2. Create a measure to total my sales amount where the City/State has both retailers and where the City/State does not have a Shelf.

   1. BothRtlrsECOnly:=SUMX(

      City_State,

      IF( [DistinctRtlr] > 1 && [HasShelf] < 1 , [Amount], BLANK())

      )

What I can't understand is that when I manage the data model, this gives me the correct result (total sales = 600). However, when I make the pivot table using this measure, it still includes Los Angeles (though it does exclude the sales for Retailer A, the retailer that has the shelf. It only pulls the sales for Retailer B, which does not have the shelf).

Still very much a DAX novice, I appreciate any help.

Sample dataset & screen shots below:

Sample Dataset:

Retailer	City, State	Month	Sales	Fixture
Retailer A	Los Angeles, CA	45292	100	Shelf
Retailer A	Los Angeles, CA	45292	50	Endcap
Retailer A	Las Vegas, NV	45292	200	Endcap
Retailer A	Springfield, IL	45292	100	Endcap
Retailer A	Springfield, ID	45292	100	Endcap
Retailer B	Los Angeles, CA	45292	200	Endcap
Retailer B	Las Vegas, NV	45292	100	Endcap
Retailer B	Springfield, IL	45292	200	Endcap
Retailer B	Phoenix, AZ	45292	100	Endcap

Los Angeles should not pull into this pivot table at all

[DataNinja777 · Accepted solution]

Hi @DoesNotCompute 

Thank you for your follow up and clarification of the issue. I tried to produce your required output in a single formula, but it didn't work, but when I split the measure in two, first producing the measure like below, and then in a separate measure, sumxing it over 'City, State' dimension, it produced your required output.  I am not sure why it couldn't be done in one measure, but it must be due to the intricate filter contect related matter as you said.  

In the second measure where it sumxed over the 'City, State' dimension, it is simply like below.  

Sumx_City_State_BothRtlrsECOnly = sumx(City_State,[BothRtlrsECOnly])

I also encountered similar issues, where it didn't produce the intended output in one long measure, but when I split it out in two measures, it produced the required output.  

I attach the pbix file below.  

[DoesNotCompute]

@DataNinja777  This worked great, thank you very much for your help.

Also, I did not consider using variables within the first measure as an alternative to the excess measures within the file. Very good idea, keeps everything cleaner.

[DataNinja777]

Hi @DoesNotCompute 

Does the below output meet your requirement?  

I attach an example pbix file.  

[DoesNotCompute]

Hi @DataNinja777 ,

Thanks for taking a look at this. Your measure does not produce the desired result - it does make the Grand Total match to the visual on the table.

However, the desired result is to keep the Grand Total as my original measure had it (so 600), but

1. Los Angeles should be removed from the table entirely, because it has a Shelf fixture.
2. Retailer B Total should be 300 instead of 500 (because it should not be capturing Los Angeles).

I'm sure there is something I am not understanding about filter context here, but I cannot figure it out.