Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 -
Below is the table that I am using -
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
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?
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.
User | Count |
---|---|
56 | |
33 | |
18 | |
17 | |
15 |
User | Count |
---|---|
94 | |
84 | |
37 | |
22 | |
20 |