Count with condition per customer per id

[H_insight]

Hiya,

I am trying to write a measure to count the Products per customer and per id, where the "Product" column = "Product 1" and does not contain any "Product 2" for the same ID.

here s my attempt

Measure = 
CALCULATE(COUNTA(Table1[Product]),
        FILTER(Table1, Table1[Product] = "Product 1"), FILTER(Table1, 
 NOT(CONTAINSSTRING(Table1[Product],"Product 2"))))

I am getting the result count of 3, but I am looking only for 2 as a result (example below)

Sample Data:

IdCustomerProduct

1	Customer 1	Product 1
1	Customer 1	Product 2
1	Customer 1	Product 3
1	Customer 1	Product 4
2	Customer 1	Product 1
2	Customer 1	Product 4
2	Customer 1	Product 5
3	Customer 1	Product 1
3	Customer 1	Product 8
3	Customer 1	Product 3
3	Customer 1	Product 4

Thanks

[H_insight]

Hi @Jihwan_Kim ,

I am still getting the same result for 3 records, and my goal is to show only 2 records

Goal:

[Jihwan_Kim · Accepted solution]

Hi,

thank you for your feedback.

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.

[H_insight]

@Jihwan_Kim Super Star🌟!! Thank you very much. I have to say, formatting DAX does help a lot in understanding the logic behind the code. 👍

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file, whether it suits your requirement.

Expected result measure: =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            DISTINCT ( 'Table'[Id] ),
            "@productonecondition", CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Product] = "Product 1" ) ) ),
            "@producttwocondition", CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Product] = "Product 2" ) ) )
        ),
        [@productonecondition] >= 1
            && [@producttwocondition] = 0
    )
)