Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 measure of bookable hours.

 

The Building needs to sum all of the rooms bookable hours on a top level of a matrix. So 400 instead of 200 in the file

 

PBIX File 

 

Hierarchy = SWITCH(

    TRUE(),

    ISINSCOPE(

    vw1[Room]), [Total Bookable Hours],

    ISINSCOPE(

    vw1[Building Name]), 

))

 

Thanks

 

Liam

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

// 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

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

// 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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
D
Anonymous
Not applicable

Hi @Anonymous ,

 

I think you may have misunderstood,

 

Thank you for your time

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Not applicable

@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

Anonymous
Not applicable

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
D
Anonymous
Not applicable

Its 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

Anonymous
Not applicable

Well, you can have a model in either the import or live mode. If you want the import mode, then you either prepare your dimensions for BI through views or you can extract everything in one big table and then use PQ to create a proper model with dimensions and facts. And trust me: a proper model is EVERYTHING. Bad model = bad calculations and slow DAX. Therefore it does not pay to be lazy. You'll learn this soon enough.

Secondly, if you use a live connection to your SQL server, then just create the right views (or just the correct SQL) and connect to them. But remember one golden rule: NEVER, UNDER ANY CIRCUMSTANCES, EMBED A VIEW WITHIN A VIEW. If you start doing this you'll pay a very high price in terms of speed and resource utilization. Stay away from this practice.

Best
D
Anonymous
Not applicable

By the way... I've had a look at your file. The model is not correct. Please identify the business entities and then create dimensions out of them. Then connect them to a fact table that will store only measures about your process and keys to the dimensions. The fact table's columns must be hidden and only measures can be exposed; all slicing must be performed through dimensions. This is the correct star-schema design. If you do this, your DAX will be simple and fast. Do not create monster models like the one you've got right now because you'll suffer and in no time you'll be creating measures that will not only be inefficient but also wrong without you even noticing it.

 

Here are some materials to show you what a good model looks like:

 

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

 

Best

D

amitchandak
Super User
Super User

@Anonymous , there some missing items you have to fill

Hierarchy = SWITCH(

TRUE(),

ISINSCOPE(

vw1[Room]), [Total Bookable Hours],

ISINSCOPE(

vw1[Building Name]), [?], [?]

)

 

refer:https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Anonymous
Not applicable

Hi @amitchandak ,

 

Thanks for your reply. Yes I'm a bit confused as to what to put where the questions marks are.

 

In all of the examples I have seen with isinscope there has been a sum or divide after the isinscope i.e. 

 

ISINSCOPE(

vw1[Room]), SUM(example),

 

However, mine already has a measure in place of the sum as below.

ISINSCOPE(

vw1[Room]), [Total Bookable Hours],

 

When I copy the measure into the second isinscope for the building name, this does not give the desired outcome.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors