Forum Discussion
Add missing year(categorical column) and assign zero value to count for each medicine category
My sample dataset as follows:
| Year | Medicine | Count |
| 2018 | M1 | 1 |
| 2019 | M1 | 1 |
| 2020 | M1 | 1 |
| 2018 | M2 | 1 |
| 2019 | M2 | 1 |
| 2018 | M3 | 1 |
I need output as follows:
| Year | Medicine | Count |
| 2018 | M1 | 1 |
| 2019 | M1 | 1 |
| 2020 | M1 | 1 |
| 2018 | M2 | 1 |
| 2019 | M2 | 1 |
| 2020 | M2 | 0 |
| 2018 | M3 | 1 |
| 2019 | M3 | 0 |
| 2020 | M3 | 0 |
I want to have all the values in the Year column for each medicine and assign value 0 to the counts for missing year. The year column will have more years moving forward - starting from 2018 and then every year, a new year category will be added. For example: It's 2022 now, so years in my dataset are from 2018 till 2022, but will have 2023, 2024.... every year.
Any help would be highly appreciated! Thanks!
Hi,
I imported your data and called it Table1 (normally give it a descriptive name!).
I then created two calculated tables:Medicine = DISTINCT ( Table1[Medicine] )Year = DISTINCT ( Table1[Year] )Then created relationships:
Then in Table1 added a measure:
# Medicines = SUM ( Table1[Count] ) + 0The +0 stops it returning blank.
You can then use them in a table visual with Medicine and Year coming from your new dimension tables:
2 Replies
- bcdobbs
Community Champion
Hi,
I imported your data and called it Table1 (normally give it a descriptive name!).
I then created two calculated tables:Medicine = DISTINCT ( Table1[Medicine] )Year = DISTINCT ( Table1[Year] )Then created relationships:
Then in Table1 added a measure:
# Medicines = SUM ( Table1[Count] ) + 0The +0 stops it returning blank.
You can then use them in a table visual with Medicine and Year coming from your new dimension tables: