Failed to make the formula CALCULATE work

Question

This formula worked for me in another report with no problems. I am trying to recycle it in another Power Bi and I get the error message.&nbsp; "A function "CALCULATE" has been used in a True/False expression that is used as a table filter expression.&nbsp; This is not allowed."&nbsp;&nbsp;I have tried using FILTERS but I couldn't get it to work either.SLA - Communication =AVERAGEX(VALUES(Dates[Biweek Start]),Var StartOfBiweek = CALCULATE(SELECTEDVALUE(Dates[Biweek Start]))Var EndOfBiweek = StartOfBiweek + 13 + 0.999999RETURN&nbsp;// Open tickets within the biweek: Exclude tickets on Backlog, On Hold or Cancelled at the start of the biweek or during the biweekVAR tbl_open_tickets =&nbsp;&nbsp;&nbsp; VAR Base = CALCULATETABLE(Issues,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Issues[Created] &lt; EndOfBiweek,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COALESCE(Issues[Closed Date], date(2999,12,31)) &gt;=&nbsp; StartofBiweek)&nbsp;&nbsp;&nbsp;&nbsp; VAR TBL = SELECTCOLUMNS(Base,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Issue", Issues[Key],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Created", Issues[Created],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Closed", COALESCE(Issues[Closed Date], date(2999, 12, 31)))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// issues_with_evaluation: Universe of tickets that ticket owner should provide visibility within the next two weeks&nbsp;&nbsp;&nbsp; VAR issues_with_evaluation =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ADDCOLUMNS(TBL,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Include 1", //To capture issues that were not in Backlog, On Hold, Cancelled before the start of the biweek, i.e. they were already on these status at the start of the biweek&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VAR MaxSequence = CALCULATE(max('History Status'[History Sequence]), 'History Status'[History New Value Start] &lt; StartofBiweek)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VAR status_at_start_of_biweek =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(SELECTEDVALUE('History Status'[History New Value]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'History Status'[History Sequence] = MaxSequence)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT(status_at_start_of_biweek IN {"Backlog", "On Hold", "Canceled","Completed", "Closed"}))RETURNSELECTCOLUMNS(FILTER(issues_with_evaluation, [Include 1] ), "Issues", [Issue])&nbsp;VAR tickets_with_status_change =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATETABLE(DISTINCT('History Status'[Key]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'History Status'[History New Value Start] &gt;= StartOfBiweek,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'History Status'[History New Value Start] &lt;= EndOfBiweek&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;VAR tickets_with_comments =&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(values(Issues[Key]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(COUNTROWS(Comments),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Comments[Comment Created] &gt;= StartofBiweek,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Comments[Comment Created] &lt;= EndOfBiweek) &gt; 0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VAR open_tickets_count = COUNTROWS(tbl_open_tickets)VAR Tickets_with_comments_or_change = UNION(tickets_with_status_change, tickets_with_comments)VAR count_of_tickets_to_include = COUNTROWS(INTERSECT(tbl_open_tickets, Tickets_with_comments_or_change))RETURN&nbsp;DIVIDE(count_of_tickets_to_include, open_tickets_count))&nbsp;&nbsp;

daxtreme · Answer

Hi&nbsp;AndreaCami&nbsp;&nbsp;If I were you, I'd first try to debug the formula piece by piece trying to establish which piece/variable is giving you the error. Putting this big formula in here and the text of the error does not really help much. Please try to debug the measure one small piece at a time to see where the offending line is.&nbsp;Googling for "A function "CALCULATE" has been used in a True/False expression that is used as a table filter expression.&nbsp; This is not allowed."&nbsp;yields this, for instance:&nbsp;sql server - a function calculate has been used in a true/false expression that is used as a table filter expression which is not allowed - Stack Overflow&nbsp;or this&nbsp;DAX error: A function 'XXXX' has been used in a True/False expression that is used as a table filter expression. This is not allowed. - SQLGene Training

anonymous · Answer

Hi&nbsp;AndreaCami&nbsp;,
&nbsp;
Please try modifying [tickets_with_comments] as below:
VAR count_comments =
    CALCULATE (
        COUNTROWS ( Comments ),
        Comments[Comment Created] &gt;= StartofBiweek,
        Comments[Comment Created] &lt;= EndOfBiweek
    )
VAR tickets_with_comments =
    CALCULATETABLE ( VALUES ( Issues[Key] ), count_comments &gt; 0 )
&nbsp;
Best Regards,
Jay

andreacami · Answer

Thanks for your support, but it didn't work for me. I keep getting the same message.I tried to find out how to debug the program but it didn't get to it either.

andreacami · Answer

How do I do a piece debug? I have never done it.

daxtreme · Answer

Well, each variable or any piece of code that returns something can be turned into its own measure. By the way, nesting variables is not a good idea. It has its uses but you should avoid it. On YT there are vids where Marco/Alberto debug DAX code.

Forum Discussion

Failed to make the formula CALCULATE work

6 Replies

Related content

If formula

DAX Formula Help

formula

Formula to calculate YoY

Scheduled pipelines failing