cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Dax Filter

Hi.

I've created a working DAX formula, that I now need to add an additional filter to.

Working:-

ReleasedOrderAmount MTD = CALCULATE(TOTALMTD(SUM(SalesOrder[LineAmountLCY]),SalesOrder[OrderDate]),USERELATIONSHIP(SalesOrder[No],Items[No]),FILTER(SalesOrder,SalesOrder[Status] = "Released")) + TOTALMTD(SUM(SalesOrderArchive[LineAmountLCY]),SalesOrderArchive[DocumentDate],USERELATIONSHIP(SalesOrderArchive[No],Items[No]))

ReleasedOrderAmount MTD = CALCULATE(TOTALMTD(SUM(SalesOrder[LineAmountLCY]),SalesOrder[OrderDate]),USERELATIONSHIP(SalesOrder[No],Items[No]),FILTER(SalesOrder,SalesOrder[Status] = "Released")) + TOTALMTD(SUM(SalesOrderArchive[LineAmountLCY]),SalesOrderArchive[DocumentDate],USERELATIONSHIP(SalesOrderArchive[No],Items[No]),FILTER(SalesOrderArchiveHdr,SalesOrderArchiveHdr[No] >"SO01612"))

I now have an error:
! Too many arguments were passed to the TOTALMTD function.  The maximum argument count for the function is 3

Any thoughts on where I'm going wrong?

1 ACCEPTED SOLUTION
Super User

Sorry I see it now! You can only have one filter directly inside TOTALMTD which is why your first one uses calculate.

Try:

ReleasedOrderAmount MTD =
CALCULATE (
TOTALMTD ( SUM ( SalesOrder[LineAmountLCY] ), SalesOrder[OrderDate] ),
USERELATIONSHIP ( SalesOrder[No], Items[No] ),
FILTER ( SalesOrder, SalesOrder[Status] = "Released" )
)
CALCULATE (
TOTALMTD (
SUM ( SalesOrderArchive[LineAmountLCY] ),
SalesOrderArchive[DocumentDate],
USERELATIONSHIP ( SalesOrderArchive[No], Items[No] )
),
FILTER ( SalesOrderArchiveHdr, SalesOrderArchiveHdr[No] > "SO01612" )
)

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
4 REPLIES 4
Regular Visitor

I don't think its quite right as my error has now changed to the sections highlighted and underlined in red:=

ReleasedOrderAmount MTD = CALCULATE(TOTALMTD(SUM(SalesOrder[LineAmountLCY]),SalesOrder[OrderDate])),USERELATIONSHIP(SalesOrder[No],Items[No]),FILTER(SalesOrder,SalesOrder[Status]="Released")) + TOTALMTD(SUM(SalesOrderArchive[LineAmountLCY]),SalesOrderArchive[DocumentDate],USERELATIONSHIP(SalesOrderArchive[No],Items[No]),FILTER(SalesOrderArchiveHdr,SalesOrderArchiveHdr[No] >"SO01623"))
Super User

Sorry I see it now! You can only have one filter directly inside TOTALMTD which is why your first one uses calculate.

Try:

ReleasedOrderAmount MTD =
CALCULATE (
TOTALMTD ( SUM ( SalesOrder[LineAmountLCY] ), SalesOrder[OrderDate] ),
USERELATIONSHIP ( SalesOrder[No], Items[No] ),
FILTER ( SalesOrder, SalesOrder[Status] = "Released" )
)
CALCULATE (
TOTALMTD (
SUM ( SalesOrderArchive[LineAmountLCY] ),
SalesOrderArchive[DocumentDate],
USERELATIONSHIP ( SalesOrderArchive[No], Items[No] )
),
FILTER ( SalesOrderArchiveHdr, SalesOrderArchiveHdr[No] > "SO01612" )
)

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Regular Visitor

Spot on.

Thanks!

Super User

I think you're just missing a bracket.

TOTALMTD(

SUM(SalesOrder[LineAmountLCY]),SalesOrder[OrderDate] ) ) extra one at the end here.

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors