🏆 Final CDIP-BP Capstone Exam

You are starting Stage 1 and need to connect to the AdventureWorks data (provided as either CSV exports or SQL tables). What is the standard initial step in Power BI Desktop?

Use Get Data -> Folder to import all CSVs at once.

Correct Incorrect Correct answer

Use Get Data -> SQL Server or CSV/Excel for each required table individually.

Correct Incorrect Correct answer

Merge all CSV files manually before importing.

Correct Incorrect Correct answer

Use Enter Data to create the tables manually.

Correct Incorrect Correct answer

The imported SalesOrderHeader table has a column named OrderDate. The project requires business-friendly labels like “Sales Date”. Where should you perform this renaming during the ETL process?

In the Power BI Model view after loading.

Correct Incorrect Correct answer

In the Power BI Report view using visual formatting options.

Correct Incorrect Correct answer

In the Power Query Editor by right-clicking the column header and selecting Rename.

Correct Incorrect Correct answer

Using a DAX formula like RENAMECOLUMNS.

Correct Incorrect Correct answer

Stage 1 requires merging Product, ProductSubcategory, and ProductCategory into a unified hierarchy within the Product query. How is this achieved in Power Query?

Use Append Queries to stack the tables vertically.

Correct Incorrect Correct answer

Use Merge Queries to join Product to ProductSubcategory (on SubcategoryKey), then merge the result with ProductCategory (on CategoryKey).

Correct Incorrect Correct answer

Create relationships between them in the Power BI Model view.

Correct Incorrect Correct answer

Add Calculated Columns using DAX RELATED function.

Correct Incorrect Correct answer

The project requires a dedicated Date table. What is a common method to create this within Power Query during the ETL stage?

Import the Production.TransactionHistoryArchive table and hope it covers all dates.

Correct Incorrect Correct answer

In Power Query, create a blank query and use M code (e.g., List.Dates, #date) to generate a continuous sequence of dates covering the required range.

Correct Incorrect Correct answer

In Power BI Data view, create a Calculated Table using the DAX CALENDAR function.

Correct Incorrect Correct answer

Rely on Power BI's built-in auto date/time feature.

Correct Incorrect Correct answer

You find null values in the ProductCost column which should be numeric 0 if missing. What is the standard Power Query transformation to replace these nulls?

Use Transform > Fill > Down or Up.

Correct Incorrect Correct answer

Use Transform > Replace Values (finding null and replacing with 0 or "N/A").

Correct Incorrect Correct answer

Filter out rows containing null.

Correct Incorrect Correct answer

Load the data and use a DAX IF(ISBLANK(…)) measure.

Correct Incorrect Correct answer

Where does Power Query automatically record the sequence of transformations (like filtering, renaming, merging) applied to each query, providing basic lineage?

The Model View relationship diagram.

Correct Incorrect Correct answer

The Applied Steps pane within each query in the Power Query Editor.

Correct Incorrect Correct answer

The DAX Measure documentation table.

Correct Incorrect Correct answer

The Report View visuals and annotations.

Correct Incorrect Correct answer

You have completed all the required data extraction, cleaning, merging, and transformation steps in Power Query for Stage 1. What is the final action needed to load this cleaned data into the Power BI model?

File > Save the PBIX file.

Correct Incorrect Correct answer

Home > Refresh Preview.

Correct Incorrect Correct answer

Home > Close & Apply.

Correct Incorrect Correct answer

View > Query Dependencies.

Correct Incorrect Correct answer

Now in Stage 2, you need to establish the formal relationships between your loaded tables (e.g., FactSales to DimProduct). Which view in Power BI Desktop is primarily used for this?

The Report view

Correct Incorrect Correct answer

The Data view

Correct Incorrect Correct answer

The Power Query Editor

Correct Incorrect Correct answer

The Model view

Correct Incorrect Correct answer

What is the expected and standard cardinality for the relationship between a dimension table (like DimCustomer) and a fact table (like FactSales) on the CustomerID key?

One-to-One (1:1)

Correct Incorrect Correct answer

Many-to-Many (:)

Correct Incorrect Correct answer

One-to-Many (1:*)

Correct Incorrect Correct answer

Many-to-One (*:1)

Correct Incorrect Correct answer

For the relationship between DimTerritory and FactSales, what is the recommended “Cross filter direction” setting for optimal performance and a clear star schema?

Both Directions

Correct Incorrect Correct answer

Single Direction (Dimension table filters Fact table)

Correct Incorrect Correct answer

Single Direction (Fact table filters Dimension table)

Correct Incorrect Correct answer

No Filtering

Correct Incorrect Correct answer

Stage 2 requires calculating GrossMargin. Is this best implemented as a Calculated Column or a Measure, and why?

Calculate Total Sales as SUM(SalesOrderDetail[LineTotal]).

Correct Incorrect Correct answer

Calculate GrossMargin as LineTotal – (OrderQty * RelatedProductCost) for each row in SalesOrderDetail.

Correct Incorrect Correct answer

Calculate Year-to-Date Sales using DATESYTD.

Correct Incorrect Correct answer

Calculate Total Orders using DISTINCTCOUNT(SalesOrderHeader[SalesOrderID]).

Correct Incorrect Correct answer

The SalesOrderDetail table already has a LineTotal column (calculated in ETL or source). What is the simplest DAX measure to calculate the overall Total Sales?

Total Sales = SUMX(SalesOrderDetail, SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice])

