Calculating Measures by Searching text in another table

Hi jvirgi ,

You can create 2 measures for calculating average of rating and sentiment as below:

Avg rating = 
SUMX (
    FILTER (
        'Table 1',
        SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
    ),
    'Table 1'[Rating]
)
    / COUNTROWS (
        FILTER (
            'Table 1',
            SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
        )
    )

Avg sentiment = 
SUMX (
    FILTER (
        'Table 1',
        SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
    ),
    'Table 1'[Sentiment]
)
    / COUNTROWS (
        FILTER (
            'Table 1',
            SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
        )
    )

Best Regards

Rena