Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help building a query, many2many

I have a list of product ID's and the users that order certain products.  I am looking to find a count for the number of ID's that have one, the other, and both of certain users.  In reality, the user list is 18 names (2 different sets of 9 names).  

 

In this example:

Col - A - ID -> duplicated to reflect the product and the multiple users that access the product

Col - B - User -> interested in finding relationships between users and the products they use

Col - C - Result -> What I envision a simple operation would do to show only the relevant user data I am after

Col - D - Wanted -> The goal is to have the query return an answer based on the ID's 

 

To sum this up.  I want to know when certain configurations of any "User A" and any "User C" are occurring.  Furthermore, I want this information to be reflected for all cells with the same ID.  

 

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago
    This did the trick
     
    Wanted =
    VAR countr =
    CALCULATE(
    COUNTROWS(Table),
    FILTER(Table,
    Table[ID] =  EARLIER(Table[ID])
    && Table[Result] = EARLIER(Table[Result])
    ))
    Return IF(countr > 1, "Both", BLANK())

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try this measure:

    Wanted =
IF (
    MAX ( 'Table'[ID] )
        = CALCULATE (
            MAX ( 'Table'[ID] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[User] = 'Table'[Result]
                    && 'Table'[ID] IN FILTERS ( 'Table'[ID] )
            )
        ),
    "Both",
    BLANK ()
)

    The result shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    This did the trick
     
    Wanted =
    VAR countr =
    CALCULATE(
    COUNTROWS(Table),
    FILTER(Table,
    Table[ID] =  EARLIER(Table[ID])
    && Table[Result] = EARLIER(Table[Result])
    ))
    Return IF(countr > 1, "Both", BLANK())