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 use...
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())
v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity 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