Forum Discussion

avtle's avatar
avtle
Frequent Visitor
6 years ago
Solved

ALLEXCEPT Overwrites Relationships

Background: I have three tables: 1) Time Selection 2) Month 3) FactTable. I'm using Time Selection to filter down the Month table so that the user can dynamically filter between Calendar Years and Fi...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi avtle ,

    Allexcept() removes all context filters in the table except filters that are applied to the specified columns, the year should naturally be start from 2017 to 2020 in your visual.

    When you apply the slicer,,for example 2020 the sum will show the corresponed value, other year values are blank and the yearly sum will always show from 2017 to 2020.

    Use if statement is a alternative workaround, but if you think it is inefficient, you can use the visual filter to set the sum value is not blank to get the same result.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

     

avtle's avatar
avtle
Frequent Visitor
6 years ago

Hi mahoneypat, thanks for the response.

 

The Year column is not used, but I would like to use this measure in other scenarios to show the yearly sum.

 

I have created a work around function:

var x = ISBLANK(MIN('Year Selection'[Year Month]))
return
IF(x,BLANK(),CALCULATE([Values Sum], ALLEXCEPT('Year Selection', 'Year Selection'[Year], 'Year Selection'[Time Period])))
 
This seems to eliminate the interaction between tables issue, however it seems inefficient to use an if statment.
 
Am I fundamentaly missing something with ALLEXCEPT?

Chart with new function (this is graphically what I was expecting):
v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
6 years ago

Hi avtle ,

Allexcept() removes all context filters in the table except filters that are applied to the specified columns, the year should naturally be start from 2017 to 2020 in your visual.

When you apply the slicer,,for example 2020 the sum will show the corresponed value, other year values are blank and the yearly sum will always show from 2017 to 2020.

Use if statement is a alternative workaround, but if you think it is inefficient, you can use the visual filter to set the sum value is not blank to get the same result.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.