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

[dvp]

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?

[dvp · Accepted solution]

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]

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

[Greg_Deckler]

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.

[dvp]

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

[dax]

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.

[dvp]

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!

[dvp]

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

[dvp]

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