topic Calculate Sum and Filter with IF conditions in DAX Commands and Tips

Calculate Sum and Filter with IF conditions

Pdawar — Mon, 11 Dec 2023 10:22:56 GMT

Hi,

I have a monthly trend table where I need to calculate the sum of patients with few filters and conditions applied. See the below measure -

Patients_Adj =

var test = CALCULATE(SUM(Patients[Patients]),Patients[TYPE] = "Paid", Patients[PRODUCT_FK] = "Product A")

return

IF(MAX('Date'[Month])>=[max_Date],test,0)

This measure is giving correct output at row level, but totals are incorrect because this is doing the aggregation first and then applying the return condition. Is there a way I can re-write this measure so that conditions are applied first and then the sum aggregation happens. This is important because I need to calculate the cumulative number on top of this measure so that totals has to be accurate.

Below is the table that I am using -

Thanks!

Re: Calculate Sum and Filter with IF conditions

123abc — Mon, 11 Dec 2023 12:03:57 GMT

Certainly! It looks like you want to calculate the sum of patients with specific filters and conditions, and you want these conditions to be applied before aggregation to get accurate totals. To achieve this, you can use the FILTER function along with SUMX instead of CALCULATE. Here's a modified version of your measure:

Patients_Adj =
SUMX (
FILTER (
Patients,
Patients[TYPE] = "Paid" &&
Patients[PRODUCT_FK] = "Product A" &&
'Date'[Month] >= [max_Date]
),
Patients[Patients]
)

This measure uses the FILTER function to create a table where the specified conditions are met, and then uses SUMX to iterate over that table and calculate the sum of patients. This way, the conditions are applied before the aggregation.

Make sure to replace [max_Date] with the appropriate reference to the maximum date you're comparing against. This measure should provide accurate totals because it applies the conditions at the row level before summing up the values.

Re: Calculate Sum and Filter with IF conditions

Pdawar — Mon, 11 Dec 2023 12:58:25 GMT

I am not sure how to apply this because it involves applying the if condition as well. Also, the filter that we need to apply are from different tables as the data model is star schema. If we go ahead with this measure, how can we apply filter conditions from different tables and if conditions?

Re: Calculate Sum and Filter with IF conditions

Anonymous — Wed, 13 Dec 2023 07:53:45 GMT

Hi Pdawar,

It sounds like a common multiple level aggregation calculate requirement in Dax. You can refer to the Greg’s blog to know how to handle this scenario: (SUMMARIZE function and iterator aggregation functions)

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng