A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expre

Question

I realise that there are a few posts with similar titles but I've looked through all of them I and still don't have my answer!

I am trying to write a new measure which will calculate late returns, i.e.: "calculate sum of full returns where late".

So my DAX formula is:

Late Return = calculate(sum(Compliance[Full return]), [No. of days late] > 1)

where "No. of days late" is already defined as:

No. of days late = if([Deadline] >= today(), blank(), if(isblank(selectedvalue(Compliance[Date Returned])), datediff([Deadline], today(), DAY), datediff([Deadline], selectedvalue(Compliance[Date Returned]), DAY)))

What am I doing wrong?

Please keep your answers simple as I am only seven weeks into my Power BI journey!

mahoneypat · Answer

This is an old post but explains the error and fix.&nbsp;&nbsp;
https://powerpivotpro.com/2012/06/filter-when-why-how-to-use-it/
&nbsp;
You need to use FILTER in your CALCULATE for this more complex evaluation.&nbsp; For example,
Late Return&nbsp;= calculate(sum(Compliance[Full return]), FILTER(VALUES(Compliance[Column), [No. of days late] &gt; 1))
&nbsp;
You need to use VALUES or ALL, or just filter the whole Compliance table, and that measure will be evaluated on each row so that the table can be filtered to &gt;1.
Regards,
Pat
&nbsp;

ajpf · Answer

I think I understand it now.&nbsp; My new formula is:&nbsp;&nbsp;Late Return = calculate(sum(Compliance[Full return]), filter(Metrics, [No. of days late] &gt; 1))&nbsp;but this doesn't work because I still get ALL full returns.&nbsp; The filter element isn't working.&nbsp;&nbsp;Where you have written:&nbsp;&nbsp;Late Return&nbsp;= calculate(sum(Compliance[Full return]), FILTER(VALUES(Compliance[Column], [No. of days late] &gt; 1))&nbsp;what exactly is "Column"?&nbsp;&nbsp;

mahoneypat · Answer

Column is any one of your columns, or you can make a table expression with multiple columns.&nbsp; Whatever the entity is for which the {No of Days Late] measure should be evaluated is what you need to put there.&nbsp; If you explain further and show your example/mock data, a specific expression can be suggested.
Regards,
Pat
&nbsp;

ajpf · Answer

The data itself is simple in structure.&nbsp;&nbsp;For each department, we send an empty report, then we log the date it's returned to us.&nbsp;&nbsp;Department is a single character: A, B, C, D etc.Deadline is a dateFull return is booleanNo. of days late is as already defined and uses only dates, in its calculationLate Return is now defined as:Late Return = calculate(sum(Compliance[Full return]), filter(values(Compliance[Full return]), [No. of days late] &gt;= 1))&nbsp;I want to create a chart showing number of late returns by department, so I have a simple column chart with Axis = Department and Values = Late Return. The chart no longer has an error message, but the values in it are incorrect. Is this because it's somehow summarising the No. of days late?

v-deddai1-msft · Answer

Hello @AJPF , You must use the following measure:  Late Return =
CALCULATE (
    SUM ( Compliance[Full return] ),
    FILTER (
        ALLEXCEPT ( Compliance, Compliance[Department] ),
        [No. of days late] &gt;= 1
    )
)  If this post helps, then consider Accept it as the solution to help other members find it faster. Best regardsDedmon Dai

Forum Discussion

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expre

5 Replies

Related content

The True/False expression does not specify a column. Each True/False expressions used as a table

True/False expression does not specify a column. Each True/False expressions used as a table filter

The True/False expression does not specify a column. Each True/False expressions used as a table fil

True/False, if statement

Multiple True / False filter expression