topic Re: Filter a table based on concatenated values in DAX Commands and Tips

Filter a table based on concatenated values

DIACHROMA — Thu, 16 Sep 2021 18:46:20 GMT

Hi guys,

I am working with a table in which the products are concatenated in the same column instead of having one row per product row :

REF	PRODUCT OFFERED
001	AAA BBB DDD
002	CCC BBB
003	AAA
004	CCC DDD
005	BBB AAA

To give you a bit of context, my fact table is a list of sales visits where sales reps can offer multiple products. When the salesperson enters their report, they can check off as many products as they want. All these products arrive in one and the same column.

Besides, I have a Product dim table :

ProductName

AAA

BBB

CCC

DDD

I cannot create a relationship between the "Product" and "Offered Product" columns and I do not have access to the data (which is managed by the IT).

However, I absolutely need to know the number of times this or that product has been offered during the visits.

So I created the below DAX measure :

FoundProduct =

VAR SearchValue = SEARCH( SELECTEDVALUE( ProductName ) ; SELECTEDVALUE( ProductOffered ) ; ; BLANK() )

RETURN

IF( SearchValue > 0 ; "Found" )

And then, when adding the "FoundProduct" measure in the filter section of a visual i was hoping it works.

However, I have several problems: sometimes the results are completely incorrect and above all, when I select several products, I have completely inconsistent results.

How can I use my product table in a slicer to filter my visits table, while keeping the possibility of selecting several products?

I can't wait to read your feedback!

A big thank you in advance,
Pauline

Re: Filter a table based on concatenated values

Greg_Deckler — Thu, 16 Sep 2021 22:12:57 GMT

DIACHROMA Well, I would recommend that you split that column in Power Query using space as delimiter, select your REF column and unpivot other columns. That would likely make this tremendously easier. If for some reason you cannot do that try Count of List items: Count of List Items - Microsoft Power BI Community

In your case:

Measure = VAR __String = MAX('dimProduct'[ProductName]) VAR __Strings = CONCATENATEX('Table',[PRODUCT OFFERED],", ") VAR __Length = LEN(__String) RETURN ( LEN(__Strings) - LEN(SUBSTITUTE(__Strings,__String,"")) ) / __Length

If you put your ProductName in a table along with this measure, should work.

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 14:14:47 GMT

Hi Greg_Deckler ,

Thank you very much for your reply 🙂

I created this formula, I have a result but it does not work when I select several products. At this point the data becomes inconsistent. Maybe it's because the products are not concatenated in the same order each time? Or maybe it comes from my model, I don't know ...

And when I don't select any product, the result shows 0 (and not the total as usual).

While waiting to find a solution I added the visual "Text Filter" from the market place. It works when I type in the name of a product, the numbers look ok but unfortunately I cannot enter multiple products in the search bar.

So I am still working on finding a better solution...

Pauline

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 14:28:01 GMT

DIACHROMA The formula is intended to work in conjunction with the ProductName from the Products table being in a table visualization along with the measure. You could use a slicer to limit which products are in the table visual. I will think about how this might be done without that, pretty sure I could create a solution around that.

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 14:32:12 GMT

DIACHROMA OK, let's say you just want a total number of times things have been sold with only a slicer and let's say a Card visual to display the result. Try this:

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 15:09:25 GMT

Thanks again Greg_Deckler !

So I tested this formula in a card. When I select a single product, I have the right result. But when I select more than one product, some visits are counted twice.

Example :

REF	OFERRED PRODUCTS
001	AAA
002	AAA BBB
003	BBB

If I select AAA and BBB in the silcer, the result will be 4 (instead of 3 which is the expected result because there are 3 commercial visits ).

Also, if no product is selected in my slicer, the total is incorrect. It is much too high: 6599 instead of 1445. Again, if I select all products in the slicer the result is still to high : 1557 instead of 1445 visits in total.

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 15:16:37 GMT

Ah yes indeed! When I filter my slicer products report the total is lower but still incorrect: 1557 (instead of 1445) probably because some visits are counted multiple times.

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 15:55:35 GMT

DIACHROMA OK, let's go this route then:

Measure = VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName]) // this gets the products selected in the slicer in a table with just ProductName column VAR __Table = ADDCOLUMNS( __ProductsToSearchFor, "__Refs", VAR __String = [ProductName] VAR __Refs = CONCATENATEX( SELECTCOLUMNS( FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)), "__Ref",[REF] ), [__Ref],"|" ) RETURN [__Refs] ) VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|") VAR __Count = COUNTROWS(__AllRefs) VAR __AllRefsTable = ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Ref",PATHITEM(__AllRefs,[Value],TEXT) ) RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])))

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 15:56:40 GMT

