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 annual dashboards. The data structure was quite simple, basically one file per year with columns for the different indicators and rows for the participating schools (approx. 40,000). We have now dashboards for about 8 years
Now there is a request for a new dashboard in which we want to monitor progress of a set of indicators over time. My idea is to generate a new set of tables, one per indicator with columns for the different years and rows for the schools. The schools all have a unique school-id so linking or joining the data should be no problem.
I have an issue however, the number of participating schools is not fixed and is growing over time, more schools are participating and new schools are being built. This means that we went from about 37,000 schools 8 years ago to about 50,000 now. To me it seems that I can not just link the data together because of this. If I take the first year as basis, then I can link 37,000 schools based on their ID. If the next year has 40,000 schools participating, there are 3,000 schools I can not join (40,000-37,000) and etc. for all the following years.
Does any of you have a suggestion on how to solve this and if possible in structured way because it may become a recurring activity for me
in advance
Ubo
6 Replies
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" )Hi pakesubo,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to johnt75, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member johnt75, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Hi pakesubo,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.
The issue is the grain drifting each year, not the school count.
A single tall fact (SchoolID, Date, MetricName, MetricValue) usually settles it — just unpivot in PQ, set types, load.
If it still feels odd, add a small Date dimension. Once the grain is stable, the model handles new schools naturally.
