Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
125 | |
32 | |
27 | |
24 | |
23 |