Correct Incorrect Correct answer

Total Sales = SUM(SalesOrderDetail[LineTotal])

Correct Incorrect Correct answer

Total Sales = CALCULATE(SUM(SalesOrderDetail[LineTotal]))

Correct Incorrect Correct answer

Total Sales = VALUES(SalesOrderDetail[LineTotal])

Correct Incorrect Correct answer

You have created measures [Total Sales] and [Total Orders]. What is the correct and safe DAX measure for Average Order Value?

Average Order Value = AVERAGE(SalesOrderHeader[TotalDue])

Correct Incorrect Correct answer

Average Order Value = [Total Sales] / [Total Orders]

Correct Incorrect Correct answer

Average Order Value = DIVIDE([Total Sales], [Total Orders])

Correct Incorrect Correct answer

Average Order Value = AVERAGEX(SalesOrderHeader, [Total Sales])

Correct Incorrect Correct answer

How can you practically validate that your relationships and core measures (like [Total Sales]) are working together correctly in Stage 2?

Compare COUNTROWS(FactSales) to the original row count in SalesOrderDetail.xlsx.

Correct Incorrect Correct answer

Build a Matrix visual with Product Category on rows, Territory Region on columns, and [Total Sales] as values, checking totals.

Correct Incorrect Correct answer

Ensure all relationships in the Model view use "Both Directions" filtering.

Correct Incorrect Correct answer

Confirm that all Power Query M code uses error handling functions.

Correct Incorrect Correct answer

Stage 3 requires Year-to-Date Sales. Assuming you have a Date table marked as such and a [Total Sales] measure, what is the standard DAX approach?

Create a calculated column: IsYTD = YEAR([Sales Date]) = YEAR(TODAY()).

Correct Incorrect Correct answer

Create a measure: YTD Sales = CALCULATE([Total Sales], DATESYTD('Date'[Date])).

Correct Incorrect Correct answer

Filter the FactSales table in Power Query to only include current year data.

Correct Incorrect Correct answer

Use a visual-level filter on each chart for the current year.

Correct Incorrect Correct answer

You have measures [YTD Sales] and [Prev YTD Sales]. What is the correct and safe DAX formula for Sales Growth %?

Sales Growth % = DIVIDE([YTD Sales] – [Prev YTD Sales], [Prev YTD Sales])

Correct Incorrect Correct answer

Sales Growth % = [YTD Sales] / [Prev YTD Sales] – 1

Correct Incorrect Correct answer

