Forum Discussion

DIACHROMA's avatar
DIACHROMA
Icon for Helper II rankHelper II
5 years ago
Solved

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 C...
DAX
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    DIACHROMA OK, try this as the RETURN then:

    COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__AllRefsTable,[__Ref]<>BLANK()),"__Ref",[__Ref])))
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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.