cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ivandelgra Frequent Visitor
Frequent Visitor

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 table.JPGPrimary school TableSecondary table.JPGSecondary school Table

Then I have a table with the Max People Capacity regardless the Type of school

 

 

daily capacity.JPGDaily capacity Table

And at the end I have a Calendar Table

Calendar.JPGCalendar Table

The tables have these relationships:

relationships.JPGRelationships

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:

 

Tot. Availabilty by date.JPG

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jthomson New Contributor
New Contributor

Re: How to create an input table pivoting field from multiple tables

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

View solution in original post

5 REPLIES 5
ivandelgra Frequent Visitor
Frequent Visitor

How to create a measure grouping by a field in another table

Hello this is the situation:

 

I have multiple tables based on Type of school:

 

Primary table.JPGPrimary school TableSecondary table.JPGSecondary school Table

Then I have a table with the Max People Capacity regardless the Type of school

 

 

daily capacity.JPGDaily capacity Table

And at the end I have a Calendar Table

Calendar.JPGCalendar Table

And the tables have these relationships:

relationships.JPGRelationships

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:

 

Tot. Availabilty by date.JPG

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!

 

 

jthomson New Contributor
New Contributor

Re: How to create an input table pivoting field from multiple tables

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

View solution in original post

ivandelgra Frequent Visitor
Frequent Visitor

Re: How to create an input table pivoting field from multiple tables

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

Super User
Super User

Re: How to create a measure grouping by a field in another 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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rajendran Super Contributor
Super Contributor

Re: How to create a measure grouping by a field in another table

Hi @ivandelgra

 

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

 

Thanks

Raj

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,399)