Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
dvp
Helper I
Helper I

Line chart with customers per day and zeroes values if none + page filters not working

Trying to create a simple line chart with

 

x axis = Customers.RegisteredOnDate

y axis = count of Customers.customerId

Legend = Customers.DistributorId

 

with a simple page filter Dates.MonthAndYear=July 2020, but nothing works as it should.

 

There is a 1:n relationship between Dates.Date -> Customers.RegisteredOnDate.

 

Customers table has structure like this:

CustomerId

RegisteredOnDate

DistributorId

 

Dates table has structure like this:

Date

YearAndMonth

with values inserted for the period 01.01.2019 - 01.01.2029 ...

 

So far I have found the following less than ideal alternatives:

1) X-Axis -> Categorical, no "blanks->zeros conversion", page level filter works, however the lines in the line chart have gaps (as on some days some distributors have blank/zero count of customers)

2) New simple measure CountOfCustomers = COALESCE(COUNTROWS(Customers),0), lines are OK in the line chart (go down to zero, no gaps), however the page filter is not considered and the x-axis contains ALL dates (expectation is the dates should be only from 01-31st July 2020)

 

How come I am not able to do such a simple thing - where is the catch?

1 ACCEPTED SOLUTION
dvp
Helper I
Helper I

Wondering why nobody helped me with this one, and even MS Power BI Support could not give me an answer 👎

 

Thanks to this thread:

 

https://community.powerbi.com/t5/Desktop/Plot-zero-when-there-is-no-data-in-line-chart/m-p/585171#M2...

 

I found that the solution is extremely simple - just create a measure 

 

CountOfCustomersWithZeroes = COUNT(Customers[CustomerId]) + 0
 
and use that in the line chart ...
 

View solution in original post

8 REPLIES 8
dvp
Helper I
Helper I

Wondering why nobody helped me with this one, and even MS Power BI Support could not give me an answer 👎

 

Thanks to this thread:

 

https://community.powerbi.com/t5/Desktop/Plot-zero-when-there-is-no-data-in-line-chart/m-p/585171#M2...

 

I found that the solution is extremely simple - just create a measure 

 

CountOfCustomersWithZeroes = COUNT(Customers[CustomerId]) + 0
 
and use that in the line chart ...
 
amitchandak
Super User
Super User

@dvp ,Can you share sample data and sample output in table format?

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Greg_Deckler
Super User
Super User

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Sure, understood. I will try to prepare a sample pbix and attach it.

dax
Community Support
Community Support

Hi @dvp , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Thank you for your sharing!

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

I have created a sample pbix file: https://github.com/deyanp/power-bi-measure-issues/blob/master/MeasureIssues.pbix

 

Let me know if the problem statement requires additional info.

 

Thanks in advance!

@dax , @amitchandak , @Greg_Deckler , does the sample explain what my issue is? I would appreciate it if you could take a look at it!

@dax , @amitchandak , @Greg_Deckler , would really appreciate if you could take a look at the sample pbix and give me a hint!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.