Why DAX powers calculations in Power BI - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why is DAX preferred for calculations in Power BI?

Power BI uses DAX for calculations. What is the main reason DAX is preferred over other languages for this?

A DAX is a general-purpose programming language used for web development.

B DAX is optimized to work with relational data models and supports context-aware calculations.

C DAX is only used for data import and does not support calculations.

D DAX requires no understanding of data relationships to create calculations.

💡 Hint

Think about how Power BI handles data relationships and filtering.

DAX is designed to work with tables and relationships in Power BI. It understands filter context and row context, enabling dynamic and efficient calculations.

❓ dax_lod_result

intermediate

Calculate Total Sales with Filter Context

Given a Sales table with columns: Product, Region, and SalesAmount, what is the result of this DAX measure when filtered by Region = 'West'?

Sales West = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "West")

Sales table data:

Product | Region | SalesAmount
A       | West   | 100
B       | East   | 200
C       | West   | 150
D       | North  | 50

Power BI

Sales West = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "West")

A 100

B 500

C 250

D Syntax Error

💡 Hint

Add only sales where Region is West.

The measure sums SalesAmount only for rows where Region is West: 100 + 150 = 250.

🔧 Formula Fix

advanced

Identify the error in this DAX measure

What error does this DAX measure produce?

Average Sales = AVERAGE(Sales[SalesAmount], Sales[Region] = "East")

A Returns average sales for East region correctly

B Returns total sales instead of average

C Runtime error due to missing table reference

D Syntax Error due to incorrect use of AVERAGE function arguments

💡 Hint

Check the syntax of AVERAGE function and how filters are applied.

AVERAGE function takes only one column argument. Filters must be applied using CALCULATE or FILTER functions, not as a second argument.

❓ visualization

advanced

Best visualization for showing sales trends over time

You want to show how sales change month by month for different product categories. Which visualization type is best suited for this in Power BI?

A Line chart with Product Category on legend and Month on axis

B Pie chart showing total sales per product category

C Table showing raw sales data

D Stacked bar chart showing sales by region only

💡 Hint

Think about how to show trends over time clearly.

Line charts are ideal for showing trends over time with multiple categories distinguished by lines.

❓ data_modeling

expert

Handling many-to-many relationships in Power BI data model

You have two tables: Customers and Products. Customers can buy multiple products, and products can be bought by multiple customers. How should you model this many-to-many relationship in Power BI?

A Create a bridge table (fact table) with CustomerID and ProductID to connect both tables.

B Directly create a one-to-one relationship between Customers and Products tables.

C Merge Customers and Products tables into one table to avoid relationships.

D Use only one table and ignore the relationship.

💡 Hint

Think about how to represent many-to-many relationships properly.

A bridge table with CustomerID and ProductID allows Power BI to model many-to-many relationships correctly and enables accurate filtering and aggregation.