CONTAINSROW() vs. INTERSECT() Power BI Functions: Pros and Cons and When to Use

I just recently did a video tutorial for CONTAINSROW() function. In that video I was taking a look at the coupon redemption data with a goal of determining how much fraud was occurring across various retailers. One of the subscribers commented on the video that this problem could have been solved with the INTERSECT() DAX function and I could not have agreed more. Obviously, the goal was to explain how CONTAINSROW() function worked, but now there is no reason why we could not take another look at the problem and see if INTERSECT() could work just as well.

Turns out it works even better than the CONTAINSROW() function (given our specific scenario), however, there are several important consideration to remember. Even though INTERSECT() worked very well it has several limitations, specifically, it requires that both tables used for intersecting (I am guessing it must be a technical term ) have to have the same amount of columns. CONTAINSROW() on the other hand allows us flexibility to compare tables that have completely different structures. So I would recommend INTERSECT() to be considered first due to its simplicity, however, in a case where you are going to be making subsequent manipulations on your source table and you need a specific set of columns in it (and where not all of these columns are present in the other table used for comparison), CONTAINSROW() function would be a clear winner for this scenario.

You can download the model used in this video here.

Fraud Coupons count with CONTAINSROW() = 
var badCoupons = 
CALCULATETABLE(
	FILTER(
		SUMMARIZE(
			'Coupon Redemptions', 
			'Coupon Redemptions'[Coupon], 
			"Redemption Count", 
			COUNT('Coupon Redemptions'[Redemption Date])
		), 
		[Redemption Count]>1
	), 
	ALL('Coupon Redemptions')
)

return
COUNTROWS(
    SUMMARIZE(
        FILTER('Coupon Redemptions', 
            CONTAINSROW(
                SUMMARIZE(
                    badCoupons, 
                    [Coupon]
                ), [Coupon]
            )
        ), 
        [Coupon]
    )
)

Fraud Coupons count with INTERSECT() = 
var badCoupons = 
CALCULATETABLE(
	FILTER(
		SUMMARIZE(
			'Coupon Redemptions', 
			'Coupon Redemptions'[Coupon], 
			"Redemption Count", 
			COUNT('Coupon Redemptions'[Redemption Date])
		), 
		[Redemption Count]>1
	), 
	ALL('Coupon Redemptions')
)

RETURN
COUNTROWS(
    INTERSECT(
        VALUES('Coupon Redemptions'[Coupon]),
        SUMMARIZE(badCoupons, [Coupon])
    )
)