How to create an input table pivoting field from multiple tables

[ivandelgra]

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!

[ivandelgra]

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!

[Anonymous]

Hi @ivandelgra

You can have the sample data for all 4 tables in an excel and attach the file here.

Thanks

Raj

[Greg_Deckler]

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. 

[jthomson · Accepted solution]

It'd be an awful lot easier if you merged your primary and secondary tables together...

[ivandelgra]

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