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])))
Helper IIHi 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
Community ChampionDIACHROMA 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.
- Greg_Deckler
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])))- DIACHROMA
I can't write :
VAR __Count = COUNTROWS(__AllRefs)
It doesn't allow me to put "__AllRefs" in the COUNTROWS