Power BI DAX - Count number of records if contains string from a reference table

Question

How do I write a DAX function to count the number of cells in a column contain a string, from a list of strings in a reference table.

For example, imagine that I have a items table and a reference table:

Items Table:

id    items_list
1     hats, jar, shoes
2     Fedora, key, Pens
3     Fedora, jars, Pens
4     baseball cap, Sandals

Reference Type Table:

id     hat_types    shoe_types
1      hat          shoes
2      fedora       sandals
3      cap

I would like to write a DAX function that takes in the strings from the Reference Type table and checks that if the string is contained in the Items table. And if it does, then count the number of rows.

For example:

When looking to see if the strings in the hat_types column are contained in the items_list, I can count 4 rows.

When looking to see if the strings in the shoe_types column are contained in the items_list, I can count 2 rows (first and last row).

How can I do this on Power BI?

Any help you can give is much appreciated!

Thank you

Welcome to Stack Overflow! Please edit to add meaningful code and a problem description here. Posting a minimal reproducible example that demonstrates your problem will increase your chances of getting good answers. — lucascaro

Alexis Olson Alexis Olson · Accepted Answer · 2018-11-12T16:21:52

You can do something like this:

ShoeCount = 
SUMX(Items,
    IF(
        SUMX(ReferenceType,
            IF(
                LEN(SUBSTITUTE(LOWER(Items[items_list]), ReferenceType[shoe_types], ""))
                    < LEN(Items[items_list]),
                1
            )
        ) > 0,
        1
    )
)

This iterates through each row in the Items table and checks if any of the shoe_types are a substring of items_list. If so, then you add one to the count.

I test for substring by attempting to substitute any occurrence of the shoe_types string with the empty string "" and checking if that made the whole items_list string shorter.

Note: LOWERconverts the string to all lower case for better matching.

The hat counting measure can be done analogously. All you need to do is change shoe_types to hat_types.

Power BI DAX - Count number of records if contains string from a reference table

1 Answers