Show dates with no data in a Visual

[lordmukund]

Hi,

I am pulling bugs data from Azure DevOps and creating a Bar Chart to show how many Bugs are created each day. PFB snip

But here I am not able to show the dates (Days) when there are no bugs. They are just excluded from the visual.

I tried "Show Items with no data" option ; I tried using DAX measure for values as: IF(ISBLANK(Count(Bugs)), 0 , Count(Bugs)) as well but no luck.

All the data is there in the single table itself. I am using Calculated Column Day in Y-Axis of this visual for showing days. 

Day = 'Bugs DevOps'[Created Date].[Day] & " " & LEFT('Bugs DevOps'[Created Date].[Month],3)
[Created Date] is the default date column in the table.

When there is no bug created for a particular workstream on a particular date then that date (Day) is not showing up in the visual. (3 Feb, 6 Feb, 7 Feb are not showing up on the Y-axis below as there are no Bugs created for that particular workstream on those days)
I want all the days to be displayed on the Y-axis and show a 0 (Blank will also do, but Y-axis should have all Days displayed) against it when there are no bugs for a given workstream on that Day. Please help me on this

P.S. The below result is same even if I just drag the [Work Items] into Values field. This below measure isn't creating any difference.

[amitchandak · Accepted solution]

@lordmukund , +0 will work with date table, Please create a date table and join with that and use that on x-axis

Then Measure +0 will work

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[v-alq-msft · Accepted solution]

Hi, @lordmukund 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is a relationship between two tables. You may create a measure as below.

Result = DISTINCTCOUNT('Table'[Work Items])

Then you need to use 'Date' column from 'Calendar' table and check 'show item with no data'.

Best Regards

Allan

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

[amitchandak · Accepted solution]

@lordmukund , +0 will work with date table, Please create a date table and join with that and use that on x-axis

Then Measure +0 will work

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.