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.
I have a data set like the below and am building a report in Power Pivot. Thanks to previous help HERE, I was able to make a pivot table that excluded any citites from the pivot table that were not in both retailers. Or to say, both retailers must be in the City/State for that City/State to remain in the pivot table.
I'm now trying to take this one step further and again hitting a wall. There are two fixture types: "Shelf" and "Endcap." My goal is to also exclude any cities from the pivot table that have a Shelf.
My current method that is failing:
HasShelf:=CALCULATE(
DISTINCTCOUNT('Dataset'[Fixture]),
FILTER(Fixture,Fixture[Fixture] = "Shelf")
)
BothRtlrsECOnly:=SUMX(
City_State,
IF( [DistinctRtlr] > 1 && [HasShelf] < 1 , [Amount], BLANK())
)
What I can't understand is that when I manage the data model, this gives me the correct result (total sales = 600). However, when I make the pivot table using this measure, it still includes Los Angeles (though it does exclude the sales for Retailer A, the retailer that has the shelf. It only pulls the sales for Retailer B, which does not have the shelf).
Still very much a DAX novice, I appreciate any help.
Sample dataset & screen shots below:
Sample Dataset:
Retailer | City, State | Month | Sales | Fixture |
Retailer A | Los Angeles, CA | 45292 | 100 | Shelf |
Retailer A | Los Angeles, CA | 45292 | 50 | Endcap |
Retailer A | Las Vegas, NV | 45292 | 200 | Endcap |
Retailer A | Springfield, IL | 45292 | 100 | Endcap |
Retailer A | Springfield, ID | 45292 | 100 | Endcap |
Retailer B | Los Angeles, CA | 45292 | 200 | Endcap |
Retailer B | Las Vegas, NV | 45292 | 100 | Endcap |
Retailer B | Springfield, IL | 45292 | 200 | Endcap |
Retailer B | Phoenix, AZ | 45292 | 100 | Endcap |
Solved! Go to Solution.
Thank you for your follow up and clarification of the issue. I tried to produce your required output in a single formula, but it didn't work, but when I split the measure in two, first producing the measure like below, and then in a separate measure, sumxing it over 'City, State' dimension, it produced your required output. I am not sure why it couldn't be done in one measure, but it must be due to the intricate filter contect related matter as you said.
In the second measure where it sumxed over the 'City, State' dimension, it is simply like below.
Sumx_City_State_BothRtlrsECOnly = sumx(City_State,[BothRtlrsECOnly])
I also encountered similar issues, where it didn't produce the intended output in one long measure, but when I split it out in two measures, it produced the required output.
I attach the pbix file below.
Thank you for your follow up and clarification of the issue. I tried to produce your required output in a single formula, but it didn't work, but when I split the measure in two, first producing the measure like below, and then in a separate measure, sumxing it over 'City, State' dimension, it produced your required output. I am not sure why it couldn't be done in one measure, but it must be due to the intricate filter contect related matter as you said.
In the second measure where it sumxed over the 'City, State' dimension, it is simply like below.
Sumx_City_State_BothRtlrsECOnly = sumx(City_State,[BothRtlrsECOnly])
I also encountered similar issues, where it didn't produce the intended output in one long measure, but when I split it out in two measures, it produced the required output.
I attach the pbix file below.
@DataNinja777 This worked great, thank you very much for your help.
Also, I did not consider using variables within the first measure as an alternative to the excess measures within the file. Very good idea, keeps everything cleaner.
Hi @DataNinja777 ,
Thanks for taking a look at this. Your measure does not produce the desired result - it does make the Grand Total match to the visual on the table.
However, the desired result is to keep the Grand Total as my original measure had it (so 600), but
I'm sure there is something I am not understanding about filter context here, but I cannot figure it out.
User | Count |
---|---|
55 | |
46 | |
18 | |
16 | |
15 |
User | Count |
---|---|
115 | |
42 | |
41 | |
27 | |
22 |