Using ID number to find various associated products

[Anonymous]

Long time reader, first time posting...

I am looking to create a column that identifies if a customer has the required product, in addition to other products. Customers have ID numbers, and each product has a row. I am looking to add a column that uses the ID number as the lookup, and finds if that ID number has a row with the REQUIRED product name. The hope is to return a True vs False.

OR the inverse... use the ID number that has a row with the REQUIRED product, and return True vs False if there are other products associated with the ID number.

First scenario:

Customer ID #	Product Name	Has Required
1234	Required Product	True
5678	Optional Product	False
9100	Optional Product	False
1234	Optional Product	True

Second scenario:

Customer ID #	Product Name	Has Required
1234	Required Product	True
5678	Required Product	False
9100	Required Product	False
1234	Optional Product	True

I hope this makes sense. Any help would be greatly appreciated.

Thanks!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

I have created a sample for your reference. Here I created two measures as below to work on different tables.

Measure = 
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Customer ID #] ),
            'Table'[Product Name] = "Required Product"
        )
    )
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Customer ID #] )
    )
RETURN
    IF ( c = BLANK (), FALSE (), IF ( a > c, TRUE (), IF ( a = c, FALSE () ) ) )

Measure 2 = 
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table (2)' ),
        FILTER (
            ALLEXCEPT ( 'Table (2)', 'Table (2)'[Customer ID #] ),
            'Table (2)'[Product Name] = "Required Product"
        )
    )
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table (2)' ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[Customer ID #] )
    )
RETURN
    IF ( c = BLANK (), FALSE (), IF ( a > c, TRUE (), IF ( a = c, FALSE () ) ) )

Attached the pbix as well.

[Nathaniel_C]

HI @Anonymous ,

Try this.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Required product =
VAR _calc =
    CALCULATE (
        COUNTROWS ( myTable ),
        mytable[product] = "Required Product",
        ALLEXCEPT ( mytable, mytable[ID] )
    )
RETURN
    IF ( _calc >= 1, "True", "False" )