## Desktop

Highlighted
New Member
Posts: 1
Registered: ‎11-08-2018

# Count number of records if contains string from a reference table

[ Edited ]

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.

Thank you!

Accepted Solutions
Community Support Team
Posts: 1,648
Registered: ‎07-10-2018

## Re: Count number of records if contains string from a reference table

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

All Replies
Community Support Team
Posts: 1,648
Registered: ‎07-10-2018

## Re: Count number of records if contains string from a reference table

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Posts: 1,648
Registered: ‎07-10-2018

## Re: Count number of records if contains string from a reference table

Hi @powernewb,

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

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.