Forum Discussion
Filter by Multiple Column Value Conditions
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:
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,
4 Replies
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,
Fantastic, thank you so much.
You can consider using PRODUCTX and throwing away the BLANK() results.
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.
