PowerBI Desktop data modeling

[pakesubo]

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

[Kagiyama_yutaka]

I think the real issue is the grain shifting each year, not the changing school count… so build one tall fact (SchoolID, Date, MetricName, MetricValue) and in PQ just Unpivot → set types → load.
If it still misbehaves, add a small Date dimension and relate it—once the grain is stable, the model handles new schools naturally.

[v-kpoloju-msft]

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.

[pakesubo]

Thanks @johnt75,  for this. Holy week got in the way of work, but I will be looking into this solution soon! 

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

Hi @pakesubo, 

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[johnt75]

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" )