Best way to sum a column relating to a calculated table?

[D_PBI]

Hi,
I have the below code snippet which filters down the agreement table.

The agreement table joins to the obligation table by agreement[agreementid] <<>> obligation[agreementid]. This is configured in the model.
From the resulting agreement[agreementid] values in the above calculate table function, I need to sum the obligation[amount/unit price] column from the relating table based on that join.

What is the best way (and how) to do this?
1) Add the obligation[amount/unit price] column to the calculatetable function, and then create a measure to sum that column from the calculated table.
OR
2) In the measure to sum the obligation[amount/unit price], should I create the code to join the calculate table and the obligation table together there?

Thanks.

[D_PBI]

@v-mdharahman thanks again for your response.
I've followed your instructions but the outcome is still the same. I'm not receiving the intellisense error now and the measure returns a value, however the value returned is incorrect. The value returned is the same value returned when attempting the DAX in your first response (in your first post). Below is the measure structure having followed your instructions.

I very much appreciate your help and explanations along the way. If you would like to continue to provide suggestions to get this working in DAX, it'll be welcomed. Although, I have built the needed logic in Power Query and loaded that to the PBI model. This now returns the expected figures.

[v-mdharahman · Accepted solution]

Hi @D_PBI,

From what you’ve described, the root cause seems to be that the _TranslationalFunding variable (which is a union of calculated tables) doesn’t pass filter context properly to the obligation table when using TREATAS or KPI-style measures. This is why you're seeing incorrect totals like 15M instead of the expected 39M.

Instead of using TREATAS, try directly filtering the obligation table using a FILTER + IN combination this approach ensures agreement IDs from your virtual table are correctly matched with the obligation table.

You can try this updated measure:

VAR _TranslationalFunding =
-- your existing union of calculated agreement tables

RETURN
CALCULATE(
SUM(obligation[Amount / Unit Price]),
FILTER(
obligation,
obligation[agreementid] IN
SELECTCOLUMNS(_TranslationalFunding, "agreementid", agreement[agreementid])
)
)

This should return the correct figures, including when using slicers like Teams in the visual. Also as you have mentioned the you were able to get a workaround and you issue was resolved, can you please mark the helpful reply as solution or provide your workaround and mark it as solution so that other community members having similar issue can find the solution easily.

Best Regards,

Hammad.

[v-mdharahman]

Hi @D_PBI,
I wanted to quickly follow up on your issue. Was your issue resolved in the end?
If yes, it’d be great if you could share a quick update and accept a solution, so other users can benefit from your experience too.
If you’re still stuck, let us know, we’re more than happy to continue helping you through it.

[v-mdharahman]

Hi @D_PBI,
We noticed there hasn’t been any recent activity on this thread. If your issue is resolved, marking the correct reply as a solution would be a big help to other community members.
If you still need support, just reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[v-mdharahman]

Hi @D_PBI,
I wanted to quickly follow up on your issue. Was your issue resolved in the end?
If yes, it’d be great if you could share a quick update and accept a solution, so other users can benefit from your experience too.
If you’re still stuck, let us know, we’re more than happy to continue helping you through it.

[D_PBI]

@bhanu_gautam  ( @v-mdharahman , anyone) - thanks for your reply. I've implemented what you've suggesed but the figures/measures aren't returning as they should. I shall provide more context in case it is needed.

I have a measure. This measure uses a Switch() statement to determine which figure/result the end user wants to see. Point being, this measure contains a lot of forumulas. Focusing on the relevant, I have the following relationships:
1) Date to Agreement table:
dim[Date] <<>> agreement[Execution Date] (this is the active relationship)
dimDate] <<>> agreement[Received Date[ (this is an inactive relationship)
2) Team to Agreement table:
team[teamid] <<>> user[userid] <<>> agreement[managerid]
3) Agreement to Obligation table:
agreement[agreementid] <<>> obligation[agreementid]

There is a union of calculated tables that contribute to the figure I'm struggling with. This union code is:

