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:
image

So I added some random dates:
image

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”

image
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!

@britniblock,

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

image

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

Hope this is helpful.

@britniblock, @Nick_M,

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
image

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

@britniblock, @Nick_M,

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

image

1 Like

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