topic Re: No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER in DAX Commands and Tips

No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER

Mikeincairns — Mon, 19 Feb 2024 03:17:42 GMT

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......

Re: No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER

lbendlin — Sun, 18 Feb 2024 14:29:48 GMT

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

Re: No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER

Jihwan_Kim — Sun, 18 Feb 2024 17:32:55 GMT

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] )

Re: No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER

Mikeincairns — Mon, 19 Feb 2024 03:12:47 GMT

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???

Re: No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER

Mikeincairns — Mon, 19 Feb 2024 03:15:33 GMT

Thanks Ibendlin. So many functions !!!
I have a (sort of) working solution CALCULATE(SUM(FILTER
And Jhiwan_Kim sugested a SUMX(FILTER IN VALUES
I will read about TREATAS and see how that might help.

Re: No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER

Jihwan_Kim — Mon, 19 Feb 2024 04:41:06 GMT

Hi,

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

Re: No SUMIF in DAX. Please help with this SUM/LOOKUP/FILTER

Mikeincairns — Sun, 25 Feb 2024 06:39:00 GMT

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])) ) )