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
RvdHeijden
Post Prodigy
Post Prodigy

Need help with my formula

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.

1 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.

 

 

View solution in original post

4 REPLIES 4
JDLee23
Frequent Visitor

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!

 

@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.

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.

 

 

@JDLee23
That did the trick, thank you

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.