Forum Discussion
Filter a table based on concatenated values
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
DIACHROMA OK, try this as the RETURN then:
COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__AllRefsTable,[__Ref]<>BLANK()),"__Ref",[__Ref])))
21 Replies
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,"")) ) / __LengthIf you put your ProductName in a table along with this measure, should work.
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
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.