Sales Growth % = ([YTD Sales] – [Prev YTD Sales]) / [Prev YTD Sales]

Correct Incorrect Correct answer

Sales Growth % = DIVIDE([YTD Sales], [Prev YTD Sales]) – 1

Correct Incorrect Correct answer

How would you typically use DAX to calculate the total sales generated only by the Top 10 Products (based on their [Total Sales])?

Top 10 Products = TOPN(10, DimProduct, [Total Sales], DESC)

Correct Incorrect Correct answer

Top 10 Products = CALCULATE([Total Sales], FILTER(DimProduct, [Product Rank] <= 10)) where [Product Rank] uses RANKX.

Correct Incorrect Correct answer

Top 10 Products = RANKX(ALL(DimProduct), [Total Sales], , DESC) <= 10

Correct Incorrect Correct answer

Top 10 Products = IF(RANKX(…) <= 10, [Total Sales])

Correct Incorrect Correct answer

You need a KPI measure for the number of unique customers who placed orders. Which DAX measure correctly calculates this?

Customer Count = COUNTROWS(DimCustomer)

Correct Incorrect Correct answer

Customer Count = COUNT(DimCustomer[CustomerID])

Correct Incorrect Correct answer

Customer Count = DISTINCTCOUNT(SalesOrderHeader[CustomerID])

Correct Incorrect Correct answer

Customer Count = SUMMARIZE(SalesOrderHeader, SalesOrderHeader[CustomerID])

Correct Incorrect Correct answer

To analyze the difference between “Reseller” vs “Online” sales channels (assuming Channel info is in DimCustomer), what DAX approach allows you to directly compare these segments in visuals or further calculations?

Create two separate measures: Reseller Sales = CALCULATE([Total Sales], DimCustomer[Channel]="Reseller") and Online Sales = CALCULATE(…).

Correct Incorrect Correct answer

Add a Slicer for Channel and let the user select.

Correct Incorrect Correct answer

Create a Calculated Column Channel Type in FactSales.

Correct Incorrect Correct answer

Use SUMMARIZE to create a table grouped by Channel.

Correct Incorrect Correct answer

Where should the business logic, DAX formula, and validation notes for KPIs like YoY Growth Rate and Customer Retention Rate be formally documented?

The ETL documentation table.

Correct Incorrect Correct answer

The KPI Register Sheet or a similar DAX documentation log.

Correct Incorrect Correct answer

Comments embedded within the DAX formula bar for each measure.

Correct Incorrect Correct answer

The Model View relationship descriptions.

Correct Incorrect Correct answer

Stage 4 requires an Executive Overview dashboard page. What type of content is most appropriate for this high-level audience?

Detailed tables showing every sales transaction.

Correct Incorrect Correct answer

High-level KPI Cards (Total Sales, Profit Margin, Growth Rate) and key trend lines.

Correct Incorrect Correct answer

Slicers for every possible dimension in the model.

Correct Incorrect Correct answer

Technical details about the data model relationships.

Correct Incorrect Correct answer

For the Regional Dashboard, you need to show sales performance across different geographic territories. Which Power BI visual is specifically designed for this?

Use a Table visual listing territories and sales.

Correct Incorrect Correct answer

Use a Map visual (e.g., Filled Map or Bubble Map) with Territory and [Total Sales].

Correct Incorrect Correct answer

Use a Scatter plot with territory on one axis.

Correct Incorrect Correct answer

Use a Line chart showing sales over time for each territory.

Correct Incorrect Correct answer

You need to clearly show the Profit Margin % for different Product Categories and highlight which ones are above or below target. Which visual and feature combination is effective for this?

A Line chart showing profit over time.

Correct Incorrect Correct answer

A Matrix visual with Product Category on rows and [Profit Margin %] as values, using conditional formatting.

Correct Incorrect Correct answer

A Card visual showing total company profit.

Correct Incorrect Correct answer

A Map visual showing profit by country.

Correct Incorrect Correct answer

