Forum Discussion

frankegg's avatar
frankegg
New Member
3 years ago
Solved

Creating a new table with rows containing substring in a column

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's avatar
    FreemanZ
    3 years ago
    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"
              )
          )
    )

2 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    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.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago
    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"
              )
          )
    )