Count rows containing different specifc text

[JensHN]

Hi,

my datamodel contains two tables. First table is "Jobs" with an "Attributes" filed:

  

Second table is "Attribute" with a "Name" filed.

There is no connection between these tables. Important thing: The Jobs[Attributes] field does not contain the exact same values like Attribute[Name]. For example, the Attribute table has the value "Christmas", the Jobs table has values like "Christmas 2017", "Christmas 2018", etc.

What i need is the amount of jobs for each attribute. For the sample data the result should look like this:

I tried a calculated column on the Attribute table, but I only got an error:

AmountJobs = CALCULATE(COUNTROWS(Jobs); SEARCH(Attribute[Name]; Jobs[Attributes]))

[Stachu · Accepted solution]

this will work

Measure = 
VAR _Attribute = SELECTEDVALUE(Attribute[Name],BLANK())
VAR _SearchFlag = ADDCOLUMNS(Jobs,"Flag",SEARCH(_Attribute,Jobs[Attributes],1,BLANK()))
VAR _RelevantRows = FILTER(_SearchFlag,[Flag]<>BLANK()) 
RETURN
COUNTROWS(_RelevantRows)

you need to use Name from Attribute table and measure in the visual, there should be no joins between the tables
it does the job, but search will affect the performance badly (it has to iterate every string in the Jobs table), so the performance may not be great for bigger datasets

[JensHN]

Thank you for your fast solution, this works well.

Would it be better for the performance when the amount of jobs is a calculated column in the Attribute table?

[Stachu]

does it perform badly now? if you're fine with performance then I would keep it as is

The problem with precalculating columns in the table is following: you would have to do it for each event, so Christmas, New Year, and Easter separately, and additional one for every single new event. You cannot have calculated column that's dependant on the slicer value, although it does work nicely as variable (cause it's always single value in the filter context of the visual, i.e. for Christmas column in the chart I only calculate Christmas)

I think the most performant solution would be changing the Jobs table to something like this:

ID	Attribute	Year
1	Christmas	2017
2	Christmas	2017
2	New Year	2017
3	New Year	2018
3	Easter	2018

then you can just do simple row count of unique IDs per Attributes

transformation could be done in M, assuming the naming conventions is always "eventname YYYY"