Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joep78
Helper III
Helper III

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 aamount bfactorresult
13 113
 12224
1015115

 

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
Fowmy
Super User
Super User

@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
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Fowmy
Super User
Super User

@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
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.