7 Replies Latest reply on Mar 18, 2015 10:50 AM by Matt Lutton

    null if zero


      Is there a function that sets zero values to null?  Basically, the opposite of ZN?  My end game is to set a user-defined reference line on a line graph that disappears completely when set to 0. 


      I am close to what I want using parameters, but when the user enters 0 then the y-axis drops to 0 as well, which the customer doesn't want.  Unfortunately, setting fixed values for the y-axis is also not an option as the metric has a lot of variance depending on selected filters.


      I have tried setting the reference line to be based on a calculated field that reads from the parameter, but it's still setting the value at 0 instead of null:   iif([Goal]=0,Null,[Goal])

        • 1. Re: null if zero
          Matt Lutton

          Did you base the reference line on your new value, or the original Goal value?  I ask because I tried to replicate what you have described, and I initially used the original measure instead of the newly created calculated field; once I noticed my mistake, I was able to achieve what I think you're after:


          Null Ref Line Disappears.png

          Notice that the 0 values have no reference line.


          8.2 sample TWBX using Superstore Sales and the [Discount] measure attached.  Let me know if this helps.



          1 of 1 people found this helpful
          • 2. Re: null if zero

            I'm not basing the reference line on the metric, but rather the user input in the parameter.  I've mocked up an example.  End game is either to allow NULL as a value in the parameter, or logic to set the value to NULL when the user enters 0.



            • 3. Re: null if zero
              Matt Lutton

              Right, but any Parameter is going to be connected to a calculated field of some kind -- you'd just use that same logic, I think.  I'll try and take a look shortly.

              • 4. Re: null if zero
                Matt Lutton

                So, if the user enters 0, what would you like to happen?  As it sits, there is a reference line but it pretty much disappears (since its across the axis at the 0 value).  I just want to make sure I understand -- would you just want the axis to automatically resize, so it starts closer to the nearest non-zero value?



                • 5. Re: null if zero
                  Matt Lutton

                  Using the same logic as the example workbook above, I used your "Null if Zero" measure (which leverages the parameter input)




                  Placed that measure on the Detail shelf as SUM(Null if Zero), and when I use that to guide the reference line based on the parameter input, I believe I get your desired result -- the axis starts closer to the lowest non-zero value:




                  Where I was making a mistake is in basing my first attempt on the [Goal] parameter, rather than the "Null if Zero" calculated field that leverages that parameter value -- when we base the reference line on the calculated field, I believe we get what you are after:

                  Ref Line settings.png

                  I've reattached the 8.2 TWBX in case it helps.

                  1 of 1 people found this helpful
                  • 6. Re: null if zero

                    This is exactly the function I was trying to create.  Thank you!

                    • 7. Re: null if zero
                      Matt Lutton

                      You're welcome!