Forum Discussion

DIACHROMA's avatar
DIACHROMA
Icon for Helper II rankHelper II
4 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
4 years ago

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's avatar
DIACHROMA
Icon for Helper II rankHelper II
4 years ago

I can't write :

  VAR __Count = COUNTROWS(__AllRefs)

It doesn't allow me to put "__AllRefs" in the COUNTROWS

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    DIACHROMA My bad:

    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 - LEN(SUBSTITUTE(__AllRefs,"|","")) + 1
  VAR __AllRefsTable = 
    ADDCOLUMNS(
      GENERATESERIES(1,__Count,1),
      "__Ref",PATHITEM(__AllRefs,[Value],TEXT)
    )
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__AllRefsTable,"__Ref",[__Ref])))
    • DIACHROMA's avatar
      DIACHROMA
      Icon for Helper II rankHelper II
      4 years ago

      Sorry me again... In the VAR __Count I can't put __Len as it doesnt exist earlier as a var

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        4 years ago

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