Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago
    // 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

13 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    // 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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
      Anonymous
      Not applicable
      6 years ago

      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