Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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. After I get the ReturnValue I can do a simple DISTINCTCOUNT.

 

Is there a way I do this in a measure instead of creating a calculated column?

 

Much appreciated,

 

Lars

 

 

 
 

 

 

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.

6 Replies

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