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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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?

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.


@ 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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.