No Data showing Gap in line chart

[Mike22]

Hello Friends, 

Another problem. I have a series of % data associated with dates. When showing on a line chart it will show gaps where no data is available. 

-X axis is month field from a date formatted column "Submit Date"

-I cannot select continous on the X axis options as it flips back to categorical.

the plotted data is a measure as follows. 

Cumulative % =

var compliant=CALCULATE(DISTINCTCOUNT(Details_All[IDNumber]),Travel_Details_All[Compliant]="YES",FILTER(ALLSELECTED(Details_All),Details_All[Submit Date]<=MAX(Travel_Details_All[Submit Date])),VALUES(Travel_Details_All[Submit Date].[Year]))

return

CALCULATE(DIVIDE(compliant,DISTINCTCOUNT(Details_All[IDNumber])),FILTER(ALLSELECTED(Details_All),Details_All[Submit Date]<=MAX(Details_All[Submit Date])),VALUES(Travel_Details_All[Submit Date].[Year]))

What could be the problem?
Thanks,

Mike

[Clinical_Epi]

Anyone reading this please note the if function doesnt work if you are page filtering the date (say to last 12 calendar months)... which for a rolling 3 year data set to just show a rolling 12 months would be perfect (so you can have pa line for current year and previous year), however when using medians and averages and say a "last day of the week" graph like this below you will get the occasoinal day with no data - and so even though days either side of the last day of the week day showing has data in the average - and that shows in the average pop up table... it wont show the day... and adding an IF clause to add a zero or any number, wont work as will spread the dates out to the full 3 years... 

IF measure for the last person asking:

IF(

[Measure being used orignally]

,0

, [measure being used orignally])

PS: using +0 in or at the end of the measure wont work as will also push all the dates out to the start/end of the date table being used....

#Still a problem Microsoft!!

[v-diye-msft]

Hi @Mike22 

Some possible workarounds:

1. Change the X-Axis type to Categorical instead of Continuous. Dates without data won't show on the chart, but the downside is it's not obvious that any dates are missing, since they're all spaced equally without gaps.

2. Use a column chart instead. The gaps in data will show this way. If you still want a trend line, you can use the "Line and stacked column chart" and put the measure you want as both a line and column. Format the column color (under Data colors > Default Column Color) as something unobtrusive. You'll at least have a visual indication that there is no data if there is no bar, and you can see the line for trending.

3. Set your measure to return 0 for nulls. This will actually plot a 0, which could be really bad depending on your dataset. Don't want to confuse it with real values of 0 as opposed to gaps. But if it makes sense for your dataset, it makes the gap line up with the axis and is almost the same as not being there at all. Maybe even set it to -1 if you could have actual 0 amounts but not negative numbers in your data.

[Clinical_Epi]

The problem with all your solutoins is that the end users want it to look the same throughout - this to me is more of a bug as only breaks once published in the cloud...

The only work around I can find is add a hidden column and make it white, and reduce spacing to zero...however it ruins the look of the gridlines.... now if I could move the grid lines forwards and back.... 😐

Thanks for your responses though - good to see support for this ongoing issue.... a lot of people - myself included were fine with adding +0 or using IF( loops to resolve issues visually, but I've come to the end on this one - I truely believe its a bug...

My reason for the bug is that lets say the line dissappears for the median on weekday (as start date) 05/02/2023, but only when published... on the desktop version it shows the rate for the days before then 01/02/2023/ + 02 + 03 + 04 but, when I publish it, as there wasnt anything on 05/02/023 it decides not to show.... I can demonstrate to my colleagues, but difficult online... and time-consuming... 

I'll see what Microsoft has to say about it..

Regards,

Clin Epi

[Mike22]

dear @v-diye-msft 

Thanks for the reply. 
I would not mind using option 1. The problem is that in this sample the X axis is already set to categorical and keeps leaving the gap. I cannot select continuous as it defaults back to categorical. 

Is there a work around? Thanks!

[v-diye-msft]

Hi @Mike22 

Would you mind sharing your dummy pbix here? that I can check it further.

Please make sure you've removed the confidential info.

[Mike22]

@v-diye-msft  The dataset is very big but I am trying to put it together. I think I understood where the issue might be.

For the specific month where the blanks are there is no data at all in the dataset. So the date doesn't exist at all in the dataset.

I basically would need to tell the measure that it should use the complete calendar and if there is no value for a specific day to use the value of the previous day. This should allow the line to be straight and continuous  until a new value makes it change. 

How can I achieve this measure?

Thanks!

[v-diye-msft]

Hi @Mike22 

If you'd like to keep the line to be straight and continuous even the actual value is null, that means the specific day will take the last valuable value rather than no value. that is different from the actual data, do you mean it? Yes, it can be achieved by measure using IF function. 

[Mike22]

hi @v-diye-msft ,

This is exactly what I want. If there is no data for the day it should show the data from the previous day. Until the value changes of course. 

Can you guide me on how to do it with an IF?
Thanks!!

[ecalzavara]

Hey Mike, I am after exactly the same thing. Did you get an answer on this?

Thanks

[jcarlos]

Good morning.

I don't want to open a new thread.

I am facing the same issue. My chart is showing gaps in the line because my data set doesn't have info for these dates.

How can use the IF function to solve it?