Using Ref in dependant drop down lists

[Mike_VK]

I am creating a facilities management app and am using three tables. 

1) The Room Repair Request table (relevant columns: Repair ID (key), Building - Ref Type, pointed to Buildings Table, Room - Ref Type, pointed at Rooms table

2) The Building table (relevant columns: Building ID (key) and Building Name (Label)

3) The Rooms Table (relevant columns: Room ID (key) the Room_Space =Name or Number of Room (label) and Building-contains the building the room is in.

In my Room Repair Request form, I have created a column for Building (Type Ref, Ref Table "Buildings"),. This works great, the drop down shows the values from the referenced table

I want the Rooms column to show only the values associated with the Building selected. I have set this column up as (Type Ref, Ref Table "Rooms") and then used the 

SELECT(
Rooms[ROOM_SPACE],
[BUILDING] = [_THISROW].[Building]
)

My dropdown list for Room keeps coming back blank. I'm at a total loss and have spent hours on this. I'm sure it is a simple fix but I have tried every permutation I can find online and still the same. 

Any help would be greatly appreciated

[Aurelien]

Hi @Mike_VK 

Let's say you have:

1) Table ROOM with columns:

- id

- label

- BUILDING, Type Ref, source table BUILDING

2) Table BUILDING, with columns:

- id

- label

==> you probably have a virtual column named [Related ROOMS]

This columns contains all the room id that are part of the current building.

3) Table ROOM REPAIR REQUEST, with columns

- id

- BUILDING, Type Ref, source table BUILDING

- ROOM, type Ref, source table ROOM

==> You need to add a valid_if condition with this expression:

[BUILDING].[Related ROOMs]

Said otherwise:

You need to change your expresssion with:

SELECT(
Rooms[ROOM_ID],
[BUILDING] = [_THISROW].[Building]
)

 where [ROOM_ID] is the key-column of your table Rooms.

[Mike_VK]

Hi @Aurelien,

The drop down list for "Room" in the Room Repair Request remains blank. Are there any specific screenshots I could post that would help this process? Like the data source or any specific column settings?

[Aurelien]

Can you try my suggested expression instead? This would get us rid of specific columns names. 

It would probably be (with additional "s")

[BUILDING].[Related ROOMSs]