cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lordmukund
Microsoft
Microsoft

Show dates with no data in a Visual

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 (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.

 
 

Bugs date.png

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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.

View solution in original post

v-alq-msft
Community Support
Community Support

Hi, @lordmukund 

 

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

Table:

i1.png

 

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'.

i2.png

 

Best Regards

Allan

 

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

 

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
Community Support

Hi, @lordmukund 

 

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

Table:

i1.png

 

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'.

i2.png

 

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
Super User
Super User

@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.

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

Top Solution Authors