Combing TREATAS, COTAINSSTRING and CONCATENATEX issue (Icon Map)
I have quite a bespoke issue that I can seem to find a resolution for (either by my own working or by searching online). I have a column of data in one table that has a corresponding strings such as bellow:
Legend NO2 Cat
You'll note that in the last entry, multiple caterogries are included as the object encompasses multiple catergories. I have a second table (which is actually more of a legend as this is being used for a map) with the same list of corresponding catergories. The idea is that when someone clicks a catergory, or multiple catergories, within that table, it will filter the table above. Because of some quite deep complexeties with the Icon Map visualisation I am using, I cannot use a direct relationship so I have to use TREATAS.
I have come up with a DAX that almost gets me where I need to be and is as follows and uses a CONCATENATEX on the fitlering table to create a single object (as Icon Map does not like tables of multiple values) (legendcheck2 is the filtering context and SNAMaster is the primary dataset)(ignore the switch):
This allows the last object in the table to be included but only when all of the catergories listed are selected. So for example, as long as 19-22,28-31,40-43 are within the selection criteria, the object will show. However, if for example I wanted to select just 19-22, the object will not appear.
I understand why this is, because it cannot find the string as intended, but I am at a loss for how to resolve this issue so that the object will remain active when filtered by any combination of values selected. So that, for example, if I have 19-22 and 40-43 selected, the object will remain active.
Am I missing something obvious here or should I be trying something entirley different? Any help would be greatly appreciated!
Power BI is a tool that works extremely well with denormalized data only, think: star schema. Each column should contain atomic data, not compounds like your comma-delimited values. Please denormalize the table using Power Query, create suitable columns for grouping and... your life (and your users') will be much, much easier. No more containsstring, concatenatex and similar BS. Your DAX will also become as easy as 1-2-3. Not to even mention it'll be fast.