Forum Discussion
ISINSCOPE query
Hi, Could somebody please help me in finishing my ISINSCOPE command? I need the building name to sum the measure 'Total Bookable Hours' at the end, but it wont accept sum or sumx for the mea...
// 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
// 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 ,
I have everything set up properly now apart from the Bookable hours fact table which im struggling with.
With the layout that you suggested:
BuildingID | Room ID| Date| bookable hours
Would this require a calender with every day duplicated for every combination of room and building? This is the part I'm confused with.
Thanks
Liam
- Date table duplicated? No. Of course NOT. I'm a professional, not a child in a sandbox giving advice at random.
Please learn how to create a proper date table and how to do time-intel properly:
https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/
You need one calendar connected to the field in your fact.
Best
DHi Anonymous ,
I think you may have misunderstood,
Thank you for your time