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 if it works for your scenario:

Count DX v2 =
CALCULATE (
    DISTINCTCOUNT ( 'Injury'[Record No.] ),
    FILTER (
        'Injury',
        COUNTROWS (
            FILTER (
                ALLSELECTED ( 'Body Location'[Body Part] ),
                SEARCH ( 'Injury'[Key Phrase], [Body Part] ) > 0
            ) > 0
        )
    )
)

 If the above not help, please share some dummy data to test:

How to Get Your Question Answered Quickly

Regards,
Xiaoxin Sheng

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

 

Hi Xiaoxin,

 

Thanks for the reply. There is no issue with the count formula, I have this working. My issue is the size of my injury table which is over 300,000 rows. I am trying to improve the performance by creating a calculated table which is a subset of the main injury table but only contains records where there is a body part in the text.

 

The issue is with the red part of my table formula below as this limits the table to only the max body part ie shoulder.  

 

Injury with Body Part =

CALCULATETABLE('Injury',

    FILTER(Injury,

        CONTAINSSTRING('Injury'[Key Phrase],max('Body Location'[Body Part]))

        ),

RELATEDTABLE('Injury'))

 

I have attached an example (link below). I want to fix the formula above so that the Injury with Body Part table shows any record with any of the body parts on the body part table.

 

Filter of Table by Text in another Table.pbix

  • 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