Count number of records if contains string from a reference table

[powernewb]

Hi there,

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 refernece table:

Items Table:

id	items_list	cost
1	hats, jar, shoes	20
2	Fedora, key, Pens	10
3	baseball cap, Sandals	30

Refernce Type Table:

Hat Types	Shoe Types
hat	shoes
fedora	sandals
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_list 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 see 3 rows.

When looking to see if the strings in the Shoe Types column are contained in the items_list, I can see 2 rows.

Any help you can give is much appreciated!

Thank you!

[v-frfei-msft · Accepted solution]

Hi @powernewb,

We can take the following steps to meet your requirement.

1. Create a calculated table.

CRO = CROSSJOIN(Items,Refernce)

2. Create the measures as below.

Hat = IF(ISBLANK(SEARCH(MAX(CRO[Hat Types]),MAX(CRO[items_list]),1,BLANK())),BLANK(),1)

hat total = SUMX(CRO,[Hat])- CALCULATE(COUNTROWS(CRO),FILTER(CRO,MAX(CRO[Hat Types])=BLANK()))

shoes = var shoe = MAX(CRO[Shoe Types])
return
IF(ISBLANK(SEARCH(shoe,MAX(CRO[items_list]),1,BLANK())),BLANK(),1)

shot total = SUMX(CRO,[shoes])- CALCULATE(COUNTROWS(CRO),FILTER(CRO,CRO[Shoe Types]=BLANK()))

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @powernewb,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank