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
PhoenixBird66
Helper III
Helper III

DAX measure IF a value is not blank

percentage_increase.JPG

I've written a DAX measure to show percentage price increases - see screen shot.

I only want the measure to return a value when "January 2019 Avg Price" and "January 2018 Avg Price" has a value. If either of these columns is blank, I dont want the measure to retrun a value. So in the example attached the cells which have either "infinity" or "-100.000%" would be blank.

PLease could somebody point me in the right direction?

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

You could do it as follows 

 

IF( NOT( ISBLANK( [January 2019 Avg Price] ) ) && NOT( ISBLANK( [January 2018 Avg Price] ) ) , [January 2019 Avg Price] / [January 2018 Avg Price] - 1 )

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

What if you used the DIVIDE function instead?  I think it would solve at least some of those, not sure on all the issues though:

https://docs.microsoft.com/en-us/dax/divide-function-dax


@Anonymous wrote:

What if you used the DIVIDE function instead?  I think it would solve at least some of those, not sure on all the issues though:

https://docs.microsoft.com/en-us/dax/divide-function-dax


DIVIDE() is much safer than using the / operator as it has builtin protection against divide by 0 errors (which I think are producing the "infinity" result). But it will return 0 if the numerator is blank. So I think in this specific situation we need to manually check both the numerator and denominator.

d_gosbell
Super User
Super User

You could do it as follows 

 

IF( NOT( ISBLANK( [January 2019 Avg Price] ) ) && NOT( ISBLANK( [January 2018 Avg Price] ) ) , [January 2019 Avg Price] / [January 2018 Avg Price] - 1 )

Hi Gosbell, Thank you for this.. can you help me with the following:

 

I have the following working perfectly.. 

 

IF(ISBLANK([Date.Actual.Start]),BLANK(),IF(ISBLANK([Date.Actual.Completion]),BLANK(), (DATEDIFF([Date.Actual.Start],[Date.Actual.Completion],DAY)))) 

 
but I want to add to above and combine it with the following, 
IF( NOT( ISBLANK( [Date.Actual.Start] ) ),
IF(ISBLANK([Date.Actual.Completion]),
DATEDIFF([Date.Actual.Start], [If.Today],DAY)))
 
 

This works fine. Thanks so much for your help.

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.