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