Calculating aggregates in DirectQuery mode

[trevorloflin]

I'm new to PowerBI and am trying to do something that seems very simple but can't figure it out.

I'm working with two SQL Server tables in DirectQuery mode. I'm trying to create a table visualization based on one of the tables that uses an aggregate from the second table, but the aggregate needs to be added to a value from the first table. Here's a simplified example:

SQL Tables

Foo
===
ID | Name | FooValue
---------------------
1  | Joe  | 19
2  | Bob  | 23

Bar
===
ID | FooID | BarValue
----------------------
1  | 1     | 5
2  | 1     | 7
3  | 2     | 1

Visualization Table (expected)

Name | TotalValue
-------------------
Joe  | 31
Bob  | 24

Where there is a one-to-many relationship between Foo and Bar and "TotalValue" is calculated for each row as the "FooValue" for that row plus the sum of the related "BarValue"s.

When I try to create a calculated column ("TotalValue = Foo[FooValue] + SUM(Bar[BarValue]") it won't let me, saying, "Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models". I understand that there are limitations on DirectQuery models for performance reasons, but surely this is a very simple, performant query and there must be a way to do it. I could easily write a SQL query to do it:

SELECT Foo.Name, Foo.FooValue + SUM(Bar.BarValue) AS TotalValue
FROM Foo
INNER JOIN Bar on Bar.FooID = Foo.ID
GROUP BY Foo.Name, Foo.FooValue

So how do I do it in PowerBI?

[v-juanli-msft · Accepted solution]

Hi @trevorloflin 

Create relationship as below

Create a measure

Measure = SUM(foo[foovalue])+SUM(bar[barvalue])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[trevorloflin]

Thanks! I was missing that a measure takes the context of the row it's in.