How to filter rows with IN operator on subquery that uses GROUP BY HAVING COUNT(*) > 1 (SQL to DAX)

[GabrielVan]

Dear fellow developers,

I am quite new to DAX, but familiar with SQL. So I would like to explain what I want to achieve in DAX, using an SQL example:

SELECT col1, col2, col3

FROM table1

WHERE col1 IN

(SELECT col1

  FROM table1

  GROUP BY col1

  HAVING COUNT(*) > 1)

Thanks in advance!

[v-binbinyu-msft · Accepted solution]

Hi @GabrielVan ,

Please try below steps

1.my test table

Table:

2. create a new table by below dax formula

Table 2 =
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table'[Col1] ),
        FILTER (
            SUMMARIZE ( 'Table', 'Table'[Col1], "Count_Rows", COUNT ( 'Table'[Col2] ) ),
            [Count_Rows] > 1
        )
    )
RETURN
    FILTER ( 'Table', 'Table'[Col1] IN tmp )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like the attached.

I hope the attached file can provide some ideas on how to create a solution for your dataset.

[GabrielVan]

Thank you Jihwan Kim!