A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
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?
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 table 2.
But there are times when that doesn’t work. For instance, when you’re not looking for an exact match, but a “contains” match.
Cutting to the chase
I won’t drag this one out. Let’s give you a formula (I won’t call it THE formula, because there are definitely other ways and probably better ways).
[Is this company a metals company] =
) > 0,
- The SUMX part – Step through every row in MatchList. For each row in MatchList, evaluate the FIND function. FIND will return a number. Sum up all the values you get from FIND. (There will be 5 values to sum up, because there are 5 rows in MatchList.
- FIND – for this row of MatchList, see if you find that substring in the current row of Companies. If you do, return the number of the position where that substring is found, like FIND always does. If you don’t find a substring match, return 0.
- UPPER – I did this to make the FIND case-INsensitive. If you want it to be case-sensitive, remove the UPPERs.
- IF – if you get 0 back from the SUMX, that means no matches were found, so return “Probably Not.” If anything other than 0 comes back, there was at least one match (maybe more!), so return “YES!”
No Relationships Are Required for this Technique
A million variations
I bet there are lots of practical ways to twist this: Return the number of matches, rather than Yes/No. Case-sensitive versus insensitive. Begins with, ends with, exact match. Go nuts.
Grab the workbook
Seriously, just grab it already.