I encountered a (maybe?) very random issue recently, with a nifty solution that I didn’t know about, so I wanted to share a quick tip.

The issue: I have two metrics, in two separate data sources, and I’d like to blend them so I can sum them. Easy… pretty basic use case, right?

The problem is that one of the metrics is currently zero in the original data source (but I expect it to have a value in the future.) So here’s what I’m working with:

So I take these two metrics, and I blend them. (I ensure that Metric 1, the one with a value, is in fact on the left, since Data Studio blends are a left join.)

And now I pull those same two metrics, but from the blend:

Metric 1 (the one with a value) is fine. Metric 2, on the other hand, is zero in my original data source, but “No data” in the blend.

When I try to create a calculation in the blend, the result is “No data”

GAH! I just want to add 121 + 0! This shouldn’t be complicated… 

(Note that I tried two methods, both Metric1+Metric2, as well as SUM(Metric1)+SUM(Metric2) and neither worked. Basically… the “No data” caused the entire formula to render “No data”)

Voila… Rick Elliott to the rescue, who pointed me to a helpful community post, in which Nimantha provided this nifty solution.

Did you know about this formula? Because I didn’t:

NARY_MAX(Metric 1, 0) + NARY_MAX(Metric 2, 0)

Basically, it returns the max of two arguments. So in my case, it returns the max of either Metric1 or 0 (or Metric2 or 0.) So in the case where Metric2 is “No data”, it’ll return the zero. Now, when I sum those two, it works!

MAGIC!

This is a pretty random tip, but perhaps it will help someone who is desperately googling “Data Studio blend shows No Data instead of zero”  🙂