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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
crobinson661
Frequent Visitor

CALCULATE & SUM AND + OR Mutliple Conditions

 

I am very new to DAX and trying to create a Measure using multiple criteria but I cannot seem to get it to work properly:

I am trying to aggregate the SUM of a Budget column IF the following criteria are met: 

State = Ongoing OR Complete

In that set: ONLY WHEN ALL THREE fields of: Spent, processing, AND pre-processing = $0.00

So far I have:

Transaction Submitted =
CALCULATE
(SUM('Transaction List'[Budget]),
'Transaction List'[Spent] = 0.00
&& 'Transaction List'[Processing] = 0.00
&& 'Transaction List'[Pre-Processing] = 0.00
&& 'Transaction List[State] = "Ongoing"
|| 'Transaction List[State] = "Complete")
 
I also tried flipping around the && and || but that was miscalculating as well as the Spent/Processing/Pre-Processing were only being included in the calculation for "Complete" State. 
 
Transaction Submitted =
CALCULATE
(SUM('Transaction List'[Budget]),
'Transaction List[State] = "Ongoing"
|| 'Transaction List[State] = "Complete"
&& 'Transaction List'[Spent] = 0.00
&& 'Transaction List'[Processing] = 0.00
&& 'Transaction List'[Pre-Processing] = 0.00)
 
Any help would be appreciated greatly!
3 ACCEPTED SOLUTIONS
v-cgao-msft
Community Support
Community Support

Hi @crobinson661 ,

Please try this measure:

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    FILTER (
        ALL ( 'Transaction List' ),
        OR (
            'Transaction List'[State] = "Ongoing",
            'Transaction List'[State] = "Complete"
        )
            && ( 'Transaction List'[Spent] = 0
            && 'Transaction List'[Processing] = 0
            && 'Transaction List'[Pre-Processing] = 0 )
    )
)

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

View solution in original post

Hi @crobinson661 ,

Yes. The OR function accepts only two arguments.
OR - DAX Guide

Try this:

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    FILTER (
        ALL ( 'Transaction List' ),
        ( 'Transaction List'[State] = "Ongoing"
            || 'Transaction List'[State] = "Complete"
            || 'Transaction List'[Submitted] = "Yes" )
            && ( 'Transaction List'[Spent] = 0
            && 'Transaction List'[Processing] = 0
            && 'Transaction List'[Pre-Processing] = 0 )
    )
)

Best Regards,
Gao

Community Support Team

View solution in original post

Hi @crobinson661 ,

Please try replacing ALL with ALLSELECTED.

 

Best Regards,
Gao

Community Support Team

 

View solution in original post

7 REPLIES 7
v-cgao-msft
Community Support
Community Support

Hi @crobinson661 ,

Please try this measure:

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    FILTER (
        ALL ( 'Transaction List' ),
        OR (
            'Transaction List'[State] = "Ongoing",
            'Transaction List'[State] = "Complete"
        )
            && ( 'Transaction List'[Spent] = 0
            && 'Transaction List'[Processing] = 0
            && 'Transaction List'[Pre-Processing] = 0 )
    )
)

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

This works great! Thanks so much for your time and assistance!

 

I tried adding an additional condition to that OR but it did not seem to work - it was for a different field (Ex: 'Transaction List'[Submitted] = "Yes" (also tried TRUE as it is True/False, but it kept wanting to input TRUE( ) )

It didn't seem to want to accept it......does OR only evaluate to two conditions?  

 

For example: 

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    FILTER (
        ALL ( 'Transaction List' ),
        OR (
            'Transaction List'[State] = "Ongoing",
            'Transaction List'[State] = "Complete",
	    'Transaction List'[Submitted] = "Yes"
        )
            && ( 'Transaction List'[Spent] = 0
            && 'Transaction List'[Processing] = 0
            && 'Transaction List'[Pre-Processing] = 0 )
    )
)

Hi @crobinson661 ,

Yes. The OR function accepts only two arguments.
OR - DAX Guide

Try this:

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    FILTER (
        ALL ( 'Transaction List' ),
        ( 'Transaction List'[State] = "Ongoing"
            || 'Transaction List'[State] = "Complete"
            || 'Transaction List'[Submitted] = "Yes" )
            && ( 'Transaction List'[Spent] = 0
            && 'Transaction List'[Processing] = 0
            && 'Transaction List'[Pre-Processing] = 0 )
    )
)

Best Regards,
Gao

Community Support Team

Hello - I am hoping you can provide more insight into how to make this measure dynamically adjust to the filters that are selected on the page in other visuals. 

 

Any insight would be appreciated. 

 

Thank you

Awesome! 

 

The only challenge I have remaining is there is a card that displays this value that should filter to a click selection on a bar chart when clicked, but this does not? The other cards that have other Measures do, but they are not as complex. 

 

Also, when I use this calculation in a bar chart that divides the value by a separate category - it just displays the total dollar value for each. (ie: Instead of showing the dollar value of Transaction Submitted for each product, it just shows the total $$ value for all products in along side each individual products)


Is there a way to make this Measure responsive to on report selections on the Page?

 

Thanks again for all your help!

Hi @crobinson661 ,

Please try replacing ALL with ALLSELECTED.

 

Best Regards,
Gao

Community Support Team

 

JadhavVarsha_13
Resolver II
Resolver II

Hello,

 

You can go for switch case

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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