Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 3
At NextWave Analytics, you are building a Power BI report on a large dataset stored in SQL Server.
To optimize query performance, you need to allow users to set a date range (Start Date and End Date) for fetching data.
How should you modify the parameter to ensure that only relevant rows are loaded into Power BI?
-
A
Load the entire dataset into Power BI and filter it using DAX.
-
B
Use a slicer visual to filter the date range dynamically within the report.
-
C
Create two parameters (StartDate and EndDate) and apply them in the SQL query's WHERE clause.
-
D
Use a calculated measure to filter the dataset by the selected date range.
Reveal correct answer
Correct answer: C
Explanation
Optimize Data Load Step-by-Step
Create and Apply Date Range Parameters
To optimize query performance at NextWave Analytics by loading only relevant rows from a large SQL Server dataset, create two parameters and use them in the SQL query. In Power BI Desktop, go to Home > Manage Parameters > New Parameter, create "StartDate" and "EndDate" (Type: Date, Suggested Values: Any value), and set default values (e.g., 2023-01-01 and 2023-12-31). Then, in "Get Data" > SQL Server, enter your server and database, select "Advanced options," and write a query like: SELECT * FROM Sales WHERE OrderDate BETWEEN @StartDate AND @EndDate. Replace @StartDate and @EndDate with the parameter names in Query Editor (Home > Advanced Editor) using Power BI’s parameter syntax (e.g., Sql.Database("server", "database", [Query="SELECT * FROM Sales WHERE OrderDate BETWEEN '" & StartDate & "' AND '" & EndDate & "'"])). This filters data at the source, reducing load time.
Why Not Other Approaches?
Calculated Measure
A calculated measure (e.g., FilteredSales = CALCULATE([TotalSales], Sales[OrderDate] >= [StartDate] && Sales[OrderDate] <= [EndDate])) filters after loading all data, defeating performance optimization. The DAX documentation focuses on "post-load calculations."
Load and Filter with DAX
Loading the entire dataset and filtering with DAX (e.g., in a measure or filter) wastes resources by importing unnecessary rows. The data import guidance prioritizes "source-level filtering."
Slicer Visual
Using a slicer filters data visually after loading, not at the source, keeping the full dataset in memory.
Additional Considerations
User Input: Enable users to adjust parameters in Power BI Service via "Edit Parameters" in dataset settings.
Validation: Test the query with a small date range to confirm row reduction.
Final Answer
Create two parameters (StartDate and EndDate) and apply them in the SQL query's WHERE clause to load only relevant rows, optimizing query performance for NextWave Analytics.
A.
Pulling the entire dataset into memory consumes unnecessary resources, increases refresh time, and degrades report performance—especially with large datasets.
B.
Slicers filter after the data is loaded into the model. This does not reduce data retrieval from the source and will not optimize performance for large datasets.
C.
Defining StartDate and EndDate as Power Query parameters and incorporating them into the SQL query's WHERE clause enables query folding.
This ensures that only relevant rows are pulled into Power BI, drastically reducing data volume, improving performance, and optimizing memory usage.
D.
Measures work on data that is already loaded into the model, meaning they do not reduce the amount of data fetched from the source.
This approach affects visual filtering, not query performance.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
