Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Med...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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:

     

     

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

Awesome! You're the best!