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

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!

 

 

 

 


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()))

123.PNG

 

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.

View solution in original post

Attachment

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()))

123.PNG

 

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.
Attachment
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.