Forum Discussion

Glenda's avatar
Glenda
Icon for Helper I rankHelper I
5 years ago

Filtering Table to only records which contain Text from another Table

Hi All,    I have 2 tables one with records of injuries and one with Body Parts. I need to count the number of time a body part is in the injury record table. Which I have done using the formula be...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Glenda,

You can try to use the following formula to create a new table with the part field that lookup values from the 'body part' table:

Injury with Body Part 2 = 
ADDCOLUMNS (
    Injury,
    "Part",
        CONCATENATEX (
            FILTER (
                ALLSELECTED ( 'Body Location'[Body Part] ),
                SEARCH ( [Body Part], 'Injury'[Key Phrase], 1, -1 ) > 0
            ),
            [Body Part],
            ","
        )
)

After these, you can create a table visual with 'part' as category and distinct count of 'records id' as value.

Notice: the blank category means the not match records, if you not need this, you can add a filter on the part field to show nonblank records.

Regards,
Xiaoxin Sheng

Glenda's avatar
Glenda
Icon for Helper I rankHelper I
5 years ago

I am trying to avoid adding another column to my injury table as this almost doubles the size of the file which is already large. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Glenda,

    OK, you can try the following formulas if helps. (I remove the add column types and use countrow function as a condition to filter records)

    Injury with Body Part 2 =
FILTER (
    'Injury',
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Body Location'[Body Part] ),
            SEARCH ( [Body Part], 'Injury'[Key Phrase], 1, -1 ) > 0
        ) > 0
    )
)

    Regards,

    Xiaoxin Sheng