Reply
Highlighted
New Member
Posts: 1
Registered: Thursday

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:

 

iditems_listcost
1hats, jar, shoes20
2Fedora, key, Pens10
3baseball cap, Sandals30

 

 

Refernce Type Table:

Hat TypesShoe Types
hatshoes
fedorasandals
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!

 

 

 

 

Community Support Team
Posts: 1,320
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()))

123.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Attachment