topic Re: Filter by Multiple Column Value Conditions in DAX Commands and Tips

Filter by Multiple Column Value Conditions

DoesNotCompute — Fri, 12 Apr 2024 23:01:35 GMT

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:

Re: Filter by Multiple Column Value Conditions

DataNinja777 — Sat, 13 Apr 2024 05:35:42 GMT

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,

Re: Filter by Multiple Column Value Conditions

lbendlin — Sat, 13 Apr 2024 21:30:10 GMT

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

Re: Filter by Multiple Column Value Conditions

DoesNotCompute — Mon, 15 Apr 2024 23:21:57 GMT

Fantastic, thank you so much.

Re: Filter by Multiple Column Value Conditions

DoesNotCompute — Mon, 15 Apr 2024 23:23:29 GMT

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.