Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

CALCULATE() with two ALLEXCEPT() filters hitting two different tables not calculating correctly?

Running into an issue where my CALCULATE() is correct when I only use one ALLEXCEPT(), but when I introduce a second filter using ALLEXCEPT() that hits a different table, it doesn't include it and only uses the first ALLEXCEPT().

All_CY_TotalDollars =
CALCULATE (
[CY_TotalDollars],
ALLEXCEPT ( 'Store List', 'Store List'[State], 'Store List'[County] ),
ALLEXCEPT ( 'Sales Data', 'Sales Data'[Week] )
)
[CY_TotalDollars] is a calculated measure which is just SUM('Sales Data'[CY_Dollars])
I am ultimately trying to calculate dollar share across different attributes and need selected States, Counties, or Weeks to effect the dominator of the dollar share but nothing else.
 
The ALLEXCEPT('Sales Data', 'Sales Data'[Week]) acts as expected, but when I introduced the second ALLEXCEPT() that hits a different table, 'Store List', it treats it as if its not actually there and calculations do not change.
Model: ModelAny ideas?
Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Which column does the relationship rely on?

    How about changing direction of the relationshio from "single" to "both"?

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      The relationship between the table is Store_Number, which is unique in the 'Store List' table.

       

      Changing between single and both does not impact the calculations.