Creating a new table with rows containing substring in a column

[frankegg]

Hi

I am very new to PowerBI queries, and hope someone can push me in the right direction 🙂

I have a table as shown below, named RoomTable.

I will use this to create a similar table containing only the rows of type meeting room, and calculate capasity for these rooms only.

I have tried the folowing with no luck:

Meetingrooms =

FILTER (

'RoomTable'

CONTAINS ('RoomTable'), [Room], "Meeting")

)

Best regards,

Frank

[FreemanZ · Accepted solution]

Try to create a measure with the code below, then you shall be able to create your expected table visual with this measure:

Meetingrooms :=

CALCULATE(

     SUM('RoomTable'[Capacity]),

     FILTER (

         'RoomTable'

         CONTAINS (

            'RoomTable', 

            'RoomTable'[Room], 

            "Meeting"

          )

      )

)

[daXtreme]

In Power Query denormalize the table first. Each piece of info should be stored in a separate column. So, your room number in one column and the type of room in another. Then you'll be able to filter seamlessly. Filtering by a substring is not the best idea and not Best Practice. Not to mention it slows down DAX.