Filter a table based on concatenated values

It works !!!!!! 😊

A big thank you Greg_Deckler , you can't imagine how much you helped me today (tonight for me)!

Thank you again and have a great weekend !!!

Pauline 

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])))

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 🙂

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])))

It returns 1445

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

It returns this error : 

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.

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?

DIACHROMA Greg_Deckler Could someone please share sample .pbix for working solution