Forum Discussion
Returning zero when date is not in list
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 |
Perhaps a measure like:
TotalBrokenSeats = VAR __seats = SUM('Broken'[BrokenSeats]) RETURN IF(ISBLANK(__seats),0,__seats)
3 Replies
- Greg_Deckler
Community Champion
Perhaps a measure like:
TotalBrokenSeats = VAR __seats = SUM('Broken'[BrokenSeats]) RETURN IF(ISBLANK(__seats),0,__seats) 
v-danhe-msftMicrosoft Employee
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
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?