Compare rows from column having same saleId

[amrutamore25]

I need to compare two rows in a column which has same saleId and count the number of occurrences of one of the values. Please find the example below.

SaleID	Product Name
1	Product 1
1	Product 2
1	Product 3
2	Product 1x
2	Product 2
2	Product 3
3	Product 1
3	Product 2
3	Product 3

I need to find the count of 'Product 2' having 'Product 1' with same SaleID. It shouldn't include 'Product 2' having 'Product 1x' with same SaleID.

[amrutamore25]

Thank you @Mahesh0016 !
Your solution is working as expected.

[Mahesh0016 · Accepted solution]

@amrutamore25  I hope this helps you. Thank You.
Count_Product2_with_Product1 =
CALCULATE(
COUNTROWS(YourTable),
FILTER(
YourTable,
YourTable[Product Name] = "Product 2" &&
CALCULATE(
COUNTROWS(YourTable),
FILTER(
YourTable,
YourTable[Product Name] = "Product 1" &&
YourTable[SaleID] = EARLIER(YourTable[SaleID])
)
) > 0 &&
NOT(
CALCULATE(
COUNTROWS(YourTable),
FILTER(
YourTable,
YourTable[Product Name] = "Product 1x" &&
YourTable[SaleID] = EARLIER(YourTable[SaleID])
)
) > 0
)
)
)