Make empty data points show up as zero

[bigascon]

Hello!

I need to analyze data from a survey where people selected "Agree", "Somewhat Agree", "Somewhat Disagree" and "Disagree" to different questions. Upon building bar charts from this data (using percentages from grand total) I noticed that whenever no one would answer "Disagree" for example, it would not show up in the graph.

I was wondering if there was any way I could have that data point show up as zero on the graph. I attempted to write the following measures to no avail. Please let me know what I am missing!

Measure 1:

CountDisagrees = 
CALCULATE(COUNTROWS(EngagementResults),EngagementResults[Answer] = "Disagree")  

Measure 2:

DisagreeReturn = 
VAR _Answer = SUMX(VALUES(EngagementResults[Answer]),[CountDisagrees])
RETURN IF([CountDisagrees]<=0,1, _Answer)

[mhsk]

Hi!

How would i replace missing data point as in this example with a measure/value?

as suggested:

Measure =
VAR __MyCalc = <some calc>
RETURN
IF(ISBLANK(__MyCalc),0,__MyCalc)

works fine but if i would go for:

Measure =
VAR __MyCalc = <some calc>
VAR _MyOtherCalc = <some siple calc>
RETURN
IF(ISBLANK(__MyCalc),_MyOtherCalc,__MyCalc)

it does not show anything in matrix, how to overcome this?

[v-shex-msft · Accepted solution]

HI @bigascon,

I'd like to suggest you confirm if these empty data did not really exist in your tables first. For this scenario, these empty data are expanded by the current category combo and you can't direct replace these values. 

You need to extract category value as a new table and use this on your visual, then write measure formula to lookup value based on raw table category and new table value, replace the not matched parts with zero records.

Replacing null values in matrix visual 

Regards,

Xiaoxin Sheng

[bigascon]

@v-shex-msft That's exactly what I ended up doing and it worked just fine.

Thanks for the help!

[EmaVasileva]

Hi @bigascon 

Did you try to use the Replace values option in the Query Editor. You can select all empty values to be replaced with "0".

Please let me know if that helps or in case you need a different solution.

[bigascon]

Hello @EmaVasileva,

It won't work in this case since the answers are rows, and if no one answered "Disagree" then this row simply does not exist in the table for a specific question, therefore I wouldn't be able to replace it. What I can try to do is to create a column that counts the answers for each question and then replace those values. Maybe that should work.

[Greg_Deckler]

So if you have a measure, generally:

Measure =
VAR __MyCalc = <some calc>
RETURN
IF(ISBLANK(__MyCalc),0,__MyCalc)

You might also need to check for = ""