Forum Discussion
How to create missing rows
My data:
| ElementID | Elementname | ElementCategory | Date |
| 12445 | XXXX.docx | Word | 01-01-2020 |
| 53242 | xxxx.pptx | Powerpoint | 01-01-2020 |
| 32455 | xxxxxx.mpg | Video | 01-02-2020 |
| 23423 | xcvsdf | Quiz | 01-02-2020 |
My problem:
I have the following Element categories:
Word
Powerpoint
Video
Quiz
I do a running total of these and this results in a table with a total for each category for each date. However if for example no word file is created on a specific date then its not part of the running total. How do I go about creating empty rows for each category that is not represented in date range. I hope this makes sense 🙂
Any suggestions?
Hi moei
So to achieve this you can create a dimension table for your dates and categories, create a relationship with your current table and use columns from dimensions table in the visuals, and add +0 to your Measure so it looks something like.
Count Rows = COUNTROWS( table ) +0Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
4 Replies
Hi Mariusz,
Sure, I will try to explain it in another way.
Below is a screenshot of what I have before I do the running totals.
The first date "28.september" only has 2 rows because no other categories of files were created that day.
These are alle my categories.
I need every date to have all categories represented. So if no files in the other categories are created the 28.september then they should be represented with a row and a value of 0.
Best Regards,
Morten
Hi moei
So to achieve this you can create a dimension table for your dates and categories, create a relationship with your current table and use columns from dimensions table in the visuals, and add +0 to your Measure so it looks something like.
Count Rows = COUNTROWS( table ) +0Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
