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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.