Count rows containing one of many strings (not exactly)

[Anonymous]

Context:

Table1 = 'Companies' (list of companies)

Table2 = 'Specialties' (list of areas of expertise of those companies)

Relationship = 1:M (one company has multiple specialties)

Company_id	Company_name
1	Microsoft
2	Google
3	Amazon

Specialty_id	Specialty_name	Company_id
1	Cloud	1
2	Data	1
3	Database	2
4	System Architecture	2
5	Security	2

I want to create a column in the table 'Companies' that counts the amount of specialties of that company containing one of many strings. In other words, the column 'Specialty_name' has to be filtered so that only the rows remain that contains one of the strings (not exact). 

List of strings = {"Data", "Cloud", ...}

So the output should be:

Company_id	Company_name	Count_Specialties
1	Microsoft	2
2	Google	1
3	Amazon	0

As you see, the new column should also count "Database" even though that exact string is not in the list (only "Data").

So far, I managed to create a DAX but it's very long and inefficient: 

Count_Specialties= CALCULATE(COUNTROWS(FILTER('specialties', CONTAINSSTRING('specialties'[specialty_name], "Cloud") || CONTAINSSTRING('specialties'[specialty_name], "Data") + 0

The list contains 20+ strings, so the DAX keeps adding strings via || (and at the end I add '+ 0' to avoid null values). 

I wonder if there is another way that can produce the same column more efficiently. 

Thanks in advance!

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

You need to put all the conditions into the formula. There doesn't seem to be a simpler way. 

Or you can put the conditions into a column. Then count the column.

Column = if(CONTAINSSTRING('specialties'[specialty_name  ], "Cloud") || CONTAINSSTRING('specialties'[specialty_name  ], "Data"),1,0)

Measure = CALCULATE(COUNT(Specialties[Column])+0,FILTER(ALL('Table'),'Table'[Company_id  ]=SELECTEDVALUE('Table'[Company_id  ])))

If I have misunderstood your meaning, please provide your pbix file without privacy infromation and desired output.

Best Regards

Community Support Team _ Polly

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

[Whitewater100]

Hi:

You can do this via the data model by either a measure or a new table. Please see two tables (different ways of calculating the same result) and a measure doing the same thing. Please see link here:

https://drive.google.com/file/d/1EcZBdUDVUC0cOb3CSM1QdnblfbvZQzxL/view?usp=sharing 

[Anonymous]

Hey, thanks a lot for your response!

Unfortunately don't think I was clear enough in the description. I have a big list of relevant Specialties on which I want to filter, but the amount of Specialties linked to the companies is much higher. So it's not enough to just count the specialties. I know the example I gave was misleading because all of the specialties needed to be counted.

I will edit the post, let me know if it still isn't clear!

Hopefully you can still help 🙂