Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 Sage
Solution Sage

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors