Filter by Multiple Column Value Conditions

[DoesNotCompute]

Hi There,

I've been struggling with this for hours and can not figure a solution. Any help greatly appreciated.

I have a data set like the below. What I need to do via DAX (I'm trying to work in PowerPivot) is to exclude any City/State that is not in both retailers. Or to say, both retailers must be in the City/State for that City/State to remain in the data set.

Tried regular value filters in the pivot table (less than 0) and that did not work, so thinking I need to filter via the data model in Power Pivot.

So for the example below, I would exclude both Springfield, ID (only Retailer A is located here) and Phoenix, AZ (only Retailer B is located here).

Data Set:

Retailer	City, State	Month	Sales
Retailer A	Los Angeles, CA	1/1/2024	100
Retailer A	Las Vegas, NV	1/1/2024	200
Retailer A	Springfield, IL	1/1/2024	100
Retailer A	Springfield, ID	1/1/2024	100
Retailer B	Los Angeles, CA	1/1/2024	200
Retailer B	Las Vegas, NV	1/1/2024	100
Retailer B	Springfield, IL	1/1/2024	200
Retailer B	Phoenix, AZ	1/1/2024	100

Desired Output: 

[lbendlin]

You can consider using PRODUCTX and throwing away the BLANK()  results.

[DoesNotCompute]

I'm not familiar with PRODUCTX and the below solution from @DataNinja777 worked great. However, I will spend some time exploring this avenue as well to improve my skills. Thank you for the suggestion.

[DataNinja777 · Accepted solution]

Hi @DoesNotCompute 

I produced your required output as follows:

An example of the dax formulae to achive this output is as follows:  

Where the data model looks like below:

Best regards,

[DoesNotCompute]

Fantastic, thank you so much.