Returning zero when date is not in list

[Anonymous]

Hi

I have two tables "Broken" and "Calendar". 

I am seeking to create a table like "Outcome" below where every day of the year is listed - if there is an entry for that date in "Broken" it will return the BrokenSeats value.

If not, it should return zero - ie I need to be able to see a datapoint for each day.

I had just merged the tables in query editor joining on the Date and DatesBroken column but that isn't giving me zeros when there is no entry in Broken

I want to create a stacked bar visual with every day of the year as the date axis - there should be nothing showing on the day when no seats were broken - just stacked bars on days when there were broken seats.

Cheers

Broken

Theatre	BrokenSeats	DatesBroken
West	3	1/1/2019
West	3	2/1/2019
West	1	3/1/2019
North	6	4/2/2019
North	6	5/2/2019

Calendar

Date

1/1/2019

2/1/2019

3/1/2019

4/1/2019

outcome

Date	Theatre	TotalBrokenSeats
1/1/2019	West	3
1/1/2019	North	0
2/1/2019	West	3
2/1/2019	North	0
3/1/2019	West	1
3/1/2019	North	0
4/1/2019	West	0
4/1/2019	North	0

[v-danhe-msft]

Hi @Anonymous,

From your data, I could not understand the [Date] column in 'outcome' table, do you mean in the same data, there is a 'West' and a 'North'?  If so, I could not see the related data shown in your 'Broken' table, it seemed the each data just have a 'Theatre', could you please offer me more information about your data structure?

Regards,

Daniel He

[Anonymous]

The "Broken" table only has data on dates when an event (that is when broken seats are observed by staff). Some dates will not appear in the Broken table because there were no broken seats on those days

There are four theatres in total. But they will only appear in "Broken" if and when any broken seats are observed - so South does not yet appear in this year's data set but it could do, if something breaks in it.

But for an outcome, I would like to list every single day in the year and have zero when no seats are broken - that is, when there is no entry at all for those dates in the Broken table.

I need to be able to help users visualise and count the number of days when there were no events ("SeatsBroken") at all.

does that make sense?

[Greg_Deckler · Accepted solution]

Perhaps a measure like:

TotalBrokenSeats = 
VAR __seats = SUM('Broken'[BrokenSeats])
RETURN
IF(ISBLANK(__seats),0,__seats)