Forum Discussion
ISINSCOPE query
// Assuming that // 1. There is a Building dimension that stores data about buildings. // 2. There is a Room dimension that stores data about rooms. // 3. There is a proper Calendar in the model. // 3. There is a fact table Bookable Hours that for each day stores the bookable // hours for the relevant combinations of buildings and rooms // for each and every day... // you can write this measure and it'll work correctly without // any ISINSCOPE. // Bear in mind that the filtering from dimensions to the fact table // is one-way. [Bookable Hours] = CALCULATE( SUM( 'Bookable Hours'[Hours] ), LASTDATE( Calendar[Day] ) )Best
D
Hi Anonymous ,
Thankyou for taking the time to reply, its much appreciated.
I guess below is where Ive been struggling with. Could you advise on a way this should be layed out?
Each day, building and room with hours for the day as columns?
3. There is a fact table Bookable Hours that for each day stores the bookable // hours for the relevant combinations of buildings and rooms // for each and every day...
Thanks
Liam
Yes. A fact table models the process you want to report on. In your case it'll look like this:
BuildingID | RoomID | Date | BookableHours | <Other columns as needed>
If you have any other layout, you should change it to this one. Achieving the above is simple with Power Query.
Best
D
Anonymous
Okay thanks for confirming. In terms of getting the Building ID and Room ID over to its allocated Buidling, is there a smoother way to do this in power query than manually matching, or would this come out of the dim?
Thanks
- I don't understand the question. You should know which room belongs to which building and store the mappings somewhere.
I assume that a building has some rooms. You can either have a single dimension called Building where you'll store BuildingID, its name (should be unique), and rooms (with their unique ID's across the whole system) belonging to the building or you can do what I've done where there are 2 separate dimensions - Building and Room (and they join directly to the fact table). It's your choice. However, my code has been created with the latter in mind, so if you want to have one dimension storing both entities (which is also feasible and there's nothing wrong with it), you'll need to make slight changes to the measure to account for this.
Moreover, you could create a star-schema with 1 dimension Room and then a snowflake that joins to it called Building (think: product -> subcategory -> category) or you could have 1 dimension that holds both rooms and buildings as outlined above or you could have a pure star schema with separate dimensions the way I did it. I prefer star schemas as they are the most efficient and the easiest way to model data for reporting through this BI tool. DAX is also simple if you have a star schema.
Again, you should have a mapping of rooms to buildings somewhere... I don't understand why you'd need to create this manually. Unless you don't have the mappings at all and then you'll need to create this manually first, of course.
Best
DIts all coming from a select statement with joins where all of these relations exist - but in SQL.
I was hoping there would be a way to calculate bookable hours and hours booked on a matrix without having to create all of my dimensions in Power BI, as these are the only calculations. My main issue is having bookable hours add up for the room to the building total.
If it cant be done it cant be done, and I will aim to recreate with new dims.
Thanks again for your help.
Liam