Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to count rows with the same values in the column?

Hello, colleagues! 

I have such table:

iD           Action_Type         Data

1              15                       21.01.2018

2              25                       22.01.2018

3              33                       15.01.2018

4              25                       21.01.2018

5              15                       27.01.2018

6              33                       21.01.2018

7              25                       21.02.2018

 

etc.

I have to count the quantity of set where at the same date was happened three types of action (15, 25 and 33).  In this example the rusult is 1 because of 21.01.2018 happened actions 15 (id 1), 25 (id 4) and 33 (id 6).

How can I do that?

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    try this measure

    Measure = 
VAR Dates = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE('Table','Table'[Data]),
        "15",CALCULATE(COUNT('Table'[Action_Type]),'Table'[Action_Type]=15),
        "25",CALCULATE(COUNT('Table'[Action_Type]),'Table'[Action_Type]=25),
        "33",CALCULATE(COUNT('Table'[Action_Type]),'Table'[Action_Type]=33)
        ),
    "CountSet",MIN(MIN([15],[25]),[33])
)
RETURN
SUMX(Dates,[CountSet])

    it counts the occurences of the elements of the set per date

    then picks the minimum of the count for each date
    then sums up the the count numbers

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi, 

     

    A first option would be to create a table with the date and a count of the Action Type. Then you can create a measure checking how many Action Type counts are 3.

     

    Ioana

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      avramioana, thank you!

      I had been going this way before recieved a message from Stacu which I've marked as sulution.

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    try this measure

    Measure = 
VAR Dates = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE('Table','Table'[Data]),
        "15",CALCULATE(COUNT('Table'[Action_Type]),'Table'[Action_Type]=15),
        "25",CALCULATE(COUNT('Table'[Action_Type]),'Table'[Action_Type]=25),
        "33",CALCULATE(COUNT('Table'[Action_Type]),'Table'[Action_Type]=33)
        ),
    "CountSet",MIN(MIN([15],[25]),[33])
)
RETURN
SUMX(Dates,[CountSet])

    it counts the occurences of the elements of the set per date

    then picks the minimum of the count for each date
    then sums up the the count numbers

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Stachu, thanks a lot! That is excatly I need!