cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcastr02
Post Prodigy
Post Prodigy

show blank if two values are 0

I have a matrix visual showing # of surveys taken and the NPS Score - both the # of surveys and the NPS Score are Measures I have created in my table.  How can I have the values for the where the NPS AND the # Surveys if they both show 0, then leave those values blank.    

 

As example for May2021 should show as follows:

 

Survey Type     NPS    # of Survey

30 Day              17              12

60 Day

90 Day

180 Day

1 ACCEPTED SOLUTION

returning 0 for both see below.   While 0 could potentially be an NPS Score, so technically if we can just have it show blank for BOTH # of Surveys and NPS when the survey volume is 0.

 

Screenshot 2021-11-05 144628.png

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

Assuming your measure name is [NPS] and is some calculation like so.

NPS = SUM ( Table[Column] )

You can trick PowerBI into blanking it out like this.

NPS = 
VAR _Value = SUM ( Table[Column] )
RETURN DIVIDE (_Value,_Value) * _Value

Because the DIVIDE returns a blank if it tries to divide by 0 and BLANK() * 0 = BLANK() it should work.

jdbuchanan71
Super User
Super User

Is it hiding those rows instead or is your measure returning 0 for both?  What is the result you are getting now?

returning 0 for both see below.   While 0 could potentially be an NPS Score, so technically if we can just have it show blank for BOTH # of Surveys and NPS when the survey volume is 0.

 

Screenshot 2021-11-05 144628.png

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors