cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndreaCami
New Member

Failed to make the formula CALCULATE work

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.  "A function "CALCULATE" has been used in a True/False expression that is used as a table filter expression.  This is not allowed."  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.999999

RETURN

 

// Open tickets within the biweek: Exclude tickets on Backlog, On Hold or Cancelled at the start of the biweek or during the biweek

VAR tbl_open_tickets =

    VAR Base = CALCULATETABLE(Issues,

                    Issues[Created] < EndOfBiweek,

                    COALESCE(Issues[Closed Date], date(2999,12,31)) >=  StartofBiweek)

 

    VAR TBL = SELECTCOLUMNS(Base,

                "Issue", Issues[Key],

                "Created", Issues[Created],

                "Closed", COALESCE(Issues[Closed Date], date(2999, 12, 31)))

        

// issues_with_evaluation: Universe of tickets that ticket owner should provide visibility within the next two weeks

    VAR issues_with_evaluation =

        ADDCOLUMNS(TBL,

                "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

                VAR MaxSequence = CALCULATE(max('History Status'[History Sequence]), 'History Status'[History New Value Start] < StartofBiweek)

                VAR status_at_start_of_biweek =

                                CALCULATE(SELECTEDVALUE('History Status'[History New Value]),

                                            'History Status'[History Sequence] = MaxSequence)

                RETURN

                NOT(status_at_start_of_biweek IN {"Backlog", "On Hold", "Canceled","Completed", "Closed"}))

RETURN

SELECTCOLUMNS(FILTER(issues_with_evaluation, [Include 1] ), "Issues", [Issue])

 

VAR tickets_with_status_change =

             CALCULATETABLE(DISTINCT('History Status'[Key]),

                       'History Status'[History New Value Start] >= StartOfBiweek,

                       'History Status'[History New Value Start] <= EndOfBiweek

                       )

 

VAR tickets_with_comments =

             FILTER(values(Issues[Key]),

             CALCULATE(COUNTROWS(Comments),

               Comments[Comment Created] >= StartofBiweek,

               Comments[Comment Created] <= EndOfBiweek) > 0)

                      

 

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

 

DIVIDE(count_of_tickets_to_include, open_tickets_count))

 

 

6 REPLIES 6
v-jayw-msft
Community Support
Community Support

Hi @AndreaCami ,

 

Please try modifying [tickets_with_comments] as below:

VAR count_comments =
    CALCULATE (
        COUNTROWS ( Comments ),
        Comments[Comment Created] >= StartofBiweek,
        Comments[Comment Created] <= EndOfBiweek
    )
VAR tickets_with_comments =
    CALCULATETABLE ( VALUES ( Issues[Key] ), count_comments > 0 )

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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.

Hi @AndreaCami ,

 

I'd like to suggest you to modify the calculate formula like bleow:

calculate([expression],filter('table',[conditions]))

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
daXtreme
Solution Specialist
Solution Specialist

Hi @AndreaCami 

 

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.

 

Googling for "A function "CALCULATE" has been used in a True/False expression that is used as a table filter expression.  This is not allowed." yields this, for instance:

 

sql server - a function calculate has been used in a true/false expression that is used as a table f...

 

or this

 

DAX error: A function 'XXXX' has been used in a True/False expression that is used as a table filter...

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

daXtreme
Solution Specialist
Solution Specialist

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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors