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.

     

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

The Year column from the Year Selection table is not used in your visual.  Is that correct?  To remove the filters from the Year and Quarter columns used in the visual, you can use this expression.

 

Values Sum: Yearly = CALCULATE([Values Sum], ALL('Month'[Month], 'Month'[Quarter]))
 
If not, please describe your desired functionality/outcome.
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

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):
  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Using an IF is fine if the result is performant for your application.  If you see sluggish response with slicer selections, you can evaluate alternatives.  Now that I see your two charts, I get more of what you are trying to do.  I think it would be simpler to have a Date table that also has a Fiscal Year column in it that you can use in the slicer.  I think this video might be helpful.

    https://www.youtube.com/watch?v=1-agbCF7HwY

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

  • v-yingjl's avatar
    v-yingjl
    Community 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.