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 below. This works and is great on a small data set but when I load my full set (350,000 rows) this runs really slow. 

 

Count DX v2 =
CALCULATE(DISTINCTCOUNT('Injury'[Record No.]),
FILTER('Injury',
CONTAINSSTRING('Injury'[Key Phrase],MAX('Body Location'[Body Part]))))
 
To improve the performance I created a filtered table with the formula below. My problem is that I'm using MAX('Body Location'[Body Part]) so my filtered table is only giving me wrist as it's the max value. 
 
Body Loc Table =
CALCULATETABLE('Injury',
FILTER('Text Insight',
CONTAINSSTRING('Injury'[Key Phrase],max('Body Location'[Body Part]))
),
RELATEDTABLE('Injury'))
 
What should I be using instead of MAX in my CALCULATE TABLE formula? 
Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Glenda , Try a measure like

     


    Count DX v2 =
    CALCULATE(countx(values('Injury'),'Injury'[Record No.]),
    FILTER('Injury',
    CONTAINSSTRING('Injury'[Key Phrase],MAX('Body Location'[Body Part]))))

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

      That is what I did but due to the size of my injury table (350,000), the measure is really slow. So I am trying to filter the table down first as only about 40,000 records actually have body parts. 

       

      • 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