Help building a query, many2many

[Anonymous]

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.  

[Anonymous · Accepted solution]

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

[v-gizhi-msft]

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

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490