cancel
Showing results for
Did you mean:
Highlighted
Helper II

## calculate if is empty

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!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion

@joep78

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 🙂

4 REPLIES 4
Highlighted
Super User IV

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Community Champion

@joep78

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 🙂

Highlighted
Super User IV

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

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper II

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors