Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
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
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
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:
or this
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.
User | Count |
---|---|
47 | |
27 | |
22 | |
17 | |
15 |
User | Count |
---|---|
52 | |
31 | |
18 | |
16 | |
15 |