cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Hi everyone.  I'm really hoping for a simple and logical solution please.
I am from the Excel world where this would be an easy SUMIF formula, but I am stumped in DAX.  Not sure the best function to apply.
I have two tables but they CANNOT be related in the PBI data model (due to other conflicting relationships).
I think the diagram explains everything......

1 ACCEPTED SOLUTION
Frequent Visitor

Jihwan_Kim's solution was a great help, but created a measure.  This was no good because I needed to do further calculateions and required a calculated column.
For others that may need help with SUMIF in DAX, this is what worked in the end.....
Calculated Column:

``````Expense = CALCULATE(
SUM(tblExpenses[Amount]),
FILTER(
tblExpenses,
tblActivities[ActivityId] = CALCULATE(VALUES(tblExpenses[ActivityID]))
)
)``````

6 REPLIES 6
Frequent Visitor

Jihwan_Kim's solution was a great help, but created a measure.  This was no good because I needed to do further calculateions and required a calculated column.
For others that may need help with SUMIF in DAX, this is what worked in the end.....
Calculated Column:

``````Expense = CALCULATE(
SUM(tblExpenses[Amount]),
FILTER(
tblExpenses,
tblActivities[ActivityId] = CALCULATE(VALUES(tblExpenses[ActivityID]))
)
)``````

Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

``````Expense measure: =
SUMX (
FILTER ( expenses, expenses[activity_id] IN VALUES ( activities[activity_id] ) ),
expenses[amount]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Thanks so much for the suggestion Jhiwan_Kim.  And a big thank you for the sample file.  You are amazing 🙂
From your file - ultimetly, I want to add Cost_01 + Cost_02 + Expense.
I think this is not possible when we use a Measure.  Perhaps there is another solution to get the Expense using a Calculated Column rather than a Measure???

Super User

Hi,

I am not 100% sure if I understood your question correctly, but please try something like below. It is writing a measure.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

Read about TREATAS. It allows you to carry filters between unrelated tables.

Frequent Visitor

Thanks Ibendlin.  So many functions !!!
I have a (sort of) working solution CALCULATE(SUM(FILTER
And Jhiwan_Kim sugested a SUMX(FILTER IN VALUES