Categories: DAX

Tags:

Health Insurance Records Data – 10 Advanced Power BI DAX Questions (With Solutions)

Introduction

In this advanced DAX practice session based on the Health Insurance Records dataset, we are moving beyond basic aggregation and stepping into analytical thinking. As data professionals, our goal is not just to calculate totals and averages, but to understand patterns, risk segments, and behavioral insights hidden within the data. Using Insurance as our primary table and Insurance_Cost as the target variable, we are applying powerful DAX functions like SUMX, AVERAGEX, CALCULATE with multiple filters, calculated columns, and calculated tables.

These functions are not merely formulas — they represent how real-world business intelligence models are built. In insurance analytics, we often need to segment customers by health indicators, lifestyle habits, and demographic factors. By creating numerical and categorical calculated columns, we transform raw data into structured intelligence. By using SUMMARIZE, we prepare aggregated views for reporting and strategic decisions.

This exercise strengthens your ability to think like a BI developer rather than just a dashboard creator. When you understand row context, filter context, and context transition, you gain control over the model. That is the difference between a beginner and a professional Power BI analyst.

✅ 1️⃣ Using SUMX

Question:

Calculate the Total Insurance Cost adjusted by BMI risk factor, where BMI > 30 adds 10% extra cost.

Measure:

Adjusted Insurance Cost =
SUMX(
    SupplyChainData,
    IF(
        insurance[BMI] > 30,
        insurance[Insurance_Cost] * 1.10,
        insurance[Insurance_Cost]    )
)

👉 SUMX iterates row by row and applies conditional logic.

✅ 2️⃣ Using AVERAGEX

Question:

Calculate the Average Insurance Cost per Age Unit (Insurance_Cost divided by Age).

Measure:

Average Cost per Age =
AVERAGEX(
    insurance,
    insurance[Insurance_Cost] / SupplyChainData[Age])

👉 AVERAGEX evaluates expression row-wise then averages.

✅ 3️⃣ CALCULATE with 2 Filters

Question:

Calculate Total Insurance Cost for Male applicants who exercise Moderately.

Male Moderate Exercise Cost =
CALCULATE(
    SUM(insurance[Insurance_Cost]),
    insurance[Gender] = "Male",
    insurance[Exercise] = "Moderate"
)

✅ 4️⃣ CALCULATE with 2 Filters (Health Condition Based)

Question:

Total Insurance Cost where Alcohol = Daily AND Cholesterol_Level = “200-225”

High Risk Cost =
CALCULATE(
    SUM(insurance[Insurance_Cost]),
    insurance[Alcohol] = "Daily",
    insurance[Cholesterol_Level] = "200-225"
)

✅ 5️⃣ SUMX with Conditional Risk Segmentation

Question:

Calculate Total Insurance Cost only for High BMI & No Exercise group.

High BMI No Exercise Cost =
SUMX(
    FILTER(
        insurance,
        insurance[BMI] > 30 &&
        insurance[Exercise] = "No"
    ),
    insurance[Insurance_Cost])

✅ 6️⃣ AVERAGEX with Derived Calculation

Question:

Average Insurance Cost per Dependent.

Average Cost per Child =
AVERAGEX(
    insurance,
    DIVIDE(
        insurance[Insurance_Cost],
        insurance[Children]    )
)

✅ 7️⃣ Numerical Calculated Column

Question:

Create a new column that calculates BMI Risk Score

Formula:

BMI < 18.5 → 1
18.5–25 → 2
25–30 → 3
30 → 4

BMI Risk Score =
SWITCH(
    TRUE(),
    insurance[BMI] < 18.5, 1,
    insurance[BMI] <= 25, 2,
    insurance[BMI] <= 30, 3,
    4
)

👉 This is a Numerical Calculated Column

✅ 8️⃣ Categorical Calculated Column

Question:

Create Age Group category.

Age Group =
SWITCH(
    TRUE(),
    insurance[Age] < 30, "Young",
    insurance[Age] < 50, "Middle Age",
    "Senior"
)

👉 This is a Categorical Calculated Column

✅ 9️⃣ Advanced CALCULATE + FILTER

Question:

Calculate Average Insurance Cost for people with BMI > 30 AND Age > 40.

High BMI 40+ Avg Cost =
CALCULATE(
    AVERAGE(insurance[Insurance_Cost]),
    FILTER(
        insurance,
        insurance[BMI] > 30 &&
        insurance[Age] > 40
    )
)

✅ 🔟 Calculated Table using SUMMARIZE

Question:

Create a summarized table showing:

Gender
Exercise
Total Insurance Cost
Average Insurance Cost

Insurance Summary Table =
SUMMARIZE(
    insurance,
    insurance[Gender],
    insurance[Exercise],
    "Total Cost", SUM(insurance[Insurance_Cost]),
    "Average Cost", AVERAGE(insurance[Insurance_Cost])
)

👉 This creates a new table in your model.

🎯 What You Practiced Here

Concept	Covered
SUMX	✔
AVERAGEX	✔
CALCULATE (2 filters)	✔
FILTER inside CALCULATE	✔
Numerical Calculated Column	✔
Categorical Calculated Column	✔
SUMMARIZE Table	✔

🚀 Why These Questions Matter

These are not textbook questions.
These simulate:

Insurance risk modeling
Healthcare analytics
Financial BI reporting
Interview-level DAX challenges

If you can solve these confidently,
you are beyond beginner level.

Conclusion

In this entire DAX practice framework built on the Health Insurance Records dataset, the real objective was not just writing formulas — it was developing analytical maturity. Many learners make the mistake of focusing only on visuals, colors, and layouts in Power BI. But the real strength of Power BI lies in the data model and the logic you build using DAX. When you understand how SUMX iterates row by row, how AVERAGEX evaluates expressions dynamically, and how CALCULATE modifies filter context with multiple conditions, you begin to think like a data strategist rather than a report designer.

By implementing CALCULATE with two filters, we simulated real-world business queries such as identifying high-risk groups, analyzing behavior-based cost variations, and isolating specific demographic segments. These are exactly the kinds of insights insurance companies, healthcare firms, and financial institutions depend on to make pricing and risk decisions. This is not theoretical learning — this is practical, applied business intelligence.

Creating a numerical calculated column such as a BMI Risk Score demonstrates how raw numerical values can be converted into structured scoring systems. This is the foundation of predictive modeling and segmentation logic. Similarly, creating a categorical column like Age Group allows better slicing, comparison, and storytelling in dashboards. Data becomes meaningful only when categorized intelligently.

The calculated table using SUMMARIZE represents another important concept — shaping aggregated reporting layers within the model itself. Instead of relying only on visuals to aggregate data, we create summarized views at the model level. This improves performance, clarity, and analytical flexibility. In enterprise projects, summarized tables are often used for executive dashboards, performance reviews, and strategic reporting.

The most important takeaway from this exercise is understanding context — row context and filter context. Many DAX errors happen because professionals memorize formulas without understanding evaluation context. When you truly understand how DAX evaluates expressions, you can solve any business problem confidently. Whether it is risk profiling, premium optimization, or customer segmentation, the same core DAX principles apply.

As I always emphasize, Power BI is not about dragging fields into charts. It is about thinking logically, structuring data correctly, and applying intelligent calculations. Mastering DAX transforms you from a report creator into a BI solution architect.

If you practice these concepts deeply, experiment with variations, and build real scenarios around them, you will be fully prepared for advanced analytics roles and real-world consulting projects.

—
Ankit Srivastava
Digital Project Manager | IT Trainer | Data Analytics Mentor