New measure Countrows from filtered columns

[cyrilpowerbi]

Hello,

I have a table where a hotel id can have different room type per day and per hour of the day.

In the following table I am looking at hotel id 21496, on day 17/03/19 where the hotel has 2 rooms available between 10am and 16pm. Each line represent 1 hour of a room in my table.

I can easily get the room number for this hotel on that day: 2

The data I want to know is how many hours those rooms are available, i.e from 10 am to 16pm, that's 7 hours per room. 

I need the result to adapt to the number of hotel selected so that if I am looking at a city with 10 different hotels I can get the sum of hours available per day.

Hope that make sence and many thanks for your help.

[v-piga-msft]

Hi @cyrilpowerbi ,

---

The data I want to know is how many hours those rooms are available, i.e from 10 am to 16pm, that's 7 hours per room. 

---

If you want to know how many hours those rooms are available, you could try the measure below.

Measure =
VAR maxhour =
    CALCULATE ( MAX ( 'Table1'[Hour] ), ALLEXCEPT ( Table1, Table1[Room id] ) )
VAR minhour =
    CALCULATE ( MIN ( 'Table1'[Hour] ), ALLEXCEPT ( Table1, Table1[Room id] ) )
RETURN
    DATEDIFF ( minhour, maxhour, HOUR ) + 1

Here is the result.

If you still need help, please share your data sample which could reproduce your scenario as table format and your desired output so that I could undertand your requirement better and help further on it.

Best Regards,

Cherry

[cyrilpowerbi]

Thanks for the info.

It working ok for one hotel as per orginal example.

However in my table I have various hotel id. 

Therefore, once I input your formula its giving me the number of hours for all room type of the database for those days.

I´d like to have it only for the selected hotel-id.

Thanks

[cyrilpowerbi · Accepted solution]

Hello,

I have modified my table relationship to incorporate hotel into the relvant table and now it´s working.

Many thanks again