Forum Discussion
PowerBI Desktop data modeling
For the past few years, I have been making dashboard to monitor the implementation of Water, Sanitation and Hygiene in schools. These data were collected by schools on an annual basis and I made annu...
I would unpivot the data so that you have columns
- school id
- date stamp
- metric name
- metric value
with one row per school, metric and datestamp combination. Create a separate date table, marked as a date table, linked to the datestamp column, and probably a separate school table, linked to the school ID column.
You can then use the date table to chart change over time, calculate year-on-year change etc, and you could create measures to calculate the value for specific metrics, e.g.
Metric 1 Value =
CALCULATE ( SUM ( 'Table'[Metric Value] ), 'Table'[Metric Name] = "Metric 1" )