How to count row combinations based on common value

[EVG-Questions]

I'm trying to find the frequency of combinations that occur per an ID value. Example given here:

The problem is that the number of rows that could make up a combination is variable, meaning a combination could consist of just 1 value or 2, 3, 4, etc.

[Samarth_18 · Accepted solution]

Hi @EVG-Questions ,

You could create a column as below:-

Column = CONCATENATEX(FILTER(ALL('Table (2)'),[ID]=EARLIER('Table (2)'[ID])),[Cat1],"")

Then create a measure as below:-

Measure = 
COUNTROWS (
    FILTER (
        VALUES ( 'Table (2)'[Id] ),
        CALCULATE ( COUNT ( 'Table (2)'[Column]) >= DISTINCTCOUNT ('Table (2)'[Column]) )
    )
)

Output:-

Refer a attached file.

BR,

Samarth

[Anonymous]

Hi,

How can I get distinct value for the output? For example, A A B B = A B, A B B C C C = A B C.

I tried to add the distinct function but an error occured. "Too many arguments were passed to the DISTINCT function. The maximum argument count for the function is 1."

Thank you so much.

[Anonymous]

Hi,

I have this, but if the same CAT1 is more than one time fo an ID it is not listed.

Colonne = VAR IDNBR=Feuil25[ID]

var _step1=filter(SUMMARIZE(Feuil25,Feuil25[ID],Feuil25[Cat1]),Feuil25[ID]=IDNBR)

return

concatenatex(_step1,Feuil25[Cat1],"-")