Thursday - last edited Thursday
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:
|1||hats, jar, shoes||20|
|2||Fedora, key, Pens||10|
|3||baseball cap, Sandals||30|
Refernce Type Table:
|Hat Types||Shoe Types|
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.
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!
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.