I am sharing my Pbix file here.
I tried with suggestions from several posts, but I am still not getting this issue resolved.
https://community.powerbi.com/t5/Desktop/Show-zero-when-there-is-no-data-in-line-chart/td-p/401921
Basically, I am trying to display zero (goes down to zero level) when count of client is zero / if there is no value.
During the month of June and July 2021, for these criteria (M*H*C* and Private), there is no data (blank value).
Bottom is screenshot of what we have now:
Bottom is expected result:
I tried with following measures (to have a line graph goes down to Zero), but nothing seems to work:
ClientD_M = COUNT('vw_census_trend'[ClientID]) + 0
ClientID_M1 = CALCULATE(count(vw_census_trend[ClientID]), vw_census_trend[ClientID] > 0)
ClientD_M2 = COUNTX('vw_census_trend','vw_census_trend'[ClientID]) + 0
ClientD_M3 = IF( ISBLANK (COUNT(vw_census_trend[ClientID])), 0, COUNT(vw_census_trend[ClientID] )) + 0
What would be the solution?
Thanks.
1 ACCEPTED SOLUTION
@JustinDoh1 What date are you using on the chart? You need to use the DimDate date, and then just do
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Zero Measure = [Measure] + 0
Thank you so much for your help.
I think the major portion of issue was resolved, but line graph still does not go down to zero.
So, I am thinking it might be an issue with a Measure.
I have a question. How do we write measure like ( Zero Measure = [Measure] + 0 ) from what I have tried?
ClientD_M = COUNT('vw_census_trend'[ClientID]) + 0
ClientID_M1 = CALCULATE(count(vw_census_trend[ClientID]), vw_census_trend[ClientID] > 0)
ClientD_M2 = COUNTX('vw_census_trend','vw_census_trend'[ClientID]) + 0
ClientD_M3 = IF( ISBLANK (COUNT(vw_census_trend[ClientID])), 0, COUNT(vw_census_trend[ClientID] )) + 0
BTW, I have an updated Pbix file here that has a relationship with DimDate table.
I was using bottom measure:
ClientD_M = COUNT('vw_census_trend'[ClientID]) + 0
And, I was expecting to see a line graph drops to zero from June to July 2021 on these conditions, but it only displays line graph that has a value.
@JustinDoh1 See attached upated file.
You got halfway there with the DimDate table but still need to:
* Mark it as Date table
* Use it in the visuals and slicer
I have made both changes in the attached file and I think it's behaving as expected now.
Since there are no entries for June, July you'll never see those on the linechart if you use the Report Date, that's why a Date table is so important. Does that make sense hopefully?
Cheers!
@JustinDoh1 What date are you using on the chart? You need to use the DimDate date, and then just do
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Zero Measure = [Measure] + 0
