Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Joe_Marangos
Frequent Visitor

Combing TREATAS, COTAINSSTRING and CONCATENATEX issue (Icon Map)

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! 

1 REPLY 1
daXtreme
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.