Latest Enterprise DNA Initiatives

Showing "0" when other columns have figure

DAX Calculations
#1

Hi All

I have a table with 1,000+ lines, where some months an individual line may have no figure for CY, BUD & LY (in this case I have written the below DAX to “hide” the row);
RETURN
SWITCH (
TRUE (),
Actuals && PeriodTableMTD, if([CY MTD.] = 0, BLANK(),[CY MTD.]). etc.

But this then looks strange if there is data in one of the columns (i.e. bud and blanks in the other), as seen below:

image
image944×222 13.3 KB

Is there a DAX whereby if there is a number in one of the columns, it shows as “0” in the other columns? While still making it blank (and hiding the entire row is all are blank/0?

Thanks

Stuart

#2

@Stuart, the easiest solution I have found for this is to create a simple measure for the table where I need to show the zeros.

BUD (w/ zero) =
IF( [BUD] = BLANK(), 0, [BUD] )

then I just change the name in the table so that to the end-user it looks like the same measure is used in both places.

The other solution (which I don’t like as much) is to replace the blank logic in your original measure with a zero, and then in the table where you don’t want the former blanks to show, add a filter to the table to hide the zero results of the BUD measure.

1 Like
#3

@Stuart Measure’s Code + 0

#4

AFAIK no … rows with all blanks will be hidden by default, but 0 is a value, so you’ll need to define your own rule (something like SUM all values), then you can use the filter pane to filter out those rows with SUM = 0 from that visual.
Greg

#5

Hi @Stuart, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

#6

Hi @Stuart, we’ve noticed that no response has been received from you since the 31st of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

#7

Hi @Stuart, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!