topic Re: Compare rows from column having same saleId in DAX Commands and Tips

Compare rows from column having same saleId

amrutamore25 — Mon, 04 Sep 2023 09:22:34 GMT

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.

Re: Compare rows from column having same saleId

Mahesh0016 — Mon, 04 Sep 2023 09:40:02 GMT

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
)
)
)

Re: Compare rows from column having same saleId

amrutamore25 — Mon, 04 Sep 2023 10:53:01 GMT

Thank you Mahesh0016 !
Your solution is working as expected.