For the Customer Insights dashboard, you want to visualize customer segments (e.g., Platinum, Gold, Silver, Bronze based on sales) and identify patterns, perhaps comparing their sales volume to their profitability. Which visual might be suitable?

A Line chart showing customer count over time.

Correct Incorrect Correct answer

A Scatter plot showing [Total Sales] vs [Profit Margin %] per customer, potentially colored by Customer Segment.

Correct Incorrect Correct answer

A Map visual showing customer locations.

Correct Incorrect Correct answer

A Card visual showing [Customer Count].

Correct Incorrect Correct answer

How should Slicers be used in your multi-page dashboard design according to the guide?

They should be placed randomly on each page.

Correct Incorrect Correct answer

They should be included consistently, often grouped together, allowing users to filter by key dimensions like Region, Year, Channel.

Correct Incorrect Correct answer

They are not necessary if the report includes tables.

Correct Incorrect Correct answer

They should only be used on the Executive Overview page.

Correct Incorrect Correct answer

To improve the user experience navigating between the different dashboard pages (Executive, Sales, Product, etc.), what feature should you implement?

Expect users to use the default page tabs at the bottom.

Correct Incorrect Correct answer

Add Buttons or Navigation visuals linked to Bookmarks or other pages for a guided user experience.

Correct Incorrect Correct answer

Include a table of contents on the first page.

Correct Incorrect Correct answer

Provide instructions in a text box on how to switch pages.

Correct Incorrect Correct answer

Stage 4 requires a “Summary Page ‘Insights at a Glance’”. What is the purpose of this page in data storytelling?

A page containing only slicers and filters.

Correct Incorrect Correct answer

A dedicated "Insights at a Glance" page or section summarizing the key findings and recommendations.

Correct Incorrect Correct answer

A detailed log of all DAX measures used.

Correct Incorrect Correct answer

A copy of the Model view relationship diagram.

Correct Incorrect Correct answer

You have completed your Power BI report (.pbix file). What is the standard process to make it accessible online to authorized users within your organization?

Save the PBIX file to a shared network drive.

Correct Incorrect Correct answer

Publish the report from Power BI Desktop to a designated Power BI Service Workspace (e.g., CDIP-BP_Capstone).

Correct Incorrect Correct answer

Export the report as a Power BI Template (.pbit).

Correct Incorrect Correct answer

Email the PBIX file to stakeholders.

Correct Incorrect Correct answer

Where do you configure the daily scheduled refresh for your published AdventureWorks dataset?

In Power BI Desktop, under File > Options.

Correct Incorrect Correct answer

In the Power BI Service, under the Dataset settings for the published dataset.

Correct Incorrect Correct answer

In the Power Automate portal.

Correct Incorrect Correct answer

Using a DAX function like REFRESH().

Correct Incorrect Correct answer

You need to implement Row-Level Security (RLS) so that Territory Managers only see data for their specific territory, identified by their email address matching the TerritoryName (simplified scenario). What are the key steps?

Create separate reports for each Territory Manager.

Correct Incorrect Correct answer

Define a role in Power BI Desktop (e.g., "Regional Manager") with a DAX filter rule like DimTerritory[TerritoryName] = USERPRINCIPALNAME(), then assign users to this role in the Service.

Correct Incorrect Correct answer

Use Power Automate to filter the data before sending reports.

Correct Incorrect Correct answer

Configure visual-level filters based on the manager's name.

Correct Incorrect Correct answer

Stage 5 requires setting up a Power Automate flow. What is a typical purpose for integrating Power Automate with your Power BI solution?

To automatically write DAX measures based on user requests.

Correct Incorrect Correct answer

To trigger actions based on Power BI events (like refresh completion) or on a schedule (like emailing a PDF report).

Correct Incorrect Correct answer

To design the visual layout of the Power BI dashboard.

Correct Incorrect Correct answer

To manage user access and permissions to workspaces.

Correct Incorrect Correct answer

The Capstone requires a Governance Register. What is this deliverable?

