ALLEXCEPT Overwrites Relationships

[avtle]

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 Fiscal Years.

Sample database link

Issue: I'm trying to calcuate a yearly total that is dynamic between Calendar and Fiscal Years. My initial formula for this is:

Values Sum: Yearly = CALCULATE([Values Sum], ALLEXCEPT('Year Selection', 'Year Selection'[Year], 'Year Selection'[Time Period]))
The issue arises when attempting to view this measure using fields from the Month table (such as quarter). When the above measure is added to a visual, all filtering from the Year Selection table is ignored. This results in the value being repeated for each line as if there is not a relationship between Year Selection and Month. 

Is there a way to calculate a Yearly total in this situation while still maintaining the relationship between tables?

Sample database link

[mahoneypat]

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]

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 · Accepted solution]

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]

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

[Anonymous]

Hi @avtle

Please check as per your requirement

https://www.youtube.com/watch?v=r1AbCsKwzzs

[avtle]

Hi @Anonymous,

Thanks for your response. The video does not seem to address the concern I have here. I tried both the ALLSELECTED and SUMX solutions he presented.

Baseline, I am looking to trim this chart so that the axis is still filtered by the axis when the yearly total is added.

Chart without yearly total (filters working properly):

Chart with yearly total (filters not working properly):