Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Hi @powernewb,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |