Forum Discussion

DoesNotCompute's avatar
DoesNotCompute
Regular Visitor
2 years ago
Solved

Filter Results by Multiple Tables

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 i...
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi DoesNotCompute 

    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.  

  • DoesNotCompute's avatar
    DoesNotCompute
    Regular Visitor
    2 years ago

    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

    1. Los Angeles should be removed from the table entirely, because it has a Shelf fixture.
    2. Retailer B Total should be 300 instead of 500 (because it should not be capturing Los Angeles).

     

    I'm sure there is something I am not understanding about filter context here, but I cannot figure it out.