Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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
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
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!
Hello,
You can go for switch case
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |