Forum Discussion

EVG-Questions's avatar
EVG-Questions
Frequent Visitor
4 years ago
Solved

How to count row combinations based on common value

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...
Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

     

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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.