Add missing year(categorical column) and assign zero value to count for each medicine category

[Anonymous]

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!

[bcdobbs · Accepted solution]

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] ) + 0

The +0 stops it returning blank.

You can then use them in a table visual with Medicine and Year coming from your new dimension tables:

[Anonymous]

Awesome! You're the best!