Dax Filter

[RogueTrooper]

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]))

I've added an additional filter to the end:

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?

[RogueTrooper]

Hi.  Thanks for the reply.

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"))

[bcdobbs · Accepted solution]

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" )
    )

[RogueTrooper]

Spot on.

Thanks!

[bcdobbs]

I think you're just missing a bracket.

TOTALMTD(

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