DAX – “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2

  We Want to Flag Rows in our Companies Table (on left) When They Contain a Keyword from our MatchList Table (on right) These are a few of my favorite things… Perhaps the only thing that makes me happier than a new “X” function (I still badly want a CONCATENATEX) is “inventing” a new one (like we’ve seen with PRODUCTX). The other day I was looking at a Power Pivot model and thinking “gee, it sure would be nice to have a CONTAINSX.” Turns out we can “make” our own CONTAINSX using SUMX. Does this row’s value in list one also appear in list two? Reduced to this generic form, this a pretty common overall need. Sometimes you can do this VERY quickly in Power Pivot by relating the two tables, and then writing a =RELATED calc column in table 1 to see if it has a matching value in … Continue reading DAX – “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2