Unpivoting columns in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX to Unpivot Sales Data by Region

You have a table SalesData with columns: Product, North, South, East, West. Each region column contains sales numbers.

Which DAX expression correctly unpivots the regional sales into two columns: Region and Sales?

A UNPIVOT(SalesData, SalesData[North], SalesData[South], SalesData[East], SalesData[West])

B UNION(SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "North", "Sales", SalesData[North]), SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "South", "Sales", SalesData[South]), SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "East", "Sales", SalesData[East]), SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "West", "Sales", SalesData[West]))

C SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", {"North", "South", "East", "West"}, "Sales", {SalesData[North], SalesData[South], SalesData[East], SalesData[West]})

D SUMMARIZE(SalesData, SalesData[Product], "Region", SalesData[North] & SalesData[South] & SalesData[East] & SalesData[West])

💡 Hint

Think about stacking rows for each region using SELECTCOLUMNS and UNION.

Option B uses UNION and SELECTCOLUMNS to create a new table with one row per product-region combination, effectively unpivoting the columns.

Options B, C, and D are invalid DAX syntax or do not perform unpivoting.

❓ visualization

intermediate

Best Visualization After Unpivoting Sales Data

After unpivoting regional sales data into Region and Sales columns, which visualization best shows sales comparison across regions for each product?

A Pie chart showing total sales per product

B Line chart with Region on X-axis and Product as legend

C Stacked bar chart with Product on X-axis, Sales on Y-axis, and Region as legend

D Table showing Product, Region, and Sales columns

💡 Hint

Think about comparing sales by region side by side for each product.

A stacked bar chart allows easy comparison of sales by region within each product category.

Pie charts and line charts are less effective for this multi-category comparison. A table is less visual.

❓ data_modeling

advanced

Data Model Design for Unpivoted Sales Data

You have unpivoted sales data with columns: Product, Region, and Sales. You want to create a data model that supports filtering by region and product category.

Which modeling approach is best?

A Create separate dimension tables for Product and Region, and link them to the unpivoted sales fact table

B Keep all data in a single flat table without relationships

C Create a dimension table only for Product and keep Region as a column in the fact table

D Create a dimension table only for Region and keep Product as a column in the fact table

💡 Hint

Think about how to enable flexible filtering and reduce data duplication.

Option A follows best practices by separating dimensions and facts, enabling efficient filtering and scalability.

Options A, B, and D limit filtering flexibility or cause data redundancy.

🔧 Formula Fix

advanced

Identify the Error in Unpivoting DAX Expression

Consider this DAX expression intended to unpivot sales columns:

UNION(
  SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "North", "Sales", SalesData[North]),
  SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "South", SalesData[South]),
  SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "East", "Sales", SalesData[East])
)

What is the error in this expression?

A SalesData table is not referenced correctly

B UNION cannot combine more than two tables

C SELECTCOLUMNS cannot be used with string literals

D Missing the "Sales" column name in the second SELECTCOLUMNS call

💡 Hint

Check the column names in each SELECTCOLUMNS call carefully.

The second SELECTCOLUMNS call lacks the "Sales" column name before SalesData[South], causing a syntax error.

UNION can combine multiple tables, and string literals are valid in SELECTCOLUMNS.

🧠 Conceptual

expert

Why Unpivoting Columns Improves BI Analysis

Why is unpivoting columns into rows often recommended before creating reports and dashboards in BI tools?

A It converts wide data into a normalized format, enabling easier aggregation, filtering, and visualization

B It reduces the total number of rows, improving performance

C It automatically creates calculated columns for each original column

D It eliminates the need for dimension tables in the data model

💡 Hint

Think about how BI tools handle data for filtering and grouping.

Unpivoting transforms data from wide to long format, which is easier for BI tools to aggregate and filter by categories.

It usually increases rows, does not create calculated columns automatically, and does not remove the need for dimension tables.