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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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