Measure with CALCULATE stops working with filters?

[donaldo]

Hi all,

I have a measure that current works fine that looks like this:

Savings Constrained / Unconstrained toggle = 
SWITCH (
    SELECTEDVALUE ( 'Confidence Toggle'[Constrained / Unconstrained] );
    "Constrained"; CALCULATE ( SUM ( Idea[Constrained Savings] ) );
    CALCULATE ( SUM ( Idea[Unconstrained Savings] ) )
)

Now I thought I could just copy it, use the Calculate filter to restrict the savings what falls in the current year (and make another one for next year) but this doesnt work. Date Dimensions below is my date table. 

Savings This Year Constrained / Unconstrained = 
SWITCH (
    SELECTEDVALUE ( 'Confidence Toggle'[Constrained / Unconstrained] );
    "Constrained"; CALCULATE ( SUM ( Idea[Constrained Savings] );'Date Dimensions'[Year] = YEAR(TODAY());
    CALCULATE ( SUM ( Idea[Unconstrained Savings] );'Date Dimensions'[Year] = YEAR(TODAY())))
)

I get an error called "A function "Calculate" has been used in a True/false expression that is used as a table filter expression. This is now allowed".

I think I understand that the Calculate filter is looking for something more specific, but I would like it to be dynamic. I've tried adding a FILTER(), but either that doesn't work or I messed up it's syntax.

Can anyone shed some light on what it would take for this to work?

[AlB]

@donaldo

Ok, I think you're missing a closing parenthesis at the end of your first CALCULATE that's confusing the engine. Try this (I painted the culprit BIG)

Savings This Year Constrained / Unconstrained =
SWITCH (
    SELECTEDVALUE ( 'Confidence Toggle'[Constrained / Unconstrained] );
    "Constrained"; CALCULATE (
        SUM ( Idea[Constrained Savings] );
        'Date Dimensions'[Year] = YEAR ( TODAY () )
    );
    CALCULATE (
        SUM ( Idea[Unconstrained Savings] );
        'Date Dimensions'[Year] = YEAR ( TODAY () )
    )
)

Code formatted with  

[donaldo]

Hi @AlB

Thanks - you are sharp! It definitely did something, now it "works" but oddly enough does not give the right number. Weirdly enough it seems to be NOT filtering on dates at all. I'm getting the total number across any and all periods. But no errors...

[AlB · Accepted solution]

@donaldo

It's difficult to see what's going on without info on the data model. 

If it's not filtering it must be an issue with the relationships. You are applying a filter on the 'Date Dimenstions' table. Will that propagate to the 'Idea' table, which is where you're doing the SUM( ) ??? Is there a proper relationship in place to allow that?

[donaldo]

I had to flip one relationship to both ways and now it's working. 

I apologize for not being able to share the .pbix, I can barely keep track with it available, so I really appreciate your imagination in that regard!

Is there any caution against having relationships set to both ways? I can imagine with a dedicated and cleaner datamodel you'd ideally want your dimensions to filter on your facts only, but the data I'm working with was not originally inteded for this usage.

[AlB]

@donaldo

Bidirectional relationships are quite dangerous unless you know very well what you're doing...

Have a look at this article by the Italian gurus

[Anonymous]

Have you tried putting 2019 (or 2018) on the Year(today) spot? I know you want it to be dynamic, but i'm curious if you'll the same error. If so, its more likely to be a syntaxt problem.

[donaldo]

 Hi @Anonymous

I just tried, gives the same result. 

[donaldo]

I have tried a solution (well, 2 in 1, VAR and FILTER) to remedy the error to no avail.

Savings This Year Constrained / Unconstrained = 
VAR CurrentYearX = YEAR(TODAY())
RETURN
SWITCH (
    SELECTEDVALUE ( 'Confidence Toggle'[Constrained / Unconstrained] );
    "Constrained"; CALCULATE ( SUM ( Idea[Constrained Savings] );FILTER('Date Dimensions'; [Year] = CurrentYearX);
    CALCULATE ( SUM ( Idea[Unconstrained Savings] );FILTER('Date Dimensions'; [Year] = CurrentYearX)
)))

This gives the same error.

[AlB]

Hi @donaldo

Can you share the pbix?

[donaldo]

Hi again @AlB

I am not sure of the confidentiality of the data to be honest and it's quite large.

Is there anything I can provide that would help?