Filter OR across different tables

[fabiomanniti]

Hello, I would like to count the users that made at least one sale among different products. The problem is those products are on different tables, so I would like somethink like

CALCULATE(
COUNT(user[id]),
FILTER(TABLE1, COUNT(sales1[id])>0) OR
FILTER(TABLE2, COUNT(sales2[id])>0) OR
FILTER(TABLE3, COUNT(sales3[id])>0)
)

But of course this won't work, but I hope the logic is clear

[JPScotland]

Can you try it using || instread of OR

CALCULATE(
COUNT(user[id]),
FILTER (
TABLE1, COUNT(sales1[id])>0) || 
TABLE2, COUNT(sales2[id])>0) ||
TABLE3, COUNT(sales3[id])>0)
)

[fabiomanniti]

Doesn't work.
For the way the function FILTER is made you have to chose only one table

[v-easonf-msft · Accepted solution]

Hi, @fabiomanniti 

Please try measure as below:

Result =
CALCULATE (
    COUNT ( User[UserID] ),
    FILTER (
        User,
        User[UserID]
            IN VALUES ( sales1[userID] )
                || User[UserID]
                    IN VALUES ( sales2[UserID] )
                        || User[UserID] IN VALUES ( sales3[UserID] )
    )
)

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[JPScotland]

Yep I see your issue now.  

Possibly you could create a custom table (using CALCULATETABLE) and bring in all the elements you need from the 3 tables, then use the formula from above based on the new custom table.  

I think you would need relationships between the tables for this to work: -

_calcTable = 
    VAR _altTable = 
        CALCULATETABLE (
                            SUMMARIZE (
				TABLE1,
				TABLE1[sales1[id]]
				TABLE2[sales2[id]]
				TABLE3[sales3[id]]
				)
			)

    RETURN
    _altTable