Forum Discussion
Helper IIFilter a table based on concatenated values
DIACHROMA OK, try this as the RETURN then:
COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__AllRefsTable,[__Ref]<>BLANK()),"__Ref",[__Ref])))
Community ChampionDIACHROMA 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.
- DIACHROMA
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
- Greg_Deckler
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.
- DIACHROMA
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.
- Greg_Deckler
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:
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, "__Total", VAR __String = [ProductName] VAR __Strings = CONCATENATEX('ProductsOfferedTable',[PRODUCT OFFERED],", ") VAR __Length = LEN(__String) RETURN ( LEN(__Strings) - LEN(SUBSTITUTE(__Strings,__String,"")) ) / __Length ) RETURN SUMX(__Table,[__Total])- DIACHROMA
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.
