Hello this is the situation:
I have multiple tables based on Type of school:
Primary school TableSecondary school Table
Then I have a table with the Max People Capacity regardless the Type of school
Daily capacity Table
And at the end I have a Calendar Table
The tables have these relationships:
Moreover I have 2 Measures:
Total no. of People
Tot. no of People =
SUM(Primaryschool_Table[No. of People])
+ SUM(Secondaryschool_Table[No. of People])
and the Availabilty Measure (Max daily Capacity-Tot. no of People)
Availability Measure = SUM(DailyCapacity_Table[Daily Max people capacity])-SUM(Primaryschool_Table[No. of People])-SUM(Secondaryschool_Table[No. of People])
I need to create an input table with "Date", "City" and the associate "Availability" as follow:
I need it as an input table in the data model, in order to create additional Measures based on it (e.g. Sum all city with Availabilty > 40).
Perhaps I was verbose, but it was necessary to make the picture clear. I thank you all of us who can help me!!! I'm trying to solve this since 2 weeks!
Go to Solution.
It'd be an awful lot easier if you merged your primary and secondary tables together...
View solution in original post
And the tables have these relationships:
Thank you so much @jthomson.
But I do not know if in the actual situation I can do this.
I actually have:
- millions of records
- 6 types of schools tables
- the columns of the different 'type of school table' are different
- in the primaryschool_table there is a single record for any single building, while in the others tables they are grouped by location as in the example
Of course all of tables have 1 column that contains the No. of people (but with different detail aggregation depending on the table).
Do you think that I should create a single enormous Table? (maybe working with the 'group by' feature in query editor regarding the primary school table?)
First, very good post, you presented out all of the information required. Only complaint is could you post sample data in a format that can be copied and pasted? I'm too lazy to type data by hand.
Proud to be a Datanaut!
You can have the sample data for all 4 tables in an excel and attach the file here.
Kudos to you if you earned one of these! Check your inbox for a notification.
Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.
Find out where you can attend!