Filter with unrelated tables

[RichardP]

Hi all,

Is there a way to make a measure like this work?

CALCULATE(COUNTROWS('users'),FILTER('sales',CONTAINSSTRING('users'[purchase codes],'sales'[purchase codes])))

I have two unrelated tables, 'users' and 'sales'.  I'm trying to add a measure into the 'sales' table that counts the number of users who have the purchase code in their records.

I belileve the measure above is wrong because the filtering is on two unrelated tables... is there a way to make that work?

[v-cherch-msft · Accepted solution]

Hi @RichardP 

You may try to use below measure.

CountUsers = 
CALCULATE (
    COUNTROWS ( Users ),
    FILTER (
        Sales,
        MAX ( Sales[Purchase codes] ) IN VALUES ( Users[Purchase codes] )
    )
)

Regards,

[parry2k]

@RichardP can you share sample data in excel and expected result?