My first post so I hope it’s not a dumb question.
I have a case where I am collecting metrics for a Digital Strategy. I have at the top level a Digital Theme table (DIM) containing the descriptions of the strategy at a high level.
Each Digital Theme has multiple Digital Measures, so I have a Digital Measures table (DIM)… all good so far.
The strategy runs over 5 years, so I created a Years table (DIM) (I don’t need a full Date table).
Now, because the Digital Measures need to be captured every year I need a Results table (FACT). However the results could be in $$, or numbers or percentages, so I couldn’t create a single table with a single results field. So I created a table for each Digital Measure just to capture the results but I’m not sure that this is optimal.
My problem is that I cannot create an active relationship between the Digital Measures and each results table, and also an active relationship between the Years and each results table. I can do it in one instance (shown in the example with Carbon_1) but after the first table that has both relationships active the remainder only have one active.
Without the active relationships I can’t slice by Years and/or by the Digital Measure.
Is there a different way I could structure this dataset to achieve my outcome.
I have attached the pbix file and the xls the data is dummy data.
Digital Strategy Metrics.xlsx (46.5 KB)
Thanks in advance.
Digital Strategy.pbix (250.2 KB)