I can't write :

  VAR __Count = COUNTROWS(__AllRefs)

It doesn't allow me to put "__AllRefs" in the COUNTROWS

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 16:20:54 GMT

DIACHROMA My bad:

Measure = VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName]) // this gets the products selected in the slicer in a table with just ProductName column VAR __Table = ADDCOLUMNS( __ProductsToSearchFor, "__Refs", VAR __String = [ProductName] VAR __Refs = CONCATENATEX( SELECTCOLUMNS( FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)), "__Ref",[REF] ), [__Ref],"|" ) RETURN [__Refs] ) VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|") VAR __Count = __Len - LEN(SUBSTITUTE(__AllRefs,"|","")) + 1 VAR __AllRefsTable = ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Ref",PATHITEM(__AllRefs,[Value],TEXT) ) RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])))

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 16:48:16 GMT

Sorry me again... In the VAR __Count I can't put __Len as it doesnt exist earlier as a var

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 17:03:37 GMT

DIACHROMA I'll get this right eventually:

Measure = VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName]) // this gets the products selected in the slicer in a table with just ProductName column VAR __Table = ADDCOLUMNS( __ProductsToSearchFor, "__Refs", VAR __String = [ProductName] VAR __Refs = CONCATENATEX( SELECTCOLUMNS( FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)), "__Ref",[REF] ), [__Ref],"|" ) RETURN [__Refs] ) VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|") VAR __Count = LEN(__AllRefs) - LEN(SUBSTITUTE(__AllRefs,"|","")) + 1 VAR __AllRefsTable = ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Ref",PATHITEM(__AllRefs,[Value],TEXT) ) RETURN COUNTROWS(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])))

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 17:24:54 GMT

Greg_Deckler ,

Ok now we soooo close !!

I get the correct result for all product individually AND with multiple selections except for a product whose result must be 0, for this one I have 1.

And therefore the total shows me 1446 instead of 1445.

I double-double checked and it should be 1445 - I don't have any visit for the product where I get 1.

I'm sorry I'm completely lost in your formula (way too complex for me) so I can't seem to figure out when the 1 can slip in instead of counting blank or 0.

Thank you in advance for your help 🙂

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 18:40:11 GMT

DIACHROMA OK, so I know you said you double-checked but could you check with this measure to see if it returns 1446 or 1445.

Measure = COUNTROWS(DISTINCT(SELECTCOLUMNS('ProdutsOfferedTable',"__Ref",[REF])))

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 18:44:05 GMT

It returns 1445

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 18:48:34 GMT

DIACHROMA OK, well that is odd then. What if you create a new table like this and we can check what is being returned, maybe there is a blank row being returned and we can just filter it out. It's the exact same formula, you just return the __AllRefsTable instead.

New Table = VAR __ProductsToSearchFor = SELECTCOLUMNS('dimProduct',"__ProductName",[ProductName]) // this gets the products selected in the slicer in a table with just ProductName column VAR __Table = ADDCOLUMNS( __ProductsToSearchFor, "__Refs", VAR __String = [ProductName] VAR __Refs = CONCATENATEX( SELECTCOLUMNS( FILTER('ProductsOfferedTable',CONTAINSSTRING([PRODUCT OFFERED],__String)), "__Ref",[REF] ), [__Ref],"|" ) RETURN [__Refs] ) VAR __AllRefs = CONCATENATEX(__Table,[__Refs],"|") VAR __Count = LEN(__AllRefs) - LEN(SUBSTITUTE(__AllRefs,"|","")) + 1 VAR __AllRefsTable = ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Ref",PATHITEM(__AllRefs,[Value],TEXT) ) RETURN __AllRefsTable

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 19:17:31 GMT

It returns this error :

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 19:29:33 GMT

DIACHROMA Well that makes no sense since the length of the text should be the same for the measure! Let's try another route, create this the same measure that returns 1446 but with this in the last RETURN line and maybe put it in a Card visual or something:

CONCATENATEX(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])),[__Ref],",")

What we are looking for is ",," for example or some other value that doesn't look quite right.

Re: Filter a table based on concatenated values

DIACHROMA — Fri, 17 Sep 2021 20:02:25 GMT

Ok ! I did it, it returns Visit IDs in one value. I export the result to excel to check it, I have 1445 IDs in total.

I don't know if it can help, but the 1 which is extra in the total appears when I select a product which should normally show me Blank or 0. Isn't there something in the formula that could make 1 to this product that should not appear?

Re: Filter a table based on concatenated values

Greg_Deckler — Fri, 17 Sep 2021 20:06:40 GMT

DIACHROMA OK, try this as the RETURN then:

COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__AllRefsTable,[__Ref]<>BLANK()),"__Ref",[__Ref])))