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

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 )