Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RogueTrooper
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]))
 
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?
 
 
1 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" )
    )

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

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

View solution in original post

4 REPLIES 4
RogueTrooper
Regular Visitor

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

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

LinkedIn | Twitter | Blog

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

Spot on.

 

Thanks!

bcdobbs
Super User
Super User

I think you're just missing a bracket.

 

TOTALMTD(

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



Ben Dobbs

LinkedIn | Twitter | Blog

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors