Parameters in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Parameters in Power BI

What is the main purpose of using parameters in Power BI reports?

A To create complex calculated columns that update automatically

B To permanently store data in the Power BI dataset for faster queries

C To automatically refresh data sources on a schedule

D To allow users to dynamically change filter values or inputs without editing the report directly

💡 Hint

Think about how parameters help users interact with reports without changing the underlying data.

Parameters let report users input or select values that control filters or calculations dynamically, making reports flexible and interactive.

❓ dax_lod_result

intermediate

DAX Measure Using Parameter for Sales Threshold

Given a parameter named SalesThreshold with value 10000, what is the result of this DAX measure?

High Sales Count = CALCULATE(COUNTROWS(Sales), Sales[Amount] > SalesThreshold)

Assuming the Sales table has 3 rows with Amounts: 8000, 12000, 15000.

A 0

B 2

C 1

D 3

💡 Hint

Count rows where Amount is greater than 10000.

Only the rows with 12000 and 15000 are greater than 10000, so count is 2.

❓ visualization

advanced

Using Parameters to Create Dynamic Titles

You want to create a dynamic report title that changes based on a parameter SelectedYear. Which approach correctly uses the parameter in a DAX measure for the title?

A Report Title = "Sales Report for " & SelectedYear

B Report Title = CONCATENATE("Sales Report for ", SelectedYear)

C Report Title = "Sales Report for " & FORMAT(SelectedYear, "0")

D Report Title = "Sales Report for " + SelectedYear

💡 Hint

Remember to convert numeric parameters to text before concatenation.

Using FORMAT converts the numeric parameter to text, allowing safe concatenation with strings.

🎯 Scenario

advanced

Scenario: Filtering Data with Parameters in Power Query

You want to filter a table in Power Query based on a parameter RegionParam. Which M code snippet correctly applies this filter?

A FilteredTable = Table.SelectRows(Source, each [Region] = RegionParam)

B FilteredTable = Table.SelectRows(Source, each RegionParam = [Region])

C FilteredTable = Table.SelectRows(Source, each [Region] <> RegionParam)

D FilteredTable = Table.SelectRows(Source, each Text.Contains([Region], RegionParam))

💡 Hint

Check the syntax for filtering rows where the column equals the parameter.

The correct syntax uses each [Column] = Parameter to filter rows matching the parameter value.

🔧 Formula Fix

expert

Debugging Parameter Usage in DAX Measure

Consider this DAX measure using a parameter DiscountRate:

Final Price = SUM(Sales[Price]) * (1 - DiscountRate)

When running the report, the measure returns an error. What is the most likely cause?

A DiscountRate is a parameter but used without wrapping in a function like SELECTEDVALUE

B SUM(Sales[Price]) cannot be multiplied by a parameter

C DiscountRate parameter must be declared inside the measure

D The measure syntax is invalid because of missing CALCULATE

💡 Hint

Parameters in DAX measures often need to be wrapped to get their current value.

Parameters are not automatically scalar values in DAX; you often need SELECTEDVALUE or similar to retrieve their value.

Practice

1. What is the main purpose of using Parameters in Power BI reports?

easy

A. To write complex DAX formulas without errors

B. To allow users to change values dynamically and interact with the report

C. To create new tables automatically from data sources

D. To export reports to Excel format

Solution

1. Step 1: Understand what Parameters do

   Parameters let users input or select values that affect report visuals or calculations.

2. Step 2: Identify the main benefit

   This makes reports flexible and interactive, improving user experience.

3. Final Answer:

   To allow users to change values dynamically and interact with the report -> Option B

4. Quick Check:

   Parameters = Dynamic user input [OK]

Hint: Parameters let users control report values easily [OK]

Common Mistakes:

• Confusing parameters with data import
• Thinking parameters create tables
• Assuming parameters export reports

2. Which of the following is the correct way to create a parameter in Power BI?

easy

A. Use the 'Manage Parameters' option under the 'Home' tab in Power Query Editor

B. Write a DAX formula starting with PARAMETER()

C. Add a new calculated column in the data view

D. Create a new measure with the parameter name

Solution

1. Step 1: Locate parameter creation method

   Parameters are created in Power Query Editor using 'Manage Parameters' under the Home tab.

2. Step 2: Eliminate incorrect options

   DAX does not have a PARAMETER() function; calculated columns and measures are not parameters.

3. Final Answer:

   Use the 'Manage Parameters' option under the 'Home' tab in Power Query Editor -> Option A

4. Quick Check:

   Parameters created in Power Query Editor [OK]

Hint: Create parameters via Power Query Editor's Manage Parameters [OK]

Common Mistakes:

• Trying to create parameters with DAX
• Confusing parameters with calculated columns
• Assuming parameters are measures

3. Given a parameter named SelectedYear with values 2021, 2022, 2023, what will the following DAX measure return?

Sales Selected Year = CALCULATE(SUM(Sales[Amount]), Sales[Year] = SelectedYear)

medium

A. Total sales for the year selected by the parameter

B. Total sales for all years ignoring the parameter

C. An error because parameters cannot be used in DAX

D. Total sales for the latest year in the data

Solution

1. Step 1: Understand the measure logic

   The measure sums Sales[Amount] but filters Sales[Year] to equal the parameter SelectedYear.

2. Step 2: Interpret parameter effect

   SelectedYear acts as a filter value, so only sales for that year are summed.

3. Final Answer:

   Total sales for the year selected by the parameter -> Option A

4. Quick Check:

   Parameter filters data in CALCULATE [OK]

Hint: Parameters filter data in CALCULATE expressions [OK]

Common Mistakes:

• Thinking parameters can't be used in DAX
• Ignoring the filter effect of the parameter
• Assuming it sums all years

4. You created a parameter but your report visuals do not update when you change its value. What is the most likely cause?

medium

A. The parameter values are not numeric

B. Parameters only work in Power Query, not in reports

C. You forgot to refresh the data source

D. The parameter is not linked to any filter or measure in the report

Solution

1. Step 1: Check parameter usage

   Parameters must be connected to filters or measures to affect visuals.

2. Step 2: Identify why visuals don't update

   If not linked, changing parameter value has no effect on report visuals.

3. Final Answer:

   The parameter is not linked to any filter or measure in the report -> Option D

4. Quick Check:

   Parameter must connect to report elements [OK]

Hint: Link parameters to filters or measures to update visuals [OK]

Common Mistakes:

• Assuming parameter type blocks updates
• Forgetting to refresh data source
• Thinking parameters don't work in reports

5. You want to create a report where users can select a sales region from a parameter and see total sales for that region. Which steps should you follow?

hard

A. Create a parameter with numeric IDs only and use it in a visual title

B. Create a calculated column with region names, then use it as a slicer

C. Create a parameter with region values, then use it in a measure filter to calculate sales

D. Write a DAX measure that ignores region and sums all sales

Solution

1. Step 1: Create a parameter listing all sales regions

   This allows users to pick a region dynamically.

2. Step 2: Use the parameter in a measure filter

   Write a measure that sums sales filtered by the selected region parameter.

3. Final Answer:

   Create a parameter with region values, then use it in a measure filter to calculate sales -> Option C

4. Quick Check:

   Parameter filters sales by user selection [OK]

Hint: Use parameter in measure filter for dynamic region sales [OK]

Common Mistakes:

• Using calculated columns instead of parameters for user input
• Ignoring region filter in measure
• Using parameters only for titles