Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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'
Solved! Go to 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
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
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!
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