Hi all,
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
| 19-22 |
| 40-44 |
| 19-22 |
| 34-37 |
| 19-22,28-31,40-43 |
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):
Value = SWITCH('Measures Table'[SelectionMeasure],1,CALCULATE(SELECTEDVALUE(SNAMaster[NO2_mean]),
TREATAS(SUMMARIZE(FILTER(SNAMaster
(CONTAINSSTRING(CONCATENATEX(VALUES(legendcheck2[CAT]),legendcheck2[CAT],","),
VALUES(SNAMaster[Legend NO2 Cat])))),SNAMaster[Legend NO2 Cat]),SNAMaster[Legend NO2 Cat])))
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!
