Forum Discussion
Making null a zero when using 'Count' of a column
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'
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
5 Replies
In general, you use a line like this in your measure:
Measure = ... RETURN IF(ISBLANK(__var),0,__var)
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
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.
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
