Group by and Having

[Anonymous]

Hello All,

I have a sql query, which I need to mimic in PowerBI.

SELECT

COUNT(*), EMP_NAME, EMP_ID, YEAR, MONTH

FROM

EMPLOYEE

GROUP BY

EMP_NAME, EMP_ID, YEAR, MONTH

HAVING

COUNT(*) > 1

I have imported the table EMPLOYEE into Power BI.

Is it possible to do above query in Power BI

Creating a view in the DB is not an option.

Thanks in Advance.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Since you have imported the source EMPLYEE table into Power BI, you can apply filter to it via creating a calculated table with the same logic of above SQL query.

Filter Employee =
FILTER (
    SUMMARIZE (
        EMPLOYEE,
        EMPLOYEE[Emp_Name],
        EMPLOYEE[EMP_ID],
        EMPLOYEE[YEAR],
        EMPLOYEE[MONTH],
        "Count records", COUNTROWS ( EMPLOYEE )
    ),
    [Count records] > 1
)

Besides, when you create the connection string, you can type above SQL Query so that you don't need to load all data records into Power BI.

Best regards,

Yuliana Gu

[Thejeswar · Accepted solution]

@Anonymous and @v-yulgu-msft

Additionaly, Instead of creating a summarize table using DAX, we can also achieve this using Group by Option in Power Query

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Since you have imported the source EMPLYEE table into Power BI, you can apply filter to it via creating a calculated table with the same logic of above SQL query.

Filter Employee =
FILTER (
    SUMMARIZE (
        EMPLOYEE,
        EMPLOYEE[Emp_Name],
        EMPLOYEE[EMP_ID],
        EMPLOYEE[YEAR],
        EMPLOYEE[MONTH],
        "Count records", COUNTROWS ( EMPLOYEE )
    ),
    [Count records] > 1
)

Besides, when you create the connection string, you can type above SQL Query so that you don't need to load all data records into Power BI.

Best regards,

Yuliana Gu