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

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 :

 

REFOFERRED PRODUCTS
001AAA
002AAA BBB
003BBB

 

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