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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.