DAX - Perform aggregation of group-by in database query

[kiranu]

Hi All,

I'm a newbie in PowerBI and DAX who is struggling to figure out a simple thing. I'm trying to create a measure which does a group-by and sum of the the grouped rows. Please find below my measure:

HitNo = 
var outerGroup =  GROUPBY(DataTableSample,
    DataTableSample[MetricId],
    DataTableSample[AccountInstanceId],
    DataTableSample[EmployeeId],
    "Emp_Score", SUMX(CURRENTGROUP(), DataTableSample[Score]),
    "Emp_Target", SUMX(CURRENTGROUP(), DataTableSample[Target])
    )
  
var instanceGroup = GROUPBY(outerGroup,
    DataTableSample[MetricId],
    DataTableSample[AccountInstanceId],
    "Hit_Count", SUMX(CURRENTGROUP(), if([Emp_Score]>[Emp_Target],1,0))
    )    

return SUMX(instanceGroup, [Hit_Count])    

Though the above measure works fine, I've a problem with the "outerGroup" query which is getting executed in database - the query just selects all the mentioned columns (MetricId, InstanceId, EmployeeId, Score and target) and seems like the SUM operation is performed in-memory. Is there any way to force powerbi to perform the SUM operation in database itself thus reducing the number of rows retrieved from database? 

May be my entire DAX is wrong which is causing the issue, but not sure how to fix it..

Note - I've to go for Direct query as the Import data limit will not suffice our requirements. Also I've got date filters which user could select and the grouping and summation should happen to only those filtered rows (rows within the selected date range).

[kiranu · Accepted solution]

Using SUMMARIZE solved the issue; now the query performs the SUM operation in database inself:

HitNo = 
var employeeGroup = ADDCOLUMNS(
        SUMMARIZE(DataTableSample,
            DataTableSample[MetricId],
            DataTableSample[AccountInstanceId],
            DataTableSample[EmployeeId]),
        "Emp_Score", CALCULATE(SUM(DataTableSample[Score])),
        "Emp_Target", CALCULATE(SUM(DataTableSample[Target]))
        )

var instanceGroup = GROUPBY(employeeGroup,
        DataTableSample[MetricId],
        DataTableSample[AccountInstanceId],
        "Hit_Count", SUMX(CURRENTGROUP(), if([Emp_Score] > [Emp_Target],1,0)))
            
return SUMX(instanceGroup, [Hit_Count])         

Generated SQL query :

SELECT 
TOP (1000001) [t0].[MetricId],[t0].[AccountInstanceId],[t0].[EmployeeId], SUM(CAST([t0].[Target] as BIGINT)) AS [a0],
SUM(CAST([t0].[Score] as BIGINT)) AS [a1]
FROM 
(
(select [$Table].[MetricId] as [MetricId],
    [$Table].[AccountInstanceId] as [AccountInstanceId],
    [$Table].[EmployeeId] as [EmployeeId],
    [$Table].[Date] as [Date],
    [$Table].[Score] as [Score],
    [$Table].[Target] as [Target],
    [$Table].[id] as [id]
from [dbo].[SampleData] as [$Table])
)
 AS [t0]
WHERE 
(
([t0].[Date] IN (CAST( '20180202 00:00:00' AS datetime),CAST( '20180203 00:00:00' AS datetime),CAST( '20180204 00:00:00' AS datetime)))
)

GROUP BY [t0].[MetricId],[t0].[AccountInstanceId],[t0].[EmployeeId] 

which previously when used GROUPBY was just selecting the entire set without grouping and summation in database (which ended up pulling large amount of data)

Though even now, PowerBI executes couple of other unnecessary database queries which I'm not sure why, but I could live with this until this "Reporting Tool" is matured enough to pass the filter values dynamically to stored procedures.

[v-chuncz-msft]

@kiranu,

Glad to hear that. You may help accept solution. Your contribution is highly appreciated.