topic Dax Filter in DAX Commands and Tips

Dax Filter

RogueTrooper — Sat, 12 Mar 2022 08:57:20 GMT

Hi.

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

Working:-

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?

Re: Dax Filter

bcdobbs — Sat, 12 Mar 2022 13:44:25 GMT

I think you're just missing a bracket.

TOTALMTD(

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

Re: Dax Filter

RogueTrooper — Sat, 12 Mar 2022 14:01:45 GMT

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

Re: Dax Filter

bcdobbs — Sat, 12 Mar 2022 14:23:59 GMT

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

Re: Dax Filter

RogueTrooper — Sat, 12 Mar 2022 18:34:56 GMT

Spot on.

Thanks!