Show number of reports by day with empty values

[Anonymous]

Hey everyone and happy new year,

i want a line chart that shows me the number of reports someone created on a specific day and also shows the days where no report was created. So on the Y-Axe is the number of Reports (0-X) and on the X-Axe the days (1-31). I already got the chart with the values, but it is not possible to set "0" für some days where no reports are created:

As you can see on day 1 two reports were created and on day 4 one report were created. But for day 2 and 3 he should show zero reports where created. In the line chart i selected the date hierarchy and then only show the day on the X-Axe. In the Valuefield is the number of reports.

The values came from two different tables:

Table A:

Table B:

The relationship is on ID and Object ID as 1:* because there are several entries in Table B.

So the result should look like this:

looking forward to hearing from you.

Kind regards

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Based on your description, you just display days in the x-axis, y-axis shows the count of report. Based on your sample, I think you just need the TableB. I use TableB to get the expected result, please follow the steps below.

1. Create 1-31 days in a new table named 'Day'.



2. Create a calculated column to get day in TableB using the formula: Day = DAY(TableB[DateReportcreation])


3. Create relationship between 'Day' and 'TableB' as follows.



4. Create a mreasure to get the count of report, create a line chart, select the Day[Day] as x-axis, the measure as y-axis, you will get expected result.

count of report = COUNT(TableB[Object ID])+0

Best Regards,
Angelia

[Anonymous]

Hi Angelia

thanks for the solution! I already found another visualization. If you choose the bar chart, you do not need another table with the days, you just add the field "DateReportcreation" as "Day" on the axe and count of "DateReportcreation" as value.

Best regards

Marvin