Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello this is the situation:
I have multiple tables based on Type of school:
Then I have a table with the Max People Capacity regardless the Type of school
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!
Solved! Go to Solution.
It'd be an awful lot easier if you merged your primary and secondary tables together...
Hello this is the situation:
I have multiple tables based on Type of school:
Then I have a table with the Max People Capacity regardless the Type of school
And at the end I have a Calendar Table
And 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!
Hi @ivandelgra
You can have the sample data for all 4 tables in an excel and attach the file here.
Thanks
Raj
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.
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?)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |