cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fric368829
Advocate II
Advocate II

Plot zero when there is no data in line chart solution affects the date filter

Hi,

 

I have almost the same issue as descibed here:  

https://community.powerbi.com/t5/Desktop/Plot-zero-when-there-is-no-data-in-line-chart/td-p/506235

https://community.powerbi.com/t5/Desktop/Show-zero-when-there-is-no-data-in-line-chart/td-p/401921

 

I solved it with a measure:    Count of CaseNumbers = COUNTX('Cases','Cases'[CaseNumber])+0

 

But then a new issue started: the axis of the Line Chart visualisation was fixed and could not move  if a date-filter was applied. See  screenshots

wrong filtering of the x-axis as it is not adjusting the rangewrong filtering of the x-axis as it is not adjusting the rangecorrect range of x-axis (but null value issue exist in this picture)correct range of x-axis (but null value issue exist in this picture)

 

In the first picture the date filter is only removing the data and set the rest to 0

in the second picture the date filter indeeds adjust the date range. This is how I want it. (But in this picture the issue of 0 values is not solved.

 

I tried also to play around with filters, but that was just to have no ALLFILTERS not affect the visual. I could not find the other way around, such as e.g. NO-FILTERS. I tried measure Count of CaseNumbers2 = CALCULATE(COUNTX('Cases','Cases'[CaseNumber])+0)

 

Please feel free to share what I'm overlooking here. If feels like it staring in my face... 😉

 

Thanks in advance!

 

 

 

 

1 ACCEPTED SOLUTION

Hi, @Fric368829 

 

Your data is not continuous, and the date column has a specific time.

So you need to create a column and an additional date table,then use that date in slicer.

Like this:

Column = DATE(YEAR([CreatedDate]),MONTH([CreatedDate]),DAY([CreatedDate]))
Table = CALENDAR(MIN(testdataPowerBIcsv[CreatedDate]),MAX(testdataPowerBIcsv[CreatedDate]))

9.png

8.png

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-janeyg-msft
Community Support
Community Support

Hi, @Fric368829 

 

It’s my pleasure to answer for you.

According to your description,If the first picture just showed the wrong date range,you can set the start and end time period in the x-axis of the line chart.

Like this:

5.png

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Janey Guo,

 

I checked my settings, but they are exactly the same as yours. (Although thanks for meantioning. Indeed something I could have overlooked) 

 

Note that all my data is just in one table. So it should be something easy.

Also all my filters on the y-axis are working, only the CreatedDate on the x-axis isn't working.

 

Highly appreciate your help,

 

Catherine

Hi, @Fric368829 

 

Try like this:

column:Year Month = format([Date],"mmm yyyy")

measure :=
CALCULATE (
COUNTX ( 'Cases', 'Cases'[CaseNumber] ),
ALLEXCEPT ( 'Cases', 'Cases'[YearMonth] )
)+0

Could you mind providing some sample data ?So that we can help you more clearly.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I tried it, but then I see the sum of all [CaseNumber] for all CreatedDates the same. No filter on the y-axis anymore.

So it is just the opposite of what I want, i.e. a working filter on the x-axis.

 

(I will see if I can generate a sample for you, I encountered some hick-ups to make it)

And hereby the link to the sample file 

Thanks again for your help! 🙂

Hi, @Fric368829 

 

Your data is not continuous, and the date column has a specific time.

So you need to create a column and an additional date table,then use that date in slicer.

Like this:

Column = DATE(YEAR([CreatedDate]),MONTH([CreatedDate]),DAY([CreatedDate]))
Table = CALENDAR(MIN(testdataPowerBIcsv[CreatedDate]),MAX(testdataPowerBIcsv[CreatedDate]))

9.png

8.png

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sorry for the late reply. There were some prio's changing in work, so I had to postpone the work on this.

 

When relooking at the issue again, I used the Zoom sliders:  https://youtu.be/tZ9F19qTDCI

Not sure if that functionality already existed when the issue started. Really happy with the result. Although your solution is probably much more prettier, this is an fast pragmatic solution. Thank  you for your patient in assisting me!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.