Need help with my formula

[RvdHeijden]

Hello,

I need some help with my formula.

Totaal m1 geul = Countrows(filter(adressen;adressen[Project]="A"|| (adressen;adressen[Status]="Closed"))*[Gem.Lengte p/woning])

I need to filter base on 2 criteria (Project=A and Status=Closed) and that result * an average length (messure) 

It now returns an error Operator or expression '( )' is not supported in this context.

[JDLee23]

Hi,

I think you need to use the CALCULATE function (https://msdn.microsoft.com/en-us/library/ee634825.aspx), to allow you to filter as you need.

E.g. Totaal m1 geul = CALCULATE( COUNTROWS ( adressen ) , adressen[Project] = "A" && adressen[Status] = "Closed")) * AVG (length measure)

The '&&' gives you the AND operator (rather than ||, which is an OR operator). The formula above will count the rows in the table 'adressen' which have Project = A, and Status = Closed, multiplied by the average length measure.

Hope this helps!

[RvdHeijden]

@JDLee23

Thank you for your help

however the formula still has an error

Totaal m1 geul = CALCULATE(COUNTROWS(adressen);Adressen[Hoofdproject]="A" && 'Adressen'[Status Civiel]="Gesloten")*[Gem.Lengte p/woning]

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

[JDLee23 · Accepted solution]

Ah, sorry, my bad!

With CALCULATE, rather than using the '&&', simply replace with a comma - e.g. 

CALCULATE (COUNTROWS (adressen), Adressen[Project] = "A", Adressen[Status] = "Closed") * Length

That should work.

[RvdHeijden]

@JDLee23
That did the trick, thank you