Forum Discussion

fabiomanniti's avatar
fabiomanniti
Helper III
4 years ago
Solved

Filter OR across different tables

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( COUN...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

fabiomanniti's avatar
fabiomanniti
Helper III
4 years ago

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

JPScotland's avatar
JPScotland
Helper I
4 years ago

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