DAX - How to create Yes/No column based on individual item in grouped list

[mountains]

Hi, I am trying to create a column using DAX that returns Yes or No based on if there is one item in a list of transactions for each customer grouped by transaction ID. 

Below is an example of what I want to create and the blue column is my desired result. So if the customer bought a rollex, I want the row to show Yes, otherwise No.

Purchase transation table:

Power BI table view:

I'm currently using this command but it sometimes returns Yes when there is no Rollex in the customer items list.

Rollex =

IF(COUNTROWS(FILTER(Table1, (Table1[TransactionID] = EARLIER([TransactionID])) && Table1[Item] = "Rollex")) > 0, "True", "False")

Thanks in advance for any help!

[DataNinja777]

@mountains ,

There are many ways to achieve your required output and one of them is to use calculated table like below:

Summary =
SUMMARIZE (
    'Table',
    'Table'[CustomerlD],
    'Table'[CustomerName],
    'Table'[TransactionlD],
    "Number of items", DISTINCTCOUNT ( 'Table'[Item] ),
    "Rollex",
        IF (
            CONTAINSSTRING (
                CONCATENATEX ( DISTINCT ( 'Table'[Item] ), 'Table'[Item] ),
                "Rollex"
            ) = TRUE,
            "Yes",
            "No"
        )
)

The above calculated table dax formula produces the table like below in your Power BI data view. 

I attach an example pbix file.

Best regards,

[mountains]

@DataNinja777, Thanks so much for your reply.

I should have said in the original post that I'm adding the "Rollex" column to a current view that's based off data in a table. With that information, how would accomplish that? 

I tried adding just this logic and Power Bi said working onit for so long I had to close out the app and restart.  

    Rollex =
        IF (
            CONTAINSSTRING (
                CONCATENATEX ( DISTINCT ( 'Table'[Item] ), 'Table'[Item] ),
                "Rollex"
            ) = TRUE,
            "Yes",
            "No"
        )

I'm really new at dax and Power Bi so thanks for your patience. 

[Anonymous · Accepted solution]

Thanks for the reply from @DataNinja777 , please allow me to provide another insight:

Hi, @mountains 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.I've created the following measure to meet the values of the items column that you didn't mention:

 MAX('Yes/No'[ItemNumber]) 

3.I've provided two scenarios, one for calculated columns and one for measures.

 calculated column :

Rollex1 =
VAR nn1 =
    CALCULATE (
        COUNT ( 'Yes/No'[Item] ),
        FILTER (
            ALLSELECTED ( 'Yes/No' ),
            'Yes/No'[TransactionID] = EARLIER ( 'Yes/No'[TransactionID] )
                && 'Yes/No'[Item] = "Rollex"
        )
    )
RETURN
    IF ( nn1 <> 0, "Yes", "No" )

Below are the measure I've created for your needs:

Rollex =
VAR nn =
    CALCULATE (
        COUNT ( 'Yes/No'[Item] ),
        FILTER (
            ALLSELECTED ( 'Yes/No' ),
            'Yes/No'[TransactionID] = MAX ( 'Yes/No'[TransactionID] )
                && 'Yes/No'[Item] = "Rollex"
        )
    )
RETURN
    IF ( nn <> 0, "Yes", "No" )
 

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.