Making null a zero when using 'Count' of a column

[Anonymous]

Hi All!

What I am trying to achieve as an end goal is a line chart where the nulls are treated as zeros so lines remail consitant.

I unfortunatley can not show the data I am working with, as it is company data.

Overview:

I have a lot of data consititng of the export of a specific mailbox. My main activity is measuring the occurances of specific subject lines in the data.

I have managed to get it showing me the top 10 most received emails by subject, however, I want trend graphs for this data based on count per month of said subjects.

The issue is that if there are no instances of the specific subject it of course is null, and I need it to be zero so line doesent just stop on the line graph, but drops to zero. 

Again apologies for the lack of data, here is an attempt at simulated data.

Month Subject

Jan	Oranges
Jan	Apples
Jan	Oranges
Jan	Apples
Jan	Grape
Jan	Grape
Feb	Oranges
Feb	Apples
Feb	Apples
Feb	Oranges
Feb	Apples
Feb	Oranges
Feb	Grape
Mar	Apples
Mar	Oranges
Mar	Oranges
Mar	Apples
Mar	Oranges

As you can see the 'Grape' subject does not appear in march. This obviously represents a null for 'Grape' in march when using count. What I want is for the lack of said subject to be represented by zero and not null so the line graph doesnt just stop lines 'mid air' 

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Create a Month dimension table which is linked to fact data table based on [Month] field.

Month dimension = VALUES(Table7[Month])

Add [Subject] into chart legend. Add below measure to line chart rather than using Count for Subject column.

Countvalue = COUNT(Table7[Subject])+0

Best regards,

Yuliana Gu

[Greg_Deckler]

In general, you use a line like this in your measure:

Measure =

...


RETURN
IF(ISBLANK(__var),0,__var)

[Anonymous]

Thanks for the input! trouble is I am not using a measure, I am using the count [Column] in the vizulaization editor.

I am happy to write a measure to do this, however, the top subjects will change as more data is added, or as date slicers are applied, so I cant make subject specific measures.

Kind Regards

Matt

[Greg_Deckler]

Can you give me a sense of your data? Just make up data that emulates your real data using "apple", "orange" or "foo" and "bar", etc.

[Anonymous]

Hi!

Thanks for looking at this. I have posted a very small dataset as an exmple in the body of the message. Also a small extra bit to try and help explain. 

Kind Regards

Matt