The Power Query Applied Steps pane.

Correct Incorrect Correct answer

A separate Excel/Word document listing report names, owners, refresh schedules, access levels, etc.

Correct Incorrect Correct answer

The Lineage View in Power BI Service.

Correct Incorrect Correct answer

The Q&A feature in Power BI.

Correct Incorrect Correct answer

Stage 5 asks you to capture the Lineage View from Power BI Service. What does this view show?

A log of user activity within a report.

Correct Incorrect Correct answer

A visual diagram in Power BI Service showing data flow from sources to datasets to reports/dashboards.

Correct Incorrect Correct answer

A DAX function for tracing calculation dependencies.

Correct Incorrect Correct answer

A Power Query feature for documenting M code steps.

Correct Incorrect Correct answer

What is the purpose of the Compliance Checklist deliverable required in Stage 5?

A list of required visuals for the dashboard.

Correct Incorrect Correct answer

A self-assessment document confirming adherence to CDIPS governance standards (e.g., documentation, security, reproducibility).

Correct Incorrect Correct answer

A performance benchmark report for DAX measures.

Correct Incorrect Correct answer

A log of successful vs. failed dataset refreshes.

Correct Incorrect Correct answer

According to Section 9 (“Submission Requirements”), what constitutes the complete package you need to submit for the Capstone evaluation?

Only the final AdventureWorks_Capstone.pbix file.

Correct Incorrect Correct answer

The .pbix file, Governance Register, Compliance Checklist, screenshots (Flow, Lineage), and Project Documentation.

Correct Incorrect Correct answer

Only the exported Certified_Report.pdf.

Correct Incorrect Correct answer

A link to the published report in Power BI Service.

Correct Incorrect Correct answer

Based on the “Evaluation Rubric,” which areas of the project are weighted most heavily (20% each)?

ETL Implementation (15%)

Correct Incorrect Correct answer

Modeling Architecture, Analytical Measures, Visualization & Storytelling, Automation & Governance (all 20%)

Correct Incorrect Correct answer

Professional Documentation (10%)

Correct Incorrect Correct answer

Presentation & Delivery (10%)

Correct Incorrect Correct answer

What is the minimum overall score required to pass the CDIP-BP Final Capstone Project and achieve the “Professional Standard”?

0.6

Correct Incorrect Correct answer

0.7

Correct Incorrect Correct answer

0.75

Correct Incorrect Correct answer

0.9

Correct Incorrect Correct answer

The CDIPS Governance Standards emphasize “Reproducibility”. What does this mean in the context of your Capstone submission?

The Power Automate flow runs successfully every time.

Correct Incorrect Correct answer

Another analyst can follow your documentation (ETL Log, DAX Register) to recreate your model and results exactly.

Correct Incorrect Correct answer

The dashboard visuals match the provided screenshots perfectly.

Correct Incorrect Correct answer

The PBIX file can be opened in the latest version of Power BI Desktop.

Correct Incorrect Correct answer

🏆 Final CDIP-BP Capstone Exam

🏆 Final CDIP-BP Capstone Exam

DataPro 23 October 2025

Exam Summary

Information

Results

Results

Categories

1. Question

Hint

2. Question

Hint

3. Question

Hint

4. Question

Hint

5. Question

Hint

6. Question

Hint

7. Question

Hint

8. Question

Hint

9. Question

Hint

10. Question

Hint

11. Question

Hint

12. Question

Hint

13. Question

Hint

14. Question

Hint

15. Question

Hint

16. Question

Hint

17. Question

Hint

18. Question

Hint

19. Question

Hint

20. Question

Hint

21. Question

Hint

22. Question

Hint

23. Question

Hint

24. Question

Hint

25. Question

Hint

26. Question

Hint

27. Question

Hint

28. Question

Hint

29. Question

Hint

30. Question

Hint

31. Question

Hint

32. Question

Hint

33. Question

Hint

34. Question

Hint

35. Question

Hint

36. Question

Hint