Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return text values from rows with same ID

Hi,   I want to do a product analysis where I list the frequency of each distinct product combination.  My data looks like this:       I want to return values as in the ReturnValue column. ...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    Hi Anonymous ,

     

    We can create three columns then create a measure to meet your requirement.

     

    1. Create a Contatenatex column, then create a column that ranks in the same Group, at last create a column to combine them.

     

    Column = CONCATENATEX (FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])), 'Table'[Product Type], ", " )
    Rank in same ID = RANKX(FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])),'Table'[Product Type],,ASC,Dense)
    New Product Type = 'Table'[Column] &"-"& 'Table'[Rank in same ID]

     

     

    2. Then we can create a measure to distinct count the New Product Type column.

     

    Distinctcount measure = DISTINCTCOUNT('Table'[New Product Type])

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for your reply,  .How can I do it on ID directly in a measure? Can you please show me the formula?

 

Thanks!

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Anonymous 
    Expanding on what amitchandak has suggested...

    You can create a measure as:

    CONCATENATEX Measure = CONCATENATEX ( Produkt, Produkt[Produkt], ", " )

    To deal with the Total Row you can do something like:

    CONCATENATEX Measure = 
IF (
    ISFILTERED ( Produkt[ID] ),
    CONCATENATEX ( Produkt, Produkt[Produkt], ", " ),
    DISTINCTCOUNT ( Produkt[ID] )
)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you, ChrisMendoza!

       

      Will the DISTINCTCOUNT be able to account for rows with the same text values, but in different order?

       

      Ex:

      ID      Produkt

      1      Banan, Eple

      2      Eple, Banan

       

      This is the identical produkt combination and should be counted as one distinct combination

       

      BR

       

      Lars

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        And I still can't figure out how to genrerate a list of every distinct product mix and then count the number of customer ID's than have each distinct mix.

         

        Customer ID  Product Type     New COLUMN

         

        1                   Eple                     Eple, Banan

        1                   Banan                  Eple, Banan

        2                   Pære                    Pære, Eple

        2                   Eple                     Pære, Eple

        3                   Kiwi                     Kiwi, Pære, Banan

        3                   Pære                   Kiwi, Pære, Banan

        3                   Banan                  Kiwi, Pære, Banan

         

         

        I want to retun the values as in "New COLUMN". Any advice?

         

        BR

         

        Lars