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(
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

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

4 Replies

  • JPScotland's avatar
    JPScotland
    Helper I
    4 years ago

    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'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