Forum Discussion
How to create an input table pivoting field from multiple tables
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
Calendar Table
The tables have these relationships:
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!
It'd be an awful lot easier if you merged your primary and secondary tables together...
5 Replies
It'd be an awful lot easier if you merged your primary and secondary tables together...
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?)
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
Calendar Table
And the tables have these relationships:
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!
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.
Hi ivandelgra
You can have the sample data for all 4 tables in an excel and attach the file here.
Thanks
Raj
