cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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!

1 ACCEPTED SOLUTION
Super User

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:

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
2 REPLIES 2
Super User

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:

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Anonymous
Not applicable

Awesome! You're the best!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.