Sorting data in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Sorting Sales by Month Name in Calendar Order

You have a table with sales data and a column 'MonthName' containing month names as text (e.g., January, February). You want to create a measure that returns total sales sorted by calendar month order, not alphabetically.

Which DAX expression correctly creates a calculated column to sort 'MonthName' in calendar order?

A MonthSort = RANKX(ALL('Calendar'[MonthName]), [MonthName], , ASC)

B MonthSort = MONTH(DATEVALUE([MonthName] & " 1"))

C MonthSort = IF([MonthName] = "January", 1, IF([MonthName] = "February", 2, IF([MonthName] = "March", 3, 0)))

D MonthSort = SWITCH([MonthName], "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12, 0)

💡 Hint

Think about assigning a number to each month name to represent its calendar order.

Option D uses SWITCH to assign each month name a number from 1 to 12, which can be used to sort months in calendar order. Option D tries to convert text to date but may fail if the locale or format is not recognized. Option D only covers three months and is incomplete. Option D ranks alphabetically, not calendar order.

❓ visualization

intermediate

Sorting a Bar Chart by Total Sales Descending

You created a bar chart showing total sales by product category. By default, the categories are sorted alphabetically. How do you sort the bars from highest to lowest total sales?

Choose the correct step to achieve this in Power BI.

A Click the vertical axis, then select 'Sort by' > 'Total Sales' and choose 'Descending'.

B Change the data type of the category column to numeric to enable sorting by sales.

C Create a new calculated column with sales rank and sort the category by this column.

D Sort the data table in Excel before importing to Power BI.

💡 Hint

Look for sorting options directly on the visual's axis or fields.

Option A is correct because Power BI allows sorting visuals by any measure by clicking the axis and choosing 'Sort by' with direction. Option A is incorrect because changing data type does not affect sorting by measure. Option A is more complex than needed. Option A is unnecessary if sorting can be done inside Power BI.

❓ data_modeling

advanced

Sorting a Text Column by Another Column in Power BI Model

You have a 'Product' table with a 'Category' column (text) and a 'CategorySortOrder' column (number). You want the 'Category' column to be sorted by 'CategorySortOrder' in all visuals.

What is the correct way to set this up in Power BI?

A Rename 'CategorySortOrder' to 'Category' to replace the original column.

B Create a measure that returns 'CategorySortOrder' and use it to sort 'Category'.

C Select the 'Category' column in the model, then use 'Sort by Column' and choose 'CategorySortOrder'.

D Sort the 'Category' column alphabetically and ignore 'CategorySortOrder'.

💡 Hint

Power BI allows sorting one column by another column in the data model.

Option C is correct because Power BI has a 'Sort by Column' feature to sort text columns by numeric columns. Option C is invalid because measures cannot be used to sort columns. Option C would lose original data. Option C ignores the requirement.

🔧 Formula Fix

advanced

Debugging Incorrect Sorting of Weekday Names

You created a calculated column 'WeekdaySort' to sort weekday names in calendar order, but the sorting is incorrect in your report.

Here is the DAX code you wrote:

WeekdaySort = SWITCH([WeekdayName], "Monday", 1, "Tuesday", 2, "Wednesday", 3, "Thursday", 4, "Friday", 5, "Saturday", 6)

Why is the sorting wrong?

A The SWITCH function syntax is incorrect and causes a syntax error.

B The SWITCH statement is missing the 'Sunday' case, so Sunday returns BLANK() and sorts first.

C Weekday names must be numbers, not text, to sort correctly.

D The calculated column must use IF instead of SWITCH for sorting.

💡 Hint

Check if all possible weekday names are covered in the SWITCH statement.

Option B is correct because Sunday is missing from the SWITCH cases, so it returns BLANK(), which sorts before 1. This causes Sunday to appear first instead of last. Option B is wrong because syntax is correct. Option B is false; text can be sorted by numeric columns. Option B is incorrect; SWITCH is appropriate here.

🧠 Conceptual

expert

Understanding Sorting Behavior with Multiple Sort Columns

In Power BI, you want to sort a table visual by two columns: first by 'Region' alphabetically, then by 'Sales' descending within each region.

Which statement best describes how to achieve this sorting?

A Sort the visual first by 'Region' ascending, then hold Ctrl and sort by 'Sales' descending to apply multi-level sorting.

B Power BI does not support multi-column sorting in visuals; you must sort by one column only.

C Create a new calculated column combining 'Region' and 'Sales' and sort by that column.

D Sort the data source table externally before importing to Power BI.

💡 Hint

Try using Ctrl key to add multiple sort levels in the visual.

Option A is correct because Power BI allows multi-column sorting in visuals by sorting one column, then holding Ctrl and sorting another. Option A is complex and unnecessary. Option A is false; multi-column sorting is supported. Option A is not required.

Practice

1. What is the main purpose of sorting data in Power BI reports?

easy

A. To create new columns in the dataset

B. To delete unnecessary data from the report

C. To change data values automatically

D. To organize data so reports are clearer and easier to read

Solution

1. Step 1: Understand sorting purpose

   Sorting arranges data in a specific order to improve readability and clarity.

2. Step 2: Identify correct purpose in options

   Only To organize data so reports are clearer and easier to read describes organizing data to make reports clearer and easier to read.

3. Final Answer:

   To organize data so reports are clearer and easier to read -> Option D

4. Quick Check:

   Sorting = Organizing data for clarity [OK]

Hint: Sorting arranges data to improve report clarity [OK]

Common Mistakes:

• Confusing sorting with deleting data
• Thinking sorting changes data values
• Believing sorting creates new columns

2. Which of the following is the correct way to sort a column by another column in Power BI?

easy

A. Select the column, then use 'Sort by Column' and choose the other column

B. Right-click the column and select 'Delete Column'

C. Drag the column to the report canvas

D. Use 'Sort Ascending' on the other column only

Solution

1. Step 1: Recall sorting by another column method

   In Power BI, to sort a column by another, you select the column and use 'Sort by Column' feature.

2. Step 2: Match method to options

   Select the column, then use 'Sort by Column' and choose the other column correctly describes this process; other options do unrelated actions.

3. Final Answer:

   Select the column, then use 'Sort by Column' and choose the other column -> Option A

4. Quick Check:

   Sort by Column feature = Select the column, then use 'Sort by Column' and choose the other column [OK]

Hint: Use 'Sort by Column' to order one column by another [OK]

Common Mistakes:

• Trying to delete columns instead of sorting
• Dragging columns to canvas does not sort
• Using 'Sort Ascending' alone won't sort by another column

3. Given a table with columns MonthName and MonthNumber, which sorting will display months in calendar order in a visual?

medium

A. Sort MonthName by MonthNumber

B. Sort MonthNumber by MonthName

C. Sort MonthName alphabetically

D. Sort MonthNumber descending

Solution

1. Step 1: Understand sorting for calendar order

   Months should be shown in calendar order, which is numeric order of MonthNumber.

2. Step 2: Choose sorting method

   Sorting MonthName by MonthNumber arranges month names in calendar order.

3. Final Answer:

   Sort MonthName by MonthNumber -> Option A

4. Quick Check:

   MonthName sorted by MonthNumber = calendar order [OK]

Hint: Sort names by numbers for correct calendar order [OK]

Common Mistakes:

• Sorting MonthNumber by MonthName causes wrong order
• Alphabetical sort of MonthName is incorrect calendar order
• Descending MonthNumber reverses calendar order

4. You tried to sort a 'Product Category' column by 'Category ID' but the visual still shows alphabetical order. What is the likely issue?

medium

A. The data model is missing a relationship

B. The 'Category ID' column has duplicate values

C. You did not select the 'Product Category' column before applying 'Sort by Column'

D. You sorted the 'Category ID' column instead of 'Product Category'

Solution

1. Step 1: Understand sorting by column requirement

   To sort one column by another, you must select the column to sort first.

2. Step 2: Identify common mistake

   If the visual still shows alphabetical order, likely the 'Product Category' column was not selected before sorting.

3. Final Answer:

   You did not select the 'Product Category' column before applying 'Sort by Column' -> Option C

4. Quick Check:

   Select column first to sort by another [OK]

Hint: Always select the column to sort before choosing 'Sort by Column' [OK]

Common Mistakes:

• Sorting wrong column by mistake
• Ignoring duplicate values in sort column
• Assuming relationships affect sorting here

5. You have a sales table with a 'Region' column and a separate 'Region Sort Order' table. How do you ensure a bar chart sorts regions by the custom order defined in 'Region Sort Order'?

hard

A. Sort the 'Region Sort Order' column alphabetically

B. Create a relationship between tables and sort 'Region' column by 'Region Sort Order' column

C. Sort the bar chart manually by dragging bars

D. Use a slicer to filter regions in desired order

Solution

1. Step 1: Understand sorting with related tables

   To sort by a custom order in another table, a relationship must exist between the tables.

2. Step 2: Apply sorting by related column

   Sort the 'Region' column by the 'Region Sort Order' column from the related table to enforce custom order in visuals.

3. Final Answer:

   Create a relationship between tables and sort 'Region' column by 'Region Sort Order' column -> Option B

4. Quick Check:

   Relationship + sort by related column = custom order [OK]

Hint: Link tables and sort main column by related order column [OK]

Common Mistakes:

• Sorting order column alphabetically loses custom order
• Trying to drag bars does not persist sorting
• Using slicers does not change sort order