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[Orde...
  • 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" )
        )

     

     

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

     

     

    • RogueTrooper's avatar
      RogueTrooper
      Regular Visitor
      4 years ago

      Spot on.

       

      Thanks!