Forum Discussion

RogueTrooper's avatar
RogueTrooper
Regular Visitor
4 years ago
Solved

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]))
 
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?
 
 
  • bcdobbs's avatar
    bcdobbs
    4 years ago

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

     

     

4 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    I think you're just missing a bracket.

     

    TOTALMTD(

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

  • RogueTrooper's avatar
    RogueTrooper
    Regular Visitor
    4 years ago

    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's avatar
      bcdobbs
      Community Champion
      4 years ago

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