PostgreSQL to Power BI Dax-CodePudding

I am currently trying to convert a SQL-command to Power-BI DAX, because I need this to be a calculated table. The imported data is really big and calculating on the result of a query is required.

In this command, grouping, filtering and adding columns with conditions is required and I am not sure how to nest those commands.

To summarize my intentions: I am trying to group my result by c1, c2 and c3, add a column "Start" with the MIN datetime of the grouped entries, add another column with the MAX datetime of the grouped entries as "End", count the number of entries per group and count the number of entries per group with a condition. At the end I am filtering the whole query on another column by terminating false states with state >=0.

SELECT c1, c2, c3, MIN(datetime) AS Start, MAX(datetime) AS End, COUNT(*) AS NewColumn1, COUNT(*) FILTER (WHERE state=1) AS NewColumn2
FROM table
WHERE state>= 0
GROUP BY c1, c2, c3;

CodePudding user response：

You can try this:

CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'table'
            ,'table'[c1],'table'[c2],'table'[c3]
        )
        ,"Start", CALCULATE(MIN('table'[datetime]))
        ,"End", CALCULATE(MAX('table'[datetime]))
        ,"NewColumn1", CALCULATE(COUNTROWS('table'))
        ,"NewColumn2", CALCULATE(
                            COUNTROWS('table')
                           --,ALL('table')
                           ,'table'[state]=1
                       )
    )
    ,'table'[state]>= 0
)