Forum Discussion

micromilua's avatar
micromilua
Regular Visitor
6 years ago
Solved

select count >1 group by multiple column question

Hi

 

I have a question about table query

 

the table is 

 

Table1

id column1  column2

1    a    1

2    a    1

3    b    2

4    b    3

5    c    4

6    d   5

7    e    6

 

I want to do 

select column1,count(id) as count1 from table1 group by column1,column2 having count1>2

 

I haved try

 

NewTable =
SUMMARIZE (
table1,
table1[column1 ],
table1[column2 ],
"id", COUNT ( table[id] )>2
)

but not work

pls help me , thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi micromilua ,

     

    Please check the measure below.

    NewTable =
DISTINCT (
    FILTER (
        SELECTCOLUMNS (
            'Table',
            "column1", 'Table'[column1],
            "count1", CALCULATE (
                COUNT ( 'Table'[id] ),
                ALLEXCEPT ( 'Table', 'Table'[column1], 'Table'[column2] )
            )
        ),
        [count1] >= 2
    )
)

    Result would be shown as below.

     

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi micromilua ,

     

    Please check the measure below.

    NewTable =
DISTINCT (
    FILTER (
        SELECTCOLUMNS (
            'Table',
            "column1", 'Table'[column1],
            "count1", CALCULATE (
                COUNT ( 'Table'[id] ),
                ALLEXCEPT ( 'Table', 'Table'[column1], 'Table'[column2] )
            )
        ),
        [count1] >= 2
    )
)

    Result would be shown as below.

     

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • micromilua's avatar
      micromilua
      Regular Visitor
      6 years ago

      Dear Sir

       

      Thanks for your help, I provide another method

       

      table2 = 
FILTER(SUMMARIZE('table', ROLLUP(ROLLUPGROUP(('table'[column1]),'table'[column2])),
"count2",COUNT('table'[id]) ),[count2]>1 )