The Switch() is then brought into action and the four measures that utilise the above union code is shown below:

Figures 11, 13, and 14 all use the _Translational variable (the union tables code) and each perform a COUNTROWS on fields within the _Translational variable.
The Figure 12, the measure I'm struggling with, needs to sum the obligation[Amount / Unit Price] column. The agreement[agreementid] values returned from the union tables _Translational variable need to be searched for in the obligation[agreementid] table/column and those that match need to be summed.

I've peformed a convoluted test on the raw data through another method, so I know what the correct sum should be. What you kindly provided, and what I've tried separately, is returning the wrong figure(s).

That's why I'm thinking, would it be easier to perform a join to bring in the agreement[Amount / Unit Price] column from the obligation table and into the union calculatetable formular so I can simply sum it from the _Translational variable.
I'm hoping this provides more context to help get me beyond my stuggle??  Thanks.

[v-mdharahman]

Hi @D_PBI,

Thank you for the detailed follow-up. You are right in thinking that the issue lies in how _Translational (your union of filtered agreement tables) is interacting with the obligation table. While the TREATAS() approach seemed logical at first, it doesn't fully carry over the complex filters or relationship overrides (like USERELATIONSHIP) you've applied in _Translational. It only filters by agreementid, so everything else gets lost, which explains why you're seeing unexpected figures.

