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

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?

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

DIACHROMA OK, try this as the RETURN then:

COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__AllRefsTable,[__Ref]<>BLANK()),"__Ref",[__Ref])))
  • DIACHROMA's avatar
    DIACHROMA
    Icon for Helper II rankHelper II
    4 years ago

    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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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