Return text values from rows with same ID

[Anonymous]

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

[amitchandak]

@Anonymous , Why you need returnvalue to distinctcount you can do it on ID

You can use concatenatex , if needed

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

[Anonymous]

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

Thanks!

[ChrisMendoza]

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

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]

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

[v-zhenbw-msft · Accepted solution]

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.