Treat a blank result as 0 for yoy calculation

DAX DAX Calculations
1

Hi

After watching Sams Mastering Dax course on Logical Functions If/Switch i tried the If( Is Blank function on my table where i was comparing last years injury counts to this years , the calculation worked where there was no injury date prior to 2019 however where i was wanting to compare 2020 to 2019 data if on a particular 2019 date that was being compared there was no injuries recorded on that date then the calculation was also excluding these ( as it is a blank) but i actually need to include these blanks in the calculation and class the blank as a 0 so it provides a result in the yoy column e.g in the example i need the 5th of Jan Date YOY Difference to show a result as 1.

Any help is appreciated :slight_smile:

image

2

@AoakeP,

I think this is a perfect place to use the new COALESCE() DAX function that was introduced in the March 2020 update.

It would be helpful to have a PBIX file to test this out on, but I think the following expression will replace blanks in your measure with zeros::

COALESCE( [Injuries LY Measure], 0 )

Here’s an excellent summary of how COALESCE works:

Hope this is helpful. If you have any trouble, please just post your PBIX and I’d be happy to work through it with you.

  • Brian

P.S. Just confirmed that works like a charm…
image

1 Like
3

Thanks Brian

I will try it on my data and if i get stuck i’ll come back to you with the pbix file for further help

Cheers

4

Hi Brian

I don’t i have the Coalesce function available yet i need my organisation to apply the March updates, but i have created a pbix file to show the calculation, if you can assist me in applying the coalesce function to this example i will use after i get back into the office tomorrow and have our IT team upgrade my power bi version.

Using Coalesce example.pbix (686.2 KB)

5

@AoakeP,

When you get the March update installed, give this a go:

Injuries LY =

VAR LYValue =
Calculate([Total Injuries],SAMEPERIODLASTYEAR('Date Table'[Date]))

RETURN
COALESCE( LYValue, 0 )

image

Full solution file attached.

6

Thanks Brian i certainly will, i don’t think i would have been able to figure this out quickly, although i understand what the function is designed to do, i do still struggle with how to structure the formulas…i guess it comes with practice?

7

Absolutely – I think that’s the only way to develop true proficiency with DAX. The videos provide a fantastic foundation, but it’s only when you get in and try to do it yourself that it will really “click”. @sam.mckay has some great suggestions in the video below for how to practice using the resources in the eDNA portal:

To these, I would add two additional suggestions:

  1. Consider working through the eDNA Certification.

This is a great structured learning opportunity that will dramatically improve your DAX capabilities. The first of the four modules is relatively straightforward, and they get increasingly difficult through the next three. If you really challenge yourself and watch a video or go to another resource only when you are truly stuck, by the time you complete all four modules you will have mastered a wide range of DAX functions and analytical approaches.

  1. Try Responding to Questions on the eDNA Forum

IMO, this might be the best learning opportunity on the site. Questions range from very basic to incredibly difficult. Pick ones you think you can answer and try to work through a solution. Even if you’re not able to solve it, the process of doing so will make the solutions posted by others much more meaningful. By working through other members’ problems, you will gain exposure to DAX functions, data model structures, Power Query approaches, etc. that you might never see in your own projects. And over time, when you look back at your earlier solutions, you’ll see very clearly how much you’ve improved. Plus, while you’re learning, you’re also helping others. It’s been great to see more and more members diving in recently and doing this on the forum.

I hope this is helpful. Good luck practicing. :grinning:

  • Brian
2 Likes
8

Hi Brian

Firstly the COALESCE function worked so thank you very much for sending through the formula and example pbix file. :slight_smile:

Also i really appreciate you taking the time to reply regarding how i can further my DAX knowledge and understanding.

I have been working my way through the Mastering DAX online course and have decided now that i will stop and try to apply that particular function to my work model after watching each of Sams videos showing a DAX calculation

Doing the eDNA certificate will be my next mission to complete, i’m not sure however that i’m experienced enough to respond to forum questions… just yet :grinning: but i get your point and agree its a great place to learn from others

Thanks again
Paula