Forum Discussion
Count rows containing different specifc text
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
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
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?
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"
