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 a combination could consist of just 1 value or 2, 3, 4, etc.

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

     

3 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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],"-")