Instead of bringing the obligation field into the _Translational variable (which isn't ideal since obligation is a related child table and that would complicate row granularity), a better approach is to use a CALCULATE with a FILTER that directly checks if each obligation row matches an agreement ID from _Translational.

Try this measure once:

Figure12_TotalObligation :=
CALCULATE(
SUM(obligation[Amount / Unit Price]),
FILTER(
obligation,
obligation[agreementid] IN
SELECTCOLUMNS(_Translational, "aid", agreement[agreementid])
)
)

This way, you're explicitly filtering the obligation table based on the agreementid values returned by your _Translational logic — and all your existing filters on date, type, status, etc., are respected within _Translational as defined.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[D_PBI]

@v-mdharahman thanks for your response. The logic to your DAX measure makes sense and I understand what it's doing. Unfortunately, it's not producing the correct figures.

I don't know what to suggest to have this moved forward as I feel I've explained all the relevant. I'm not going to be able to provide you with the .pbix file as it contains senstive data.
Is there another approach we can use, another choice of DAX?  Thanks.

[v-mdharahman]

Hi @D_PBI,

As the previous approach using FILTER with SELECTCOLUMNS didn’t return the correct results, I suspect the issue may relate to how context transitions are occurring especially if _TranslationalFunding is constructed using multiple CALCULATETABLE calls with USERELATIONSHIP and possibly UNION. In these cases, filtering downstream (like on obligation) might not fully carry over the context as expected.

Let’s try an alternative that’s often more stable in such situations: using TREATAS, but inside the main measure not isolated like before. Try this:

Figure12_TotalObligation :=
CALCULATE(
SUM(obligation[Amount / Unit Price]),
TREATAS(
VALUES(_TranslationalFunding[agreementid]),
obligation[agreementid]
)
)

This can work better as TREATAS transitions the row context from your _TranslationalFunding variable directly into the obligation table. It keeps the granularity flat and preserves the unique agreement IDs generated by your UNION logic (which already respects date filters, status filters, and type conditions). It also avoids FILTER-based wrapping, which can sometimes lose context or suffer from performance issues on larger models.

Also double-check that the agreementid in _TranslationalFunding doesn’t include blanks or duplicates unless intentional as this could inflate or nullify the final total. You can try wrapping VALUES(...) in a FILTER(..., NOT(ISBLANK(...))) if needed.

Let me know if this gives you more accurate numbers or if they’re still off, we can look into alternate debugging routes like using EXCEPT() to isolate mismatches between _TranslationalFunding and actual obligation[agreementid].

Best Regards,

Hammad.

[D_PBI]

@v-mdharahman thanks for your continued response.
Your most recent suggestion of using TREATAS is the same method as suggested by @bhanu_gautam which didn't work.

It seems, as was the case when trying Bhanu's suggestion, the _TranslationalFunding variable (the union tables) isn't being recognised by the intellisense. In the screenshot below, I've manually typed in what you're describing. As you can see it is underlined in red (indicating an error).

I understand that you're both trying to reference the _TranslationFunding variable as the filtering logic is applied against that variable, but it doesn't seem to be acceptable.

To add further, and this won't help with the code needed but it will indicate how much the figures are incorrect by, the figure/measure we're working on will be placed in a matrix with Teams being in the columns section. By removing the Teams from the visual will give an overall figure (single figure, not split out by Teams).

The overall figure that I am expecting (the correct figure) is 39,872,284.88.
The figure that is returned through your previous suggestion @v-mdharahman (not your most recent suggestion as intellisence won't allow it) is 15,044,174.82.
That's some difference.
As I say, I believe the _TranslationalFunding union code variable is correct/fine as three other measures work off that same variable and they're correct, but those three measures count rows from within the _TranslationalFunding variable. As discussed, it's trying to use the filtered (_TranslationalFunding) agreementid values in the obligation[agreementid] table.

Any further ideas?  I'm puzzled as I wouldn't have thought what I was trying to do is difficult.
Thank you.

[v-mdharahman]

Hi @D_PBI,

Really appreciate the clarity you have provided and you are absolutely right to question the behavior of _TranslationalFunding. Since you've confirmed that_TranslationalFunding is a variable declared within the measure itself (not a physical table or a calculated table in the model) and you're attempting to reference it inside TREATAS(...) or any context outside its own scope It explains why IntelliSense flags it as invalid, it simply doesn't recognize it outside its originating VAR.

So the question becomes how can we use the contents of _TranslationalFunding which works correctly for COUNTROWS as a filter for obligation, without referencing it outside its own scope.

Please try this approach using a nested CALCULATE pattern, which keeps everything within context:

Figure12_TotalObligation :=
VAR _TranslationalFunding =
// Your existing UNION of CALCULATETABLE(...) expressions
-- returns filtered rows from agreement table --

RETURN
CALCULATE(
SUM(obligation[Amount / Unit Price]),
KEEPFILTERS(
TREATAS(
SELECTCOLUMNS(_TranslationalFunding, "agreementid", agreement[agreementid]),
obligation[agreementid]
)
)
)

This works as we define _TranslationalFunding as a variable within the same measure (as you've already done). Also we use SELECTCOLUMNS inside the same scope where _TranslationalFunding exists, this is valid DAX syntax. And wrapping TREATAS(...) inside KEEPFILTERS(...) helps preserve row-level context, especially when the measure is sliced by Teams in the visual (which could otherwise override or reduce the filtering effectiveness).

Best regards,

Hammad.

[v-mdharahman]

Hi @D_PBI,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @D_PBI,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are looking for a way to calculate the sum of two columns from two different table having relationship(agreementid) between them. As @bhanu_gautam already responded to your query, kindly go through the response and check if it solves your issue and then mark the helpful reply as solution so that other community members would find it easily.

I would also take a moment to thank @bhanu_gautam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[bhanu_gautam]

@D_PBI 

Create a measure to filter the agreement table:

DAX
FilteredAgreements :=
CALCULATETABLE(
agreement,
USERELATIONSHIP(_dimDate[Date], agreement[Received Date]),
DATESBETWEEN(agreement[Received Date], _MinDate, _MaxDate),
agreement[Agreement Type (type)] = 100000024
)

Then Create a measure to sum the obligation[amount/unit price] column based on the filtered agreements:

DAX
TotalObligationAmount :=
CALCULATE(
SUM(obligation[amount/unit price]),
TREATAS(
VALUES(FilteredAgreements[agreementid]),
obligation[agreementid]
)
)