Forum Discussion
Adding id and date rows where date is missing to table
Hello,
I two tables
Table1: Contains sales data at a distributor name level
Table two:
Standard calendar table
My issue:
I have a relationship connecting both tables on date logic. However, if you look closely at table one, you'll notice the months of September, October, November, are missing.
The problem is, is when I slice on those months, the data for "amazon" is blank because the relationship doesn't see a year-month for the select date.
Is there a way to incorporate logic in the table that says:
For each distributor:
If distributor missing a year-month:
Add the distributor name, year month, and make sales 0
This would then cure my issue.
Thanks,
Joel
Hi Anonymous
You don't need to add rows for the distributors who do not have all year/month dates in the year. If your purpose is to display blank values for those non-existing year/month in a table visual in the report, you just need to use calendar table's Year-Month column in the visual. A standard calendar table has all dates, so no year/month will be missing.
To have Distributor names for those blank rows, you can add a Dim Distributor table in the model which has all distinct distributor names in a column. Then build a relationship between this Dim table and your Table1 on "Distributor" column. Use Dim Distributor table's Distributor column in the table visual. This will make that column always have a name rather than blank.
Then drag Sales and other columns into the table visual. Select "Show items with no data" option as Amit has suggested. This will show blank values for you but the id and date are valid.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
3 Replies
- amitchandak
Super User
Anonymous , Add +0 to you measure
or use this option -> Right click on the month year in the visualization pane and use Show items with no data
thanks for your help! However, that is not quite what I am looking for.
You see, I have a relationships between my calendar table and the sales table based on the date. Since there is no date for September, October, November, when I select those months in the slicer I get 0 for sales back.
This isn't correct, as I had sales in the months leading up to this.
If possible, I want to iterate through my table in the transform data pane, and say:
For the distributors who do not have all year/month dates in the year, add a row for them with their name, year/month date, and other columns blank.
This would cure my relationship debacle. Not sure how to do this though.
v-jingzhangCommunity Support
Hi Anonymous
You don't need to add rows for the distributors who do not have all year/month dates in the year. If your purpose is to display blank values for those non-existing year/month in a table visual in the report, you just need to use calendar table's Year-Month column in the visual. A standard calendar table has all dates, so no year/month will be missing.
To have Distributor names for those blank rows, you can add a Dim Distributor table in the model which has all distinct distributor names in a column. Then build a relationship between this Dim table and your Table1 on "Distributor" column. Use Dim Distributor table's Distributor column in the table visual. This will make that column always have a name rather than blank.
Then drag Sales and other columns into the table visual. Select "Show items with no data" option as Amit has suggested. This will show blank values for you but the id and date are valid.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
