Show 0 For Blank Cell in Table Visual Based on Condition

[melq64]

Hi,

I have a table visual that shows RAG metrics for each location. It is normal that on any given date, some locations won’t have all metrics reported, which is represented by blank cells.

Is it possible to do the following:

• if a location (“Branch” column) metric is blank but also has a non-blank metric reported, then show 0

e.g. as highlighted in screenshot, the Branch “AOC Fleet” has 3 metrics reported i.e. (Del Resc = 2.1%, Proc Resc = 33.3%, Aged Freight = 0) and 1 metric (Carryover) is blank.

The expected result in this case is that “Carryover” should show 0 instead of blank.

The same logic will apply to the rest of the table.

Hope that makes sense.

Appreciate any help please.
NOC Sample Report - EDNA.pbix (3.3 MB)

[Melissa]

Hi @melq64,

As a best practice please avoid the use of implicit measures and create explicit measures instead for all your calculations. I think a pattern like this would sort it for you. Please note that when you’ve created explicit measures you can use those in the conditional test.

.

Explicit Vs Implicit DAX Measures in Power BI

[Greg]

Hi @melq64

@Melissa’s approach is best … always use explicit measures instead of implicit measures.

As with most things in Power BI, there a many ways to do the same thing. Here’s an easy fix I often use (performance stinks for large datasets, but it gets the job done…); just add a zero to your explicit measures, e.g.,

X Del Resc = 
	SUM( 'Daily Network Status Update'[Delivery Resource] ) 
	+ 0

Then using the explicit measures in your table, you can get something like this:

Hope this helps.
Greg
eDNA Forum - Show Blanks as Zeros.pbix (3.3 MB)

[melq64]

@Melissa @Greg thank you both for your feedback and I appreciate the tip re ‘explicit measures’.

Your suggestions work, however my expected output is slightly different and sorry if I wasn’t clear with my request. So if we take the same example for the first Branch “AOC Fleet”, the expected result for Carryover should be 0 (not 0%).

If there are no entries for a Branch e.g. “Hastings”, then leave blank

So basically, only return “0” (not 0%) where a Branch has a blank AND non-blank but if Branch has all blank values, then leave as blank.

Hope that makes sense.

[Melissa]

Hi @melq64,

You could use the FORMAT function to force a mixed formatting BUT know that this returns a text string and not a number.

.

As a result (text) values will left align in the matrix, you can adjust that with these settings.

I hope this is helpful

[melq64]

@Melissa we’re getting close but sorry that’s still incorrect. I believe you used @Greg pbix file and screenshot which is probably causing the confusion.

Let’s use Greg’s screenshot that includes my original implicit measures to point out the misunderstanding. What Greg has done (and I believe you followed) is replaced existing 0% values with 0. This is incorrect. Where there is 0%, leave it as is.

Let’s look at another example: Branch = “Auckland North”.
Del Resc = 8.9%
Proc Resc = 0.0%
Carryover = 16.9%
Aged Freight = 3

According to my initial logic, nothing changes above as there are values recorded for each metric including “Proc Resc” = 0.0% (this remains as 0.0% because it is not blank)

Another example: Branch = "Invercargill"
Del Resc = blank
Proc Resc = blank
Carryover = 7.4%
Aged Freight = 1

Expected result for “Invercargill” should be:
Del Resc = 0
Proc Resc = 0
Carryover = 7.4%
Aged Freight = 1

Hope that’s clearer now. Please let me know if you have further questions and sorry for the back and forth on this.

[Melissa]

Hi @melq64,

No worries but I think you have all the pieces to get this done. Just make some minor adjustments and pick what you need.
@Greg’s initial solution will always return a 0. My inital solution will conditionally return a 0.
The provided format measure currently performs a logical test for greater than or equal to 0, remove the equal sign, that should get you there…
Just give it a go.

[melq64]

Hi @Melissa ,

Thank you for the encouragement and I did spend more time studying your solution (which makes sense) and I made copies of your logic with different variations in each logic but I couldn’t get it to return expected results.

As highlighted in the screenshots, you will see the different versions of the logic I applied. The logic makes sense but I’m obviously missing something i.e. it is returning the correct %values but it’s still returning blank (instead of “0”) where “_test” >=0 or “_test” <>blank()

Sorry to keep calling you out but if you’re busy, then I’d be happy to call on another expert? Appreciate your time and patience.

X Proc Resc (Format)1 = 
var _val = [X Proc Resc]
var _test = [X Del Resc] + [X Carryover] + [X Aged Freight]
return

SWITCH(TRUE(),
_test >=0 && _val >=0, FORMAT(_val, "##0.0%"),
_test >=0 && _val =BLANK(), FORMAT(_val, "General Number"))

X Proc Resc (Format)2 = 
var _val = [X Proc Resc]
var _test = [X Del Resc] + [X Carryover] + [X Aged Freight]
return

SWITCH(TRUE(),
_test >=0 && _val <> BLANK(), FORMAT(_val, "##0.0%"),
_test <> BLANK() && _val =BLANK(), FORMAT(_val, "General Number"))

X Proc Resc (Format)3 = 
var _val = [X Proc Resc]
var _test = [X Del Resc] + [X Carryover] + [X Aged Freight]
return

SWITCH(TRUE(),
_test >0 && _val <> BLANK(), FORMAT(_val, "##0.0%"),
_test >0 && _val =BLANK(), FORMAT(_val, "0"))

[Melissa]

Hi @melq64,

Think this should do it. Note that I removed all +0’s from the measures in Greg’s file.

I hope this is helpful

[melq64]

@Melissa , that worked perfectly. I’ll need to educate myself on how you integrated the NOT and ISBLANK in the formula. Many thanks for your help and patience, much appreciated

[Melissa]

My pleasure. Wonderful to hear that worked well for you .
All the best.