Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowGet inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.
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:
Solved! Go to Solution.
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,
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
20 | |
14 | |
11 | |
10 | |
8 |