calculate if is empty

[joep78]

Hi,

I'm looking for a solution if column B is empty, do the calculation with column A (at least one contains a value):

amount a	amount b	factor	result
13		1	13
	12	2	24
10	15	1	15

first line: amount b does not containts a value so amount a multiply factor

second line: amount b contains a value so amount b multiply factor

third line: both contains a value, but since amount b containts a value, amount b multiply factor

I had the following solution but outcome is not as expected:

IF(ISBLANK(table[amount b]*table[factor]),

table[amount a]*table[factor]

and the other test was like:

IF(ISBLANK(table[amount b],

table[amount a],)*table[factor]

not sure what to do to fix this. thanks already for your support!

[Greg_Deckler]

Sometimes it is more effective to use [aaccount]="" instead of ISBLANK

Also, I would use SWITCH(TRUE()...) statement instead of nested IF statements.

[Fowmy · Accepted solution]

@joep78 

Add a new column in your table:

Result = COALESCE(DATA[amount b],DATA[amount a]) * DATA[factor]

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[joep78]

Thanks for this solution, added the new column and every line contains a value now, thanks!

[parry2k]

@joep78 try adding new column with this expression:

SWITCH ( TRUE(),
Table[Amount B] <> BLANK() && Table[Amount A] <> BLANK(), Table[Amount B] * Table[Factor],
Table[Amount B] == BLANK(), Table[Amount A] * Table[Factor],
Table[Amount A] == BLANK(), Table[Amount B] * Table[Factor]
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