# Combine Calculate and Switch for multiple criteria or condition

I have a “transaction” table with status, balance and price. I need the dax for for an “if this condition, calculate this, otherwise, calculate this” for each status in the table (an example pbix file is attached)

Conditions are: If the balance is 0 then the total of Status is based on "price’, if the balance is greater than 0 (or my “otherwise”), then the total is based on “balance”

I tried using “switch” but I’m just doing my Dax wrong and can’t figure out the correct way to write it.

StatusA when Bal 0 = CALCULATE( SUM(Transaction[price]), Transaction[status] = “status A”, SWITCH(IF(Transaction[balance]=“0”, sum(Transaction[balance]))))

Any help is appreciated.
Trans DAX help.pbix (39.5 KB)

Assuming this is just a quick sample of your data model. Without any sort of other data (such as date, which I added) the sum of transaction and balance was never zero:

So I added some random dates:

1st things first, create your simple sum of transaction and balance measures:

``````Sum of Balance =
SUM( 'Transaction'[BALANCE])
``````

`Sum Transaction = sum( 'Transaction'[PRICE] )`

Then from there you can write a switch statement with a nested if. I didnt quite understand 100% your logic of

Seems like if balance is zero then that logic would produce a zero? Not really here nor there, since the following will show the flow and how you adapt to your needs.

``````Switch Measure =
SWITCH(
SELECTEDVALUE('Transaction'[STATUS])                                        --What the switch will test for
,"StatusA", IF( [Sum of Balance] = 0,[Sum Transaction],[Sum of Balance])    --When StatusA, it will then look at the sum of balance. If that is zero, then will show Total Transaction else tot Blance
,"StatusB", IF( [Sum of Balance] = 0,[Sum Transaction],100)                 --When StatusB, it will look at the the sum of balance. If zero, then sum of transaction, else 100
,"StatusC", IF( [Sum of Balance] = 0,[Sum Transaction],-100)                --When StatusC, it will look at the the sum of balance. If zero, then sum of transaction, else -100
)
``````

Thank you but actually there are zeros. (did you see the pbix file?) Maybe I didn’t explain well enough.
For example, if looking at Status A for a card visual with the total
If the balance is 0 then the total of Status A is based on "price’, if the balance is greater than 0 (or my “otherwise”), then the total is based on “balance”

In this case, the total for status A = 50
line 1 =10 (based on price because balance is =0)
line 2=20 (based on balance because balance > 0)
line 3 =30 (based on balance because balance > 0)
line 4 =10 (based on price because balance is =0)

Does that make sense? thank you!

If I understand your requirement correctly, I think this measure is what you’re looking for:

Switch Balance Price on Status =

``````    CALCULATE(
SWITCH(
SELECTEDVALUE( 'Transaction'[BALANCE] ),
0, [Total Balance],
[Total Price]
),
VALUES('Transaction'[STATUS] )
)
``````

If I’ve misinterpreted the requirement, please let me know and we can easily tweak the DAX above.

Sorry – Nick and I must’ve been working on this at the same time. Didn’t realize he was already on it until after I posted…

• Brian
1 Like

@BrianJ
THANK YOU! That’s wonderful and so helpful, I am soooo close. I did make a change in my last post, I had them backwards (I confused myself) so had I not done that, i bet that dax would have been perfect.
It is correctly,
If Balance = 0 use price
If Balance >0 use balance

Therefore, in your example, StatusA would be “40”
StatusB = 60
StatusC = 30

I am so new to using switch and I thought simply swapping the key measures (total balance and total price) would work but it didn’t. Suggestions?

Switch Balance Price on Status =
CALCULATE(
SWITCH(
SELECTEDVALUE( ‘Transaction’[BALANCE] ),
0, [Total Price],
[Total Balance]
),
VALUES(‘Transaction’[STATUS] )
)

I think Nick and I might be standing at the intersection, waiting for each other to go. Here’s my approach, given the better understanding of your requirement from my previous post.

``````BalPrice =

IF(
'Transaction'[BALANCE] = 0,
'Transaction'[PRICE],
'Transaction'[BALANCE]
)

Sum by Status =

CALCULATE(
SUMX(
'Transaction',
[BalPrice]
),
VALUES( 'Transaction'[STATUS] )
)
``````

1 Like

@BrianJ Worked beautifully! I will use that switch sometime too so no waste there. Thank you both for your time!