---

QUESTION 1.

You publish the dataset to powerbi.com.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

[1] You need and on-premise data
 gateway to refresh the 
dataset (Y/N).

[2] You need to configure a 
scheduled refresh of the 
dataset (Y/N).

[3] You can use basic 
authentication on the dataset 
to connect to the data (Y/N).

.

.

.

[1] You need an on-premise data gateway to refresh the dataset: No: If the data is in the cloud (like Azure SQL Database, SharePoint Online, etc.), you do not need an on-premise data gateway.

[2] You need to configure a scheduled refresh of the dataset: Yes: To keep your data up-to-date, you need to configure a scheduled refresh for the dataset.

[3] You can use basic authentication on the dataset to connect to the data: No: Power BI typically uses OAuth for authentication, and basic authentication is not recommended for security reasons.

INFO:

In the context of Power BI, a dataset refers to a collection of data that you import or connect to, which you use to create reports and visualizations. Here are some key aspects of datasets in Power BI:

Key Features:

1. Data Source: A dataset can be created from a variety of data sources, including:

   • Databases (e.g., SQL Server, Oracle)

   • Online services (e.g., Azure, Salesforce)

   • Excel files

   • CSV files

2. Data Model: Within a dataset, you can define relationships between different tables, create calculated columns, and set up measures using DAX (Data Analysis Expressions).

3. Refresh Options: Datasets can be set to refresh automatically or on-demand to ensure that the data in your reports is up-to-date.

4. Sharing: Datasets can be shared across different reports and dashboards within Power BI, allowing for consistent data usage across multiple analyses.

5. Security: You can implement row-level security within a dataset to control access to data based on user roles.

Use in Reporting:

Datasets serve as the foundation for building reports and dashboards, enabling users to visualize and analyze data effectively. When you create visualizations in Power BI, they are based on the underlying dataset you have selected.

/INFO

---

QUESTION 2.

You need to create a KPI visualization to meet the reporting requirements of the sales managers.

How should you create the visualization?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Indicator:
(a) Date[month]
(b) Sales[sales_amount]
(c) Sales[sales_id]
(d) Targets[sales_target]
(e) Weekly_Returns[total_returns]

⑵ Trend axis:
(a) Date[month],
(b) Sales[sales_amount],
(c) Sales[sales_id],
(d) Targets[sales_target],
(e) Weekly_Returns[total_returns]

⑶ Target goals:
(a) Date[month]
(b) Sales[sales_amount]
(c) Sales[sales_id]
(d) Targets[sales_target]
(e) Weekly_Returns[total_returns]

.

.

.

To create a KPI visualization that meets the reporting requirements of the sales managers, you should configure it as follows:

[1] Indicator: (b) Sales[sales_amount]

[2] Trend axis: (a) Date[month]

[3] Target goals: (d) Targets[sales_target]

Reasons:
• Indicator: The KPI indicator should be the actual value you want to measure, which in this case is the sales amount.

• Trend axis: The trend axis should show the time period over which the sales are measured, so using Date[month] is appropriate.

• Target goals: The target goals should be the benchmark or target you are comparing the actual sales against, which is the sales target.

INFO:

In the context of Power BI, visualization refers to the graphical representation of data that helps users to understand and analyze information effectively. Visualizations transform raw data into understandable formats, enabling insights and decision-making. Here are key aspects of visualizations in Power BI:

Key Features:

1. Types of Visualizations:

   • Charts: Bar, column, line, pie, and area charts that display trends and comparisons.

   • Tables and Matrices: Show detailed data in a structured format.

   • Maps: Geographic representations for location-based data (e.g., shape maps, filled maps).

   • KPIs: Key Performance Indicators that show progress toward specific goals.

   • Cards: Display single values or metrics for quick insights.

   • Custom Visuals: Third-party visuals that can be imported to extend functionality.

2. Interactivity:

   • Visualizations in Power BI are interactive, allowing users to click on elements to filter data, drill down into details, or cross-highlight information across different visuals.

3. Customization:

   • Users can customize visuals by adjusting colors, labels, titles, and data fields, helping to tailor reports to specific audiences or purposes.

4. Dashboards:

   • Visualizations can be pinned to dashboards for a consolidated view of key metrics and insights, providing a high-level overview.

5. Analysis:

   • Visuals support various analytical functions, such as trend analysis, forecasting, and grouping, enabling users to derive insights from their data.

Importance:

Visualizations are crucial in Power BI as they make complex data more accessible and comprehensible, facilitating better decision-making and storytelling through data.

/INFO

---

QUESTION 3.

You need to create a visualization to meet the reporting requirements of the sales managers.

How should you create the visualization?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Visualization type:
(a) Card
(b) Donut chart
(c) Gauge
(d) Key influencers
(e) KPI

[2] Indicator:
(a) Date[month]
(b) Sales[sales_amount]
(c) Sales[sales_id]
(d) Targets[sales_target]
(e) Weekly_Returns[total_returns]    

[3] Trend axis:
(a) Date[month]
(b) Sales[sales_amount]
(c) Sales[sales_id]
(d) Targets[sales_target]
(e) Weekly_Returns[total_returns]

[4] Target goals:
(a) Date[month]
(b) Sales[sales_amount]
(c) Sales[sales_id]
(d) Targets[sales_target]
(e) Weekly_Returns[total_returns]

.

.

.

To create a visualization that meets the reporting requirements of the sales managers, you should configure it as follows:

[1] Visualization type: (e) KPI

[2] Indicator: (b) Sales[sales_amount]

[3] Trend axis: (a) Date[month]

[4] Target goals: (d) Targets[sales_target]

Reasons:

• Visualization type: KPI is ideal for tracking key performance indicators.

• Indicator: The KPI indicator should be the actual value you want to measure, which is the sales amount.

• Trend axis: The trend axis should show the time period over which the sales are measured, so using Date[month] is appropriate.

• Target goals: The target goals should be the benchmark or target you are comparing the actual sales against, which is the sales target.

INFO:

In Power BI, the visualization that is commonly used to display target goals is the "KPI" (Key Performance Indicator) visual.

Key Features of the KPI Visual: Comparison with Target: It shows actual values compared to target values. Trend Indicators: It can display trends over time to help assess performance. Color Coding: Typically uses color coding (e.g., green for on target, red for below target) to indicate performance status at a glance.

Other Visuals for Target Goals: Gauge Chart: Displays progress toward a goal using a dial-like interface. Card Visual: Can show a single value against a target, although it may require additional context to show performance against that target.

/INFO

---

QUESTION 4.

You need to calculate the last day of the month in the balance sheet data to ensure that you can relate the balance sheet data to the Date table.

Which type of calculation and which formula should you use?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Type of calculation:
(a) A DAX calculated column
(b) A DAX calculated measure
(c) An M custom column

[2] Formula:
(a) Date.EndOfMonth(#date([Year],[Month],1))
(b) Date.EndOfQuarter(#date([Year],[Month],1))
(c) ENDOFQUARTER(DATE('BalanceSheet'[Year],BalanceSheet[Month],1),0)

.

.

.

To calculate the last day of the month in the balance sheet data and relate it to the Date table, you should use the following options:

[1] Type of calculation: (a) A DAX calculated column

[2] Formula: (a) Date.EndOfMonth(#date([Year],[Month],1))

Reasons:

• Type of calculation: A DAX calculated column is appropriate because you need to create a new column in your balance sheet data that contains the last day of each month.

• Formula: Date.EndOfMonth(#date([Year],[Month],1)) is the correct formula to calculate the last day of the month for a given year and month.

INFO:

In the context of Power BI, calculation refers to the process of deriving new values or metrics from existing data using various methods, primarily through DAX (Data Analysis Expressions). Calculations enhance data analysis by allowing users to create custom metrics that fit specific business needs. Here are key aspects of calculations in Power BI:

Key Concepts:

1. DAX (Data Analysis Expressions):

   • DAX is a formula language used in Power BI to create custom calculations. It includes functions for mathematical operations, aggregation, filtering, and more.

2. Calculated Columns:

   • These are new columns added to a data table that derive their values from existing columns. For example, you can create a calculated column that combines first and last names into a full name.

3. Measures:

   • Measures are calculations that are performed on the data model and are used in visualizations. They are typically aggregated values, like sums or averages. For instance, a measure might calculate total sales or average revenue per customer.

4. Row Context vs. Filter Context:

   • Row Context refers to calculations that apply to each row in a table (e.g., calculated columns).

   • Filter Context is the context applied to calculations in measures, allowing for dynamic calculations based on user interactions with reports (e.g., filtering by date or category).

5. Time Intelligence:

   • DAX includes functions that enable time-based calculations, allowing users to analyze data over time, such as year-to-date totals or month-over-month growth.

/INFO

---

QUESTION 5.

You need to grant access to the business unit analysts.

What should you configure?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Permissions required in powerbi.com:
(a) Access permissions to an app
(b) The Member role to the workspace
(c) The Viewer role to the workspace

[2] Permissions for the profit and loss dataset:
(a) Build
(b) Delete
(c) Reshare

.

.

.

To grant access to the business unit analysts, you should configure the following:

[1] Permissions required in powerbi.com: • (c) The Viewer role to the workspace

[2] Permissions for the profit and loss dataset: • (a) Build

Reasons:

• Viewer role to the workspace: This role allows users to view the content in the workspace without making any changes, which is suitable for analysts who need to access reports and dashboards.

• Build permission for the dataset: This permission allows users to create their own reports and dashboards based on the dataset, which is essential for analysts to perform their analysis.

INFO:

In the context of Power BI, grant access refers to the process of providing users or groups the permission to view or interact with reports, dashboards, and datasets within the Power BI service. Proper access management is crucial for data security and collaboration. Here are key aspects of granting access in Power BI:

Key Concepts:

1. User Roles:

   • Power BI allows different roles with varying levels of access, such as:

     • Viewer: Can view reports and dashboards.

     • Contributor: Can edit and create reports and dashboards.

     • Admin: Has full control over the workspace and can manage access settings.

2. Workspaces:

   • Access can be granted at the workspace level, where users can be added with specific roles, determining what they can do within that workspace.

3. Sharing:

   • Reports and dashboards can be shared directly with individual users or groups, allowing them to view the content. Sharing can be done via email invitations or by providing links.

4. Row-Level Security (RLS):

   • RLS allows you to restrict data access for specific users based on their roles or attributes, ensuring that users only see data relevant to them.

5. Apps:

   • You can package reports and dashboards into an app and grant access to it. This provides a streamlined way for users to access multiple reports and datasets.

6. Permissions:

   • Administrators can manage permissions for users, including whether they can share reports with others or export data.

/INFO

---

QUESTION 6.

How should you distribute the reports to the board of directors?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Grant access by:
(a) Sharing individual reports,
(b) Using a workspace membership,
(c) Using an app

[2] Grant access to:
(a) A dynamic distribution list,
(b) A mail-enabled Azure-Actice Directory group,
(c) Individual user emails

.

.

.

To distribute the reports to the board, you should configure the following:

[1] Grant access by: • (c) Using an app

[2] Grant access to: • (b) A mail-enabled Azure Active Directory group

Reasons:

• Using an app: This method allows you to bundle multiple reports and dashboards into a single, easy-to-access location. It's efficient for distributing content to a larger audience like the board.

• A mail-enabled Azure Active Directory group: This ensures that all members of the board can access the reports without needing to manage individual user permissions. It simplifies the process and ensures that access is controlled and secure.

INFO:

In Power BI, distributing a report refers to the process of sharing a report with other users or groups so that they can view, interact with, or collaborate on the report. Here are key aspects of report distribution in Power BI:

Key Concepts:

1. Sharing:

   • Users can share reports directly with others via the Power BI service. This can be done by sending an email invitation or generating a shareable link.

2. Workspaces:

   • Reports can be published to specific workspaces, where users with the appropriate permissions can access them. Distributing reports within a workspace allows for collaboration among team members.

3. Apps:

   • Reports can be packaged into an app. An app is a collection of related reports and dashboards that can be distributed to a broader audience. Users can access the app without needing to navigate multiple reports individually.

4. Exporting:

   • Reports can also be exported to file formats like PDF or PowerPoint. This allows users to distribute static versions of the report outside of Power BI.

5. Row-Level Security (RLS):

   • When distributing reports, RLS can be applied to ensure that users only see the data relevant to them, enhancing data security and privacy.

6. Subscriptions:

   • Users can create subscriptions to receive report updates via email at scheduled intervals, ensuring they stay informed about changes in the data.

/INFO

---

QUESTION 7.

You need to create a measure that will return the percentage of late orders.

How should you complete the DAX expression?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Late Orders Percent =
VAR OrderCount =
    COUNTROWS ( 'Orders' )
VAR LateOrders =
    Ⓟ (
        COUNTROWS ( 'Orders' ),
        Ⓠ ( 'Orders', Ⓡ )
    )
RETURN
    DIVIDE ( LateOrders, OrderCount )

---------- Select Choices ---------- 
Ⓟ: (a)SUM (b)COUNTX (c)CALCULATE (d)CALCULATETABLE
Ⓠ: (a)FILTER (b)ALLEXCEPT (c)CALCULATE (d)DATESBETWEEN
Ⓡ:
(a)Orders[OrderDate]>Orders[RequiredDate],
(b)Orders[ShippedDate]>=Orders[RequiredDate], 
(c)Orders[ShippedDate]<Orders[RequiredDate],
(d)Orders[ShippedDate]>Orders[RequiredDate]

.

.

.

To create a measure that returns the percentage of late orders, you should complete the DAX expression as follows:

Late Orders Percent =
VAR OrderCount =
    COUNTROWS ( 'Orders' )
VAR LateOrders =
    CALCULATE (
        COUNTROWS ( 'Orders' ),
        FILTER ( 'Orders', 'Orders'[ShippedDate] > 'Orders'[RequiredDate] )
    )
RETURN
    DIVIDE ( LateOrders, OrderCount )

The correct selections should be:

• Ⓟ: (c) CALCULATE

• Ⓠ: (a) FILTER

• Ⓡ: (d) Orders[ShippedDate] > Orders[RequiredDate]

This DAX expression calculates the total number of orders and the number of late orders, then divides the number of late orders by the total number of orders to get the percentage of late orders.

The condition 'Orders'[ShippedDate] > 'Orders'[RequiredDate] is used to identify late orders.

Reasons:

• 'Orders'[ShippedDate]: This represents the date when the order was actually shipped.

• 'Orders'[RequiredDate]: This represents the date by which the order was supposed to be shipped.

When the shipped date is later than the required date, it means the order was shipped after the deadline, making it a late order. By using this condition in the FILTER function, you can count only those orders that were shipped late, which is essential for calculating the percentage of late orders.

INFO:

In Power BI, a measure and a DAX expression are closely related concepts used for data analysis and reporting. Here's an overview of each:

Measure

• Definition: A measure is a calculated field that aggregates data based on a formula. It is typically used to perform calculations on data in a report or visualization context.

• Characteristics:

  • Measures are dynamic and recalculated based on the context in which they are used (e.g., filters applied to reports).

  • Measures can be used to calculate sums, averages, counts, or more complex calculations depending on the data model.

• Examples:

  • Total Sales: Total Sales = SUM(Sales[SalesAmount])

  • Average Price: Average Price = AVERAGE(Products[Price])

DAX Expression

• Definition: DAX (Data Analysis Expressions) is a formula language used in Power BI, Excel, and other Microsoft tools for creating custom calculations in measures and calculated columns.

• Characteristics:

  • DAX includes functions, operators, and constants to perform calculations and return results.

  • It supports a wide range of calculations, including mathematical, statistical, logical, and time-based functions.

• Examples:

  • Basic Calculation: Total Quantity = SUM(Sales[Quantity])

  • Conditional Calculation: Sales Growth = DIVIDE(SUM(Sales[CurrentYear]), SUM(Sales[PreviousYear])) - 1

  • Time Intelligence: Year-to-Date Sales = TOTALYTD(SUM(Sales[SalesAmount]), Dates[Date])

Importance of Measures and DAX in Power BI

• Data Analysis: Measures allow users to perform complex calculations that are essential for data analysis and reporting, enabling deeper insights into business performance.

• Dynamic Reporting: The context-sensitive nature of measures allows for dynamic reporting, where calculations adjust based on user interactions with visuals (like filters and slicers).

• Custom Metrics: DAX expressions provide the flexibility to create custom metrics tailored to specific business needs, enhancing the analytical capabilities of Power BI.

/INFO

---

QUESTION 8.

You need to create a relationship in the dataset for Row-level Security (RLS).

What should you do?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Create a Ⓟ relationship between 
the Sales Employees table 
and the Ⓠ .

---------- Select Choices ---------- 
Ⓟ:
(a)one-to-one 
(b)one-to-many 
(c)many-to-one 
(d)many-to-many

Ⓠ: 
(a)Orders table
(b)Suppliers table
(c)Order Details table 
(d)Customer Details worksheet

.

.

To create a relationship in the dataset for Row-Level Security (RLS), you should configure it as follows:

Create a Ⓟ one-to-many 
relationship between the Sales
 Employees table and the Ⓠ 
Orders table.

Reasons:

• Ⓟ (b) one-to-many: This type of relationship is appropriate because each sales employee can be associated with multiple orders, but each order is typically associated with only one sales employee.

• Ⓠ (a) Orders table: The Orders table contains the data that needs to be filtered based on the sales employees for RLS to work effectively.

INFO:

In Power BI, Row-Level Security (RLS) is a feature that enables you to restrict data access for specific users based on their roles or attributes. The concept of relationships in a dataset is crucial for implementing RLS effectively. Here’s how it works:

Key Concepts:

1. Relationships:

   • Relationships define how tables in your dataset are connected. In Power BI, you create relationships between tables to allow for data modeling that reflects real-world connections.

   • Typically, relationships are established through primary and foreign keys. For instance, a Sales table can be related to a Customers table via a CustomerID.

2. RLS Roles:

   • You create roles in Power BI that define the filters applied to the data. For each role, you specify the criteria that govern which rows of data a user can see.

   • Example: A role named "Sales Region" might filter the Sales table to show data only for the regions assigned to the user.

3. Using Relationships with RLS:

   • Relationships become crucial when implementing RLS because the filters applied through roles can affect multiple tables connected by relationships.

   • For instance, if a user is restricted to viewing data only for their specific region in the Sales table, the relationship with the Customers table ensures that any customer data displayed is also filtered according to the same region.

Implementation Steps:

1. Define Relationships:

   • Ensure that your dataset has defined relationships between tables. This is done in the Power BI Desktop model view.

2. Create Roles:

   • In Power BI Desktop, go to the "Modeling" tab and select "Manage Roles" to create roles with specific DAX filters.

   • Example DAX for filtering a Sales table:dax

       [Region] = USERPRINCIPALNAME()
     

3. Testing RLS:

   • Use the "View as Role" feature in Power BI Desktop to test how the report will appear to users with different roles.

4. Publish and Assign Users:

   • After publishing the report to Power BI Service, assign users or groups to the roles you created to enforce the RLS.

Importance of Relationships in RLS:

• Data Integrity: Relationships ensure that the filtering applied through RLS maintains the integrity of the data across related tables.

• Comprehensive Security: By leveraging relationships, RLS can enforce security at multiple levels, ensuring users see only relevant data based on their roles.

• Simplified Management: Properly defined relationships simplify the management of complex data models, making RLS easier to implement and maintain.

/INFO

---

QUESTION 9.

You need to create a solution to meet the notification requirements of the warehouse shipping department.

What should you do?

Select the appropriate options in the answer area.

NOTE: Each correct select is worth one point:

Populate a Ⓟ by using a card 
visualization that shows the 
percentage of late orders in 
the current month, and then 
configure a Ⓠ .

---------- Select Choices ---------- 
Ⓟ:
    (a)report
    (b)bookmark 
    (c)dashboard
 Ⓠ:
    (a) data alert
    (b) phone view
    (c)subscription

.

.

.

To meet the notification requirements of the warehouse shipping department, you should configure it as follows:

Populate a Ⓟ dashboard by 
using a card visualization 
that shows the percentage of 
late orders in the current 
month, and then configure a Ⓠ 
data alert.

Ⓟ: (c)dashboard

Ⓠ: (a) data alert

Why:

• Dashboard: A dashboard is ideal for providing a real-time overview of key metrics, such as the percentage of late orders.

• Data alert: Configuring a data alert ensures that the warehouse shipping department receives notifications when the percentage of late orders exceeds a certain threshold.

This setup will allow the warehouse shipping department to easily monitor the percentage of late orders and receive timely notifications when the percentage exceeds a certain threshold.

INFO:

In Power BI, notification requirements refer to the settings and features that enable users to receive alerts or updates about changes in their data or reports. Notifications are essential for keeping stakeholders informed and engaged with the most relevant insights. Here’s an overview of notification requirements in Power BI:

Key Aspects of Notifications in Power BI:

1. Data Alerts:

   • Users can set up alerts on specific metrics or KPIs within reports and dashboards. For example, if sales drop below a certain threshold, a user can receive an email notification.

   • Alerts can be configured to trigger when data reaches specific conditions, such as exceeding or falling below a defined value.

2. Subscriptions:

   • Users can create subscriptions to receive scheduled updates of reports and dashboards. This ensures that stakeholders regularly receive the latest data without having to manually check the report.

   • Subscriptions can be sent via email and can include snapshots of the report or dashboard.

3. Push Notifications:

   • For mobile users, Power BI allows for push notifications. Users can receive alerts on their mobile devices, making it easier to stay updated on critical changes while on the go.

4. Integration with Microsoft Teams:

   • Power BI can be integrated with Microsoft Teams, allowing teams to receive notifications directly within their Teams environment. This facilitates collaboration and quick access to insights.

5. Custom Notifications:

   • Through Power Automate, users can create custom workflows that respond to specific events in Power BI. For instance, if a dataset is refreshed, a notification can be sent to a designated group.

Importance of Notification Requirements:

• Timely Insights: Notifications help ensure that users are promptly informed about significant changes in data, allowing for quick decision-making.

• Increased Engagement: Regular updates and alerts keep users engaged with the data, encouraging them to explore and act on insights.

• Data-Driven Culture: By facilitating easy access to critical information, notifications promote a culture where data-driven decisions are prioritized.

• Customization: The ability to tailor notifications according to user preferences ensures that stakeholders receive only the information relevant to their roles.

/INFO

---

QUESTION 10.

You need to design the data model and the relationships for the Customer Details worksheet and the Orders table by using Power BI. The solution must meet the report requirements.

For each of the following statement, select Yes if the statement is true, Otherwise, select No.

NOTE: Each correct selection is worth one point.

[1] A relationship must be 
created between the CustomerID
 column in the Customer 
Details worksheet and the 
CustomerID column in the 
Orders table (Y/N).

[2] The Data Type of the 
columns in the relationship 
between the Customer Details 
worksheet and the Orders table
 must be set to Text (Y/N).

[3] The Region field used to 
filter the Top Customers 
report must come from the 
Orders table (Y/N).

.

.

.

(1) A relationship must be created between the CustomerID column in the Customer Details worksheet and the CustomerID column in the Orders table: • Yes. Creating a relationship between these columns is essential to link customer details with their respective orders.

(2) The Data Type of the columns in the relationship between the Customer Details worksheet and the Orders table must be set to Text: • No. The data type should be consistent, but it doesn't necessarily have to be Text. It could be a numeric type as well, as long as both columns have the same data type.

(3) The Region field used to filter the Top Customers report must come from the Orders table: • No. The Region field should come from the Customer Details worksheet, as it is more likely to contain customer-specific information such as their region.

INFO:

In Power BI, models and relationships are fundamental concepts that enable effective data analysis and visualization. Here’s an overview of each:

Model

1. Definition:

   • A model in Power BI refers to the structured representation of your data, including tables, fields, and the relationships between them. It serves as the foundation for data analysis and reporting.

2. Components:

   • Tables: Collections of data organized in rows and columns.

   • Fields: Individual data points or attributes within tables (e.g., sales amount, customer name).

   • Calculated Columns and Measures: Custom calculations created using DAX (Data Analysis Expressions) to derive new insights from existing data.

3. Data Sources:

   • Models can incorporate data from various sources, including databases, Excel files, cloud services, and more. Power BI allows users to import, transform, and load data into the model.

4. Data Transformation:

   • Using Power Query, users can clean, shape, and transform data before loading it into the model, ensuring that the data is in the desired format for analysis.

Relationships

1. Definition:

   • Relationships define how tables in a Power BI model are connected to one another. They establish logical connections that allow for effective data analysis across multiple tables.

2. Types of Relationships:

   • One-to-Many (1:N): One record in a primary table relates to multiple records in a secondary table (e.g., one customer can have many orders).

   • Many-to-One (N:1): Multiple records in a primary table relate to one record in a secondary table (e.g., many orders can belong to one customer).

   • Many-to-Many (N:N): Multiple records in one table relate to multiple records in another table. This is more complex and requires careful modeling.

3. Creating Relationships:

   • Relationships can be created in the Power BI Desktop model view by dragging and dropping fields between tables. Users can also define relationships manually.

4. Cross-Filtering:

   • Power BI allows for cross-filtering between related tables. This means that if a user filters one table, the related tables will automatically adjust to reflect that filter.

5. Cardinality and Direction:

   • Cardinality refers to the type of relationship (one-to-many, many-to-one, etc.).

   • Direction indicates how filters flow between the tables—whether filters applied to one table should also filter related tables.

Importance of Models and Relationships

• Data Integrity: Properly structured models and relationships ensure that data is consistent and accurate across different reports and analyses.

• Enhanced Analysis: Relationships enable users to perform complex queries and analyses across multiple tables, deriving richer insights from interconnected data.

• Efficient Reporting: By configuring models and relationships effectively, users can create dynamic reports that respond to user interactions, providing a more engaging experience.

• Scalability: Well-designed models can scale with the organization’s data needs, accommodating new data sources and relationships as they arise.

/INFO

---

QUESTION 11.

You need to create a Power BI visual as shown in the following exhibit.

The indicator color for Total Sales will be based on % Growth to Last Year. The solution must use the existing calculations only.

How should you configure the visual?

Select the appropriate options in the answer area.

[1] Conditional formatting:
    (a)Background color, 
    (b)Data bars, 
    (c)Font color, 
    (d)Icons, 
    (e)Web URL  

[2] Format by: 
    (a)Color scale, 
    (b)Field value, 
    (c)Rules

.

.

.

To configure the table visual with varying background colors for the Total Sales column based on % Growth to Last Year, you should set up the following options:

[1] Conditional formatting: • (a)Background color

[2] Format by: • (c)Rules

Step-by-step guide:

1. Select the Total Sales column in your table visual.

2. Go to the Format pane and find the Conditional formatting section.

3. Choose Background color.

4. In the Format by dropdown, select Rules.

5. Define the rules based on the % Growth to Last Year to set the background colors accordingly.

This setup will ensure that the background color of the Total Sales column changes based on the specified growth percentage, using the existing calculations.

INFO:

In Power BI, conditional formatting is a powerful feature that allows you to change the appearance of visual elements based on the values in your data. This helps to highlight important insights and trends, making reports more intuitive and informative. Here’s a detailed explanation of conditional formatting and rules in Power BI:

Conditional Formatting

1. Definition:

   • Conditional formatting alters the visual representation of data points based on specific conditions or rules. It can be applied to various visualizations, including tables, matrices, charts, and cards.

2. Types of Formatting:

   • Data Bars: Adds horizontal bars within cells to represent the value's magnitude visually.

   • Color Scales: Uses gradients of color to represent a range of values, making it easy to identify high and low values.

   • Icons: Displays icons (like arrows or traffic lights) to indicate trends or status based on the value.

   • Font Color and Background Color: Changes the text color or background color of cells based on the value.

3. Application:

   • You can apply conditional formatting through the formatting pane of a visualization. Select the field you want to format, choose the type of formatting, and set the rules.

Rules in Conditional Formatting

1. Definition:

   • Rules are the specific conditions that determine how the formatting is applied. For example, you might set a rule to change a cell's color if its value exceeds a certain threshold.

2. Creating Rules:

   • When setting up conditional formatting, you can define rules based on numerical values, text values, or dates. Common examples include:

     • Greater Than: Format if the value is greater than a specified number.

     • Less Than: Format if the value is less than a specified number.

     • Between: Apply formatting if the value falls within a defined range.

     • Equal To: Format if the value matches a specific number or text.

3. Example:

   • If you have a sales report, you might set a rule to highlight any sales figures below $10,000 in red, while those above this threshold are highlighted in green. This immediately draws attention to underperforming sales.

Importance of Conditional Formatting

• Enhanced Data Visualization: Conditional formatting makes it easier to visualize key metrics and trends at a glance, improving the overall effectiveness of reports.

• Focus on Insights: By highlighting important data points, users can quickly identify areas that need attention or further analysis.

• Customizability: Users can tailor the formatting to their specific needs, ensuring that the report conveys the most relevant information to its audience.

• Interactivity: Conditional formatting can enhance the interactivity of reports, guiding users to explore data more effectively.

/INFO

---

QUESTION 12.

You are creating a Quick Measure for Rolling Average. You need to create a monthly rolling average measure for Sales over time.

How should you configure the quick measure calculation?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Base value: 
    (a) Month 
    (b) Total Cost 
    (c) Total Order Qty 
    (d) Total Sales
    (e) Year 

[2] Date: 
    (a) Date 
    (b) Month 
    (c) Total Sales 
    (d) Year 

[3] Period: 
    (a) Days 
    (b) Month 
    (c) Quarters 
    (d) Years

.

.

.

To create a monthly rolling average measure for Sales over time, you should configure the quick measure calculation as follows:

[1] Base value: • (d)Total Sales

[2] Date: • (a)Date

[3] Period: • (b)Month

This setup will calculate the rolling average of total sales on a monthly basis, using the date field to determine the time periods.

INFO:

Quick Measure in Power BI

A Quick Measure in Power BI is a feature that allows users to create calculations easily without writing DAX (Data Analysis Expressions) manually. It provides a guided interface where users can select from predefined calculations, making it accessible, especially for those who may not be familiar with DAX.

Key Features:

• User-Friendly: Quick Measures are designed for simplicity, allowing users to select the type of calculation they want and specify input fields.

• Automatic DAX Generation: Power BI generates the appropriate DAX formula behind the scenes, which can be reviewed and modified by users if desired.

• Common Use Cases: Quick Measures cover various analytical needs, from basic aggregations to more complex calculations.

Types of Quick Measures

Power BI includes several types of Quick Measures, such as:

1. Aggregations:

   • Sum: Total of a column.

   • Average: Average value of a column.

   • Count: Counts the entries in a column.

2. Time Intelligence:

   • Year-to-Date (YTD): Cumulative values from the beginning of the year to the current date.

   • Month-to-Date (MTD): Values from the start of the month to the current date.

   • Previous Year: Comparison of current year values to the previous year.

3. Running Total:

   • Cumulative total over a specified time period.

4. Difference:

   • Calculates the difference between two values, useful for period comparisons.

5. Percentage of Total:

   • Determines the percentage contribution of a specific value to a total.

6. Ranking:

   • Ranks items within a category based on a specified measure.

Rolling Average

A Rolling Average (or moving average) is a statistical method used to analyze data points by creating averages of different subsets of the complete data set. This helps smooth out short-term fluctuations and highlight longer-term trends.

How it Works:

• The rolling average is calculated by averaging a specific number of data points over a defined period. As new data is added, the oldest data points are dropped from the calculation.

• For example, a 3-month rolling average of sales would average the sales figures for the most recent three months.

Importance:

• Trend Analysis: Rolling averages help identify trends over time, making it easier to see long-term patterns.

• Noise Reduction: They reduce the impact of short-term fluctuations, providing a clearer view of underlying trends.

Quick Measure Containing Rolling Average

The Rolling Average is typically categorized under Time Intelligence Quick Measures in Power BI. Users can create a rolling average by selecting the appropriate Quick Measure option that allows for calculations based on a specified period (e.g., weeks, months).

/INFO

---

QUESTION 13.

You are creating an analytics report that will consume data from the tables shown in the following table:

There is a relationship between the tables. There are no reporting requirements on employee_id and employee_photo. You need to optimize the data model.

What should you configure for employee_id and employee_photo?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Employee_id: 
    (a) Change Type
    (b) Delete
    (c) Hide
    (d) Sort  

[2] Employee_photo: 
    (a) Change Type
    (b) Delete
    (c) Hide
    (d) Sort

.

.

.

To optimize the data model given there are no reporting requirements on employee_id and employee_photo, you should configure the following:

[1] Employee_id: • (c) Hide

[2] Employee_photo: • (b) Delete

Hiding employee_id will keep it in the model for relationships but prevent it from cluttering the report view. Deleting employee_photo will remove unnecessary data, optimizing storage and performance.

INFO:

In Power BI, hide and delete are two different actions that can be performed on elements within a data model, such as tables, columns, or measures. Understanding the distinction between these actions is important for effective data management and report design.

Hide

1. Definition:

   • Hiding an element means making it invisible in the report view and the fields pane, while still keeping it in the data model.

2. Use Cases:

   • Simplification: Hiding unnecessary or intermediate columns that are not relevant to end users helps simplify the user interface.

   • Internal Calculations: You might want to keep calculated columns or measures that are used for internal calculations but do not need to be visible to report consumers.

3. How to Hide:

   • To hide a column or measure, right-click on it in the fields pane and select "Hide in report view." The element will still exist in the data model and can be used in calculations, but it won’t be visible to users when creating reports.

Delete

1. Definition:

   • Deleting an element permanently removes it from the data model. Once deleted, it cannot be recovered unless you have a backup or can recreate it.

2. Use Cases:

   • Data Cleanup: When certain columns or tables are no longer needed, deleting them helps maintain a clean and efficient data model.

   • Performance Improvement: Removing unused or redundant elements can improve performance and reduce the complexity of the model.

3. How to Delete:

   • To delete a column or table, right-click on it in the fields pane or the model view and select "Delete." This action will prompt a confirmation to ensure that you really want to remove the element.

Key Differences

• Visibility: Hiding an element keeps it in the data model but makes it invisible to report users, while deleting removes it entirely from the model.

• Recovery: Hidden elements can be unhidden later, keeping the data intact, while deleted elements cannot be recovered unless recreated.

• Impact on Performance: Deleting unnecessary elements can enhance performance, whereas hiding does not impact performance but can simplify user interaction.

/INFO

---

QUESTION 14.

You are creating a Microsoft Power BI model that has two tables named CityData and Sales.

CityData contains only the data shown in the following table.

Sales contains only the data shown in the following table.

CityData and Sales are related using a many-to-many relationship based upon the State column in each table.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

[1] In the Sales table, you 
can write a DAX expression 
that uses the RELATED() 
function to get data from the 
CityData table (Y/N).

[2] A DAX expression of sales 
total=CALCULATE(SUM(Sales[Sales]),All(Sales))
 will produce the correct 
total sales value for each 
state, based on the data model
 (Y/N).

[3] A table visualization that
 uses CityData[State] and 
Sales[Sales] will contain 
sales from the state of TX 
(Y/N).

.

.

.

[1] In the Sales table, you can write a DAX expression that uses the RELATED() function to get data from the CityData table: No • The RELATED() function requires a one-to-many or many-to-one relationship. Since the relationship between CityData and Sales is many-to-many, RELATED() cannot be used directly.

[2] A DAX expression of sales_total = CALCULATE(SUM(Sales[Sales]), ALL(Sales)) will produce the correct total sales value for each state, based on the data model: Yes • The ALL(Sales) function removes any filters from the Sales table, allowing the SUM(Sales[Sales]) to calculate the total sales value without any filter context.

[3] A table visualization that uses CityData[State] and Sales[Sales] will contain sales from the state of TX: No • Since there is no corresponding State entry for TX in the CityData table, the many-to-many relationship will not include sales data for TX in the visualization.

INFO:

In Power BI, a many-to-many relationship is a type of relationship where multiple records in one table can be related to multiple records in another table. This relationship is more complex than one-to-many or many-to-one relationships and requires careful management to ensure accurate data modeling and analysis.

Understanding Many-to-Many Relationships

1. Definition:

   • In a many-to-many relationship, for example, a Students table and a Courses table can have multiple students enrolled in multiple courses. This means that:

     • Each student can enroll in multiple courses.

     • Each course can have multiple students.

2. Implementation:

   • Power BI allows many-to-many relationships by creating a bridge table (also known as a junction table) to manage the connections between the two tables. For instance, a StudentCourses table could be created to relate Students and Courses.

Impact on DAX Expressions

1. Complexity:

   • Many-to-many relationships can complicate DAX calculations. For example, when calculating totals, averages, or other aggregates, DAX may need to account for the multiple connections, which can lead to ambiguous results.

2. FILTER Context:

   • DAX expressions that rely on filters may behave differently in a many-to-many scenario. You need to ensure that the correct context is applied, which may require more complex DAX formulas to handle the relationships properly.

3. Use of Functions:

   • Functions like CALCULATE, RELATED, and VALUES may need to be carefully crafted to manage the many-to-many relationships effectively, ensuring that the right data is being filtered and aggregated.

Impact on Table Visualization

1. Data Representation:

   • In a table visual, many-to-many relationships can result in duplicated rows or unexpected aggregations if not managed properly. For example, if you display student names alongside course names, you may see repeated student names for each course they are enrolled in.

2. Data Integrity:

   • Visuals may misrepresent the data if the relationships are not properly defined or if the bridge table is not used effectively. This can lead to confusion and misinterpretation of the data.

3. Handling Ambiguities:

   • To mitigate issues, it's often necessary to use measures that explicitly define how to aggregate the data, ensuring that the visuals accurately represent the intended analysis.

Best Practices

• Use Bridge Tables: To manage many-to-many relationships effectively, create a bridge table that connects the two related tables.

• Review DAX Calculations: Carefully review and test DAX expressions to ensure they yield the correct results in the context of the many-to-many relationship.

• Test Visual Outputs: Validate that visual representations accurately reflect the underlying data model, especially when dealing with aggregated values.

/INFO

---

QUESTION 15.

You are creating a Microsoft Power Bl imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories. You import a fact table named Sales. The related dimension tables are imported into the model.

Select Yes or No for the following statement.

[1] The SalesRowID and AuditID
 columns can be removed from 
the model without impeding 
analysis goals (Y/N).

[2] Both the OrderDateKey and 
OrderDate columns are 
necessary to perform the 
basket analysis (Y/N).

[3]The TaxAmt column must 
retain the current number of 
decimal places to perform 
basket analysis (Y/N).

.

.

.

[1] The SalesRowID and AuditID columns can be removed from the model without impeding analysis goals: Yes • These columns are typically used for internal tracking and auditing purposes and are not necessary for basket analysis.

[2] Both the OrderDateKey and OrderDate columns are necessary to perform the basket analysis: No • Generally, only one date column is needed for analysis. The OrderDateKey is often a surrogate key used for relationships, while OrderDate is the actual date. You can choose one based on your model's requirements.

[3] The TaxAmt column must retain the current number of decimal places to perform basket analysis: No • Basket analysis focuses on identifying product combinations rather than precise financial calculations. Therefore, the exact number of decimal places for TaxAmt is not critical for this type of analysis

INFO:

Basket Analysis in Power BI

Basket Analysis (also known as Market Basket Analysis) is a data mining technique used to understand the purchasing behavior of customers by analyzing the co-occurrence of items in transactions. This analysis helps businesses identify patterns in product purchases, which can inform marketing strategies, product placement, and inventory management.

Purpose of Basket Analysis

• Identify Associations: Determine which products are frequently bought together.

• Improve Cross-Selling: Enhance marketing strategies by suggesting complementary products to customers.

• Optimize Layout: Inform store layouts or online product placements based on item associations.

Typical Fields Required for Basket Analysis

For effective basket analysis, certain fields are typically required:

1. Transaction ID: A unique identifier for each transaction or purchase event. This helps group items that were bought together.

2. Product ID/Name: Identifies the products purchased in each transaction. This can be a unique product ID or the product name itself.

3. Quantity: The number of units purchased for each product in the transaction. This helps quantify the strength of the association.

4. Transaction Date: The date of the transaction, which can help analyze trends over time.

5. Customer ID (optional): If available, this can provide insights into customer behavior and preferences.

Typical Fields Not Required for Basket Analysis

While certain fields are essential, others are typically not required for basket analysis:

1. Product Price: While relevant for revenue analysis, the price of individual products is not necessary for identifying associations between products.

2. Customer Demographics: Information such as age, gender, or location may not be directly relevant for basket analysis itself, although it can enhance insights when segmented analysis is performed.

3. Payment Method: Details about how a customer paid (e.g., credit card, cash) are usually not relevant for understanding product associations.

4. Promotions/Discounts: While these may influence purchasing behavior, they are not essential for basic basket analysis; however, they can be considered in a more advanced context.

/INFO

---

QUESTION 16.

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

[1] Selecting a quarter on the
 line chart will:
(a) cross-filter
(b) cross-highlight
(c) not affect

[2] Selecting a data point on 
the Tailspin Toys line on the 
line chart will:
(a) cross-filter
(b) cross-highlight
(c) not affect

.

.

.

[1] Selecting a quarter on the line chart will: • (a) Cross-filter: This will filter the data in other visuals based on the selected quarter.

[2] Selecting a data point on the Tailspin Toys line on the line chart will: • (b) Cross-highlight: This will highlight the corresponding data points in other visuals, showing the relationship between the selected data point and the rest of the data.

Reasons:

Cross-Filtering Cross-filtering allows you to filter data in other visuals based on a selection in one visual. For example, if you select a specific quarter in a line chart, all other visuals on the report page will update to show data relevant to that quarter.

Sample Data Let's say we have the following sales data:

Sales Data

If you select "2024-Q1" on a line chart, other visuals (like bar charts or tables) will only show data for "2024-Q1".

Cross-Highlighting Cross-highlighting highlights the selected data point in one visual and shows its relationship to the rest of the data in other visuals. This is useful for comparing a specific data point against the overall dataset.

Sample Data Using the same sales data, if you select "Product A" in a line chart, other visuals will highlight the sales of "Product A" while still showing the sales of other products in a lighter shade.

Sales Data

If you select "Product A" in a line chart, a bar chart might show: • Product A: $22,000 (highlighted)

• Product B: $33,000 (dimmed)

Visual Example

Imagine you have a line chart showing sales over quarters and a bar chart showing sales by product. Selecting a quarter on the line chart will filter the bar chart to show only sales for that quarter (cross-filtering). Selecting a product on the bar chart will highlight that product's sales in the line chart while still showing the overall sales trend (cross-highlighting).

The suitability of data for cross-filtering and cross-highlighting in Power BI depends on the relationships and the type of analysis you want to perform. Here are some guidelines to help you determine which data is suitable:

Cross-Filtering Suitable Data: • Categorical Data: Data that can be grouped into categories, such as dates, regions, or product types.

• Hierarchical Data: Data that has a natural hierarchy, like Year > Quarter > Month or Country > State > City.

• Related Tables: Data that is connected through relationships in your data model, such as sales data linked to product or customer data.

Example: • Sales Data by Quarter: Selecting a specific quarter will filter other visuals to show data only for that quarter.

• Region-Based Data: Selecting a region will filter other visuals to show data only for that region.

Cross-Highlighting Suitable Data: • Quantitative Data: Data that can be measured and compared, such as sales amounts, quantities, or ratings.

• Detailed Data: Data that provides detailed insights, such as individual product sales or customer transactions.

• Comparative Data: Data that benefits from comparison, such as sales performance across different products or regions.

Example: • Product Sales: Selecting a product in a bar chart will highlight that product's sales in other visuals, showing its contribution to overall sales.

• Customer Segments: Selecting a customer segment will highlight the segment's data in other visuals, showing its impact on overall metrics.

Practical Tips • Understand Relationships: Ensure your data model has the necessary relationships set up correctly. This is crucial for cross-filtering to work effectively.

• Use Appropriate Visuals: Choose visuals that support cross-filtering and cross-highlighting, such as bar charts, line charts, and scatter plots.

• Test Interactions: Experiment with different selections to see how they affect other visuals. This will help you understand the impact of cross-filtering and cross-highlighting on your data.

INFO:

In Power BI, cross-filtering and cross-highlighting are interactive features that enhance data exploration and visualization by allowing users to see relationships and impacts between different visual elements. Here’s a detailed explanation of both concepts:

Cross-Filtering

1. Definition:

   • Cross-filtering allows a visual element (like a chart or table) to filter data in other visuals on the report page. When you select a data point in one visual, it influences the data displayed in others.

2. How It Works:

   • For example, if you have a bar chart showing sales by region and a table listing individual sales transactions, clicking on a specific bar (representing a region) will filter the table to display only the transactions from that region.

3. Use Cases:

   • Cross-filtering is useful for drilling down into data, allowing users to focus on specific segments and understand how they relate to other metrics.

   • It helps in scenarios where users want to analyze data across multiple dimensions (e.g., sales performance by product category and region).

Cross-Highlighting

1. Definition:

   • Cross-highlighting is a related feature that visually emphasizes relevant data points in other visuals based on the selection made in one visual. Instead of filtering out data, it highlights specific elements.

2. How It Works:

   • For instance, if you have a pie chart showing sales distribution and you click on a slice representing a specific product, the corresponding bars in a related bar chart may change color to highlight that product’s performance while leaving other data points visible.

3. Use Cases:

   • Cross-highlighting is beneficial for comparing data across different visuals without losing context. It allows users to see how selected elements relate to the overall dataset.

   • This feature is particularly useful in dashboards where users want to maintain a broader view while focusing on specific details.

Key Differences

• Functionality:

  • Cross-filtering modifies the data shown in other visuals by applying filters based on the selection.

  • Cross-highlighting, on the other hand, emphasizes or highlights the related data in other visuals while keeping all data visible.

• User Experience:

  • Cross-filtering often leads to a more restricted view, focusing only on the relevant data, whereas cross-highlighting maintains a broader context, allowing for comparative analysis.

/INFO

---

QUESTION 17.

You have two Azure SQL databases that contain the same tables and columns. For each database, you create a query that retrieves data from a table named Customer. You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support scheduled refresh in powerbi.com.

What should you do?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Option to use to combine 
the Customer tables: 
    (a) Append Queries 
    (b) Append Queries as New 
    (c) Merge Queries
    (d) Merge Queries as New

[2] Action to perform on the 
original two SQL database 
queries: 
    (a) Delete the queries
    (b) Disable including the query in the report refresh
    (c) Disable loading the query to the data model 
    (d) Duplicate the queries

.

.

.

To combine the Customer tables from two Azure SQL databases into a single table while minimizing the size of the data model and supporting scheduled refresh in Power BI, you should:

[1] Option to use to combine the Customer tables: • (a) Append Queries

[2] Action to perform on the original two SQL database queries: • (a) Disable loading the query to the data model

Reasons:

• Append Queries: This option combines the rows from both Customer tables into a single table, which is efficient for your data model.

• Disable loading the query to the data model: This action ensures that the original queries do not load their data into the model, thus minimizing the size of the data model while still allowing you to use the combined data.

INFO:

In Power BI, when working with data from an Azure SQL Database, the concepts of append and merge relate to how data is combined and transformed during the data preparation process. Additionally, there are scenarios where you may need to disable query loading. Here’s a detailed explanation of these aspects:

Azure SQL Database and Data Operations

Append and Merge

1. Append:

   • What It Is: Appending combines multiple tables vertically by stacking their rows into a single table. This is useful when you have datasets with the same structure (e.g., sales data for different months).

   • How It Works: In Power BI’s Power Query Editor, the Append Queries feature allows you to select multiple tables from the Azure SQL Database and combine them. This operation maintains all columns, and any discrepancies in column names are handled automatically.

2. Merge:

   • What It Is: Merging combines two tables horizontally based on a common key (e.g., a customer ID). This enriches one dataset with related information from another.

   • How It Works: The Merge Queries feature in Power Query enables users to join tables from the Azure SQL Database based on matching columns. This creates a new table that includes columns from both datasets, facilitating more comprehensive analysis.

Disabling Query Loading

1. What It Means: Disabling query loading prevents specific queries from being loaded into the Power BI data model while still allowing them to be used for transformations or as intermediate steps in the data preparation process.

2. When to Disable Query Loading:

   • Intermediate Queries: If you create intermediate queries for transformations (e.g., cleaning data, aggregating results) that do not need to be directly loaded into the model, you can disable loading for these queries. This helps keep the data model clean and efficient.

   • Performance Optimization: By reducing the number of queries loaded into the model, you can improve performance, especially when dealing with large datasets. This minimizes memory usage and speeds up report performance.

   • Complex Workflows: In complex data preparation workflows where multiple transformations occur, you may have several queries that serve as steps in a transformation process. Disabling loading for these queries helps streamline the final output and maintain focus on the necessary datasets.

3. How to Disable Query Loading:

   • In Power Query Editor, you can right-click on a query and select "Enable Load" to toggle this option off, preventing the query from being included in the data model.

/INFO

---

QUESTION 18.

You have a folder of monthly transaction extracts. You plan to create a report to analyze the transaction data. You receive the following email message:

“Hi, I’ve put 24 files of monthly transaction data onto the shared drive. File Transactions201601.csv through Transaction201612.csv have four columns while files Transactions201701.csv through Transaction201712.csv have the same four columns plus an additional three columns*. Each file contains 10 to 50 transactions.”*

You get data from the folder and select Combine & Load.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

[1] The resulting query will 
contain all the columns from 
2016 transactions (Y/N).

[2] The resulting query will 
contain all the columns from 
2017 transactions (Y/N).

[3] Setting Data Type 
Detection to Based on first 
200 rows will improve import 
times (Y/N).

.

.

.

[1] The resulting query will contain all the columns from 2016 transactions. • Yes. Since the columns are based on the entire dataset, the resulting query will include all columns from both 2016 and 2017 transactions.

[2] The resulting query will contain all the columns from 2017 transactions. • Yes. The additional columns from the 2017 files will be detected and included in the resulting query.

[3] Setting Data Type Detection to Based on first 200 rows will improve import times. • Yes. Limiting data type detection to the first 200 rows can still reduce processing time, thereby improving import times.

INFO:

Data Loading via Folder in Power BI

Data loading via folder in Power BI allows users to import multiple files from a specified folder into a single dataset. This is particularly useful when dealing with similar files (e.g., CSV, Excel) that share a common structure but may contain variations.

How It Works

1. Connect to Folder:

   • Users select "Get Data" in Power BI and choose the "Folder" option. They then specify the path to the folder containing the files to be loaded.

2. Combine Files:

   • Once connected, Power BI displays a list of files in that folder. Users can choose to combine these files into a single table, which is typically done using Power Query.

3. Data Transformation:

   • After combining, users can transform the data (e.g., cleaning, filtering, reshaping) to prepare it for analysis.

Scenario: Table1 has Fewer Fields than Table2

When loading data from multiple files where Table1 has fewer fields than Table2, here's what happens:

1. Combine Operation:

   • During the combine operation, Power BI attempts to align the columns based on their names. If Table1 is missing some columns that are present in Table2, Power BI will handle this by creating those columns in the combined output.

2. Resulting Data Structure:

   • The combined table will include all unique columns from both tables. Columns that are present in Table2 but not in Table1 will be included in the final dataset, but for the rows originating from Table1, those columns will contain null values (or blanks) for each row.

3. Example:

   • If Table1 has columns: Date, Sales

   • And Table2 has columns: Date, Sales, Region

   • The combined result will have columns: Date, Sales, Region. For rows from Table1, the Region column will show null.

Considerations

• Data Quality: Handling varying fields can lead to a dataset with many null values, which may affect analysis and reporting.

• Schema Consistency: To ensure a smoother process, it’s best practice to standardize the schema of all files before loading or to handle inconsistencies within Power Query.

• Transformations: Users can apply transformations to manage the presence of null values, such as replacing them with default values or filtering them out.

/INFO

---

QUESTION 19.

You view a query named Transactions as shown in the following exhibit:

The query gets CSV files from a folder.

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

[1]  There are __ CSV files. 
    (a) 9 
    (b) 10 
    (c) 25 
    (d) 90 
    (e) 1000

[2] Removing duplicates based 
on the Date column will reduce
 the dataset to __ .
    (a) 9 
    (b) 10 
    (c) 25 
    (d) 90 
    (e) 1000

.

.

.

Based on the information provided in the exhibit:

[1] There are:
• (a) 9 CSV files.

• This is inferred from the "Source.Name" field, which shows 9 distinct values.

[2] Removing duplicates based on the Date column will reduce the dataset to:
• (b) 10 rows.

• This is inferred from the "Date" field, which shows 10 distinct values.

INFO:

In Power BI, the terms distinct and unique in the context of a Column Profile refer to different aspects of data analysis, though they are often used interchangeably in casual conversation. Here’s a detailed explanation of each term and the implications of removing duplicates:

Distinct vs. Unique in Power BI Column Profile

1. Distinct:

   • Definition: The term "distinct" refers to the set of unique values within a column, where duplicates are not counted. For example, if a column contains the values {A, A, B, C}, the distinct values would be {A, B, C}.

   • Count: The Column Profile will show the count of distinct values, giving insight into the diversity of the data.

2. Unique:

   • Definition: "Unique" often refers to values that appear only once in the dataset. Using the same example {A, A, B, C}, the unique values would be {B, C} since they occur only once.

   • Count: The Column Profile may also show the count of unique values separately, helping to identify truly one-off entries.

Effects of Removing Duplicates

When duplicates are removed from a dataset, several effects can occur:

1. Data Accuracy:

   • Improved Distinct Count: Removing duplicates ensures that the distinct count accurately reflects the number of unique values, which is essential for analysis and reporting.

2. Clarity in Insights:

   • Simplified Reporting: By focusing on distinct values, reports become clearer. For instance, understanding how many unique customers made purchases versus the total number of transactions can provide better insights into customer behavior.

3. Changes in Metrics:

   • Impact on Aggregates: Removing duplicates can significantly alter calculated metrics (e.g., averages, sums), as aggregations may previously have included inflated counts due to duplicates. For example, calculating the average sales per customer is affected if duplicates are counted.

4. Loss of Context:

   • Contextual Information: While distinct values enhance clarity, removing duplicates may obscure important context, like the frequency of purchases. Knowing how often a product is sold can be critical for inventory decisions.

5. Data Quality Insights:

   • Identifying Issues: Duplicates can sometimes indicate data quality problems (e.g., data entry errors). Removing them without understanding the underlying issues could lead to a loss of valuable insights into data integrity.

/INFO

---

QUESTION 20.

You have an API that returns more than 100 columns. The following is a sample of column names.

1. client_notified_timestamp

2. client_notified_source

3. client_notified_sourceid

4. client_notified_value

5. client_responded_timestamp

6. client_responded_source

7. client_responded_sourceid

8. client_responded_value

You plan to include only a subset of the returned columns. You need to remove any columns that have a suffix of sourceid.

How should you complete the Power Query M code?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

let
    Source = ... ,
    rawData = Source { [tableId = "clientData"] } [Data],
    removeSources = 
        Ⓟ (
            rawData,
                Ⓠ (
                    Table.ColumnNames(rawData),
                    each Ⓡ (_, "sourceid")
                )
        )
in
    removeSources

---------- Select Choices ---------- 
Ⓟ:
    (a) Table.CombineColumn
    (b) Table.FindText
    (c) Table.FromList
    (d) Table.RemoveColumns
Ⓠ:
    (a) List.Contains
    (b) List.Select
    (c) Table.FindText
    (d) Table.FromList
Ⓡ: 
    (a) Text.Contains
    (b) Text.EndsWith
    (c) Text.From
    (d) Text.StartsWith

.

.

.

To remove any columns that have a suffix of "sourceid" in Power Query M code, you can complete the code as follows:

let
    Source = ... ,
    rawData = Source { [tableId = "clientData"] } [Data],
    removeSources = 
        Table.RemoveColumns(
            rawData,
                List.Select(
                    Table.ColumnNames(rawData),
                    each Text.EndsWith(_, "sourceid")
                )
        )
in
    removeSources

Breakdown of the options:

• (d) Table.RemoveColumns: This function is used to remove columns from a table.

• (b) List.Select: This function is used to filter the list of column names.

• Table.ColumnNames: This function retrieves the column names from the table.

• (b) Text.EndsWith: This function checks if the column name ends with the specified suffix "sourceid".

INFO:

What is Power Query M Code?

Power Query M Code is a formula language used in Power BI, Excel, and other Microsoft services to define data transformations and queries. M is the language behind the Power Query Editor, allowing users to perform various data manipulation tasks such as importing, cleaning, reshaping, and aggregating data from different sources.

Key Features of M Code

1. Data Transformation:

   • M Code allows users to define a series of transformations that can be applied to data. This includes filtering rows, renaming columns, changing data types, and merging tables.

2. Query Definition:

   • M Code is used to define queries that pull data from various sources, including databases, files, and web services. It specifies how to connect to these sources and what data to retrieve.

3. Integration with Power BI:

   • In Power BI, M Code is generated when you perform actions in the Power Query Editor, such as applying filters or merging tables. Users can also write or modify M Code directly in the Advanced Editor for more complex transformations.

4. Functional Language:

   • M is a functional programming language, meaning it treats computation as the evaluation of mathematical functions and avoids changing state or mutable data. This approach allows for powerful data manipulation capabilities.

What Does M Mean?

The M in Power Query M Code does not have a formally defined meaning, but it is often thought to stand for "Mashup". This reflects the language's purpose in combining and transforming data from multiple sources into a cohesive dataset for analysis.

Examples of M Code

Here are a few simple examples of M Code snippets:

1. Basic Query to Load Data:

    let
        Source = Excel.Workbook(File.Contents("C:\path\to\file.xlsx"), null, true)
    in
        Source
   

2. Filtering Rows:

    let
        Source = Table.FromRecords({[Name="Alice", Age=30], [Name="Bob", Age=25]}),
        FilteredRows = Table.SelectRows(Source, each [Age] > 27)
    in
        FilteredRows
   

3. Adding a Custom Column:

    let
        Source = Table.FromRecords({[Sales=100], [Sales=200]}),
        AddedColumn = Table.AddColumn(Source, "DoubleSales", each [Sales] * 2)
    in
        AddedColumn
   

/INFO

---

QUESTION 21.

Your company has affiliates who help the company acquire customers. You build a report for the affiliate managers at the company to assist them in understanding affiliate performance. The managers request a visual showing the total sales value of the latest 50 transactions for each affiliate. You have a data model that contains the following tables.

You need to develop a measure to support the visual.

How should you complete the DAX expression?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Revenue Last 50 Transactions =
Ⓟ (
    Ⓠ ( Transactions[Amount] ),
    Ⓡ ( 50, Transactions, Transactions Ⓢ , DESC )
)

---------- Select Choices ---------- 
Ⓟ: (a)CALCULATE (b)CONCATENATEX (c)SUM (d)SUMX (e)TOPN
Ⓠ: (a)CALCULATE (b)CONCATENATEX (c)SUM (d)SUMX (e)TOPN 
Ⓡ: (a)CALCULATE (b)CONCATENATEX (c)SUM (d)SUMX (e)TOPN
Ⓢ: (a)[TransactionID] (b)[Amount] (c)[ItemsOrdered] (d)[TransactionDate] .

.

.

.

To create a measure that shows the total sales value of the latest 50 transactions for each affiliate, you can complete the DAX expression as follows:

Revenue Last 50 Transactions =
CALCULATE (
    SUM ( Transactions[Amount] ),
    TOPN ( 50, Transactions, Transactions[TransactionDate], DESC )
)

Reasons:

[1] CALCULATE: This function is used to modify the context of the calculation.

[2] SUM: This function is used to sum the Amount column.

[3] TOPN: This function is used to get the top 50 rows based on the TransactionDate in descending order.

So, the correct selections are:

• CALCULATE

• SUM

• TOPN

• TransactionDate

---

QUESTION 22.

You are creating a Microsoft Power BI data model that has the tables shown in the following table.

The Products table is related to the ProductCategory table through the ProductCategoryID column.

You need to ensure that you can analyze sales by product category.

How should you configure the relationships from Products to ProductCategory?

To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Answer area:

[1] Cardinality: 
    (a) One-to-many 
    (b) One-to-one 
    (c) Many-to-many 

[2] Cross-filter direction: 
    (a) Single
    (b) Both

.

.

.

To ensure you can analyze sales by product category, you should configure the relationships from the Products table to the ProductCategory table as follows:

[1] Cardinality: • (a) One-to-many

[2] Cross-filter direction: • (a) Both

Reasons:

• Cardinality: The relationship between the Products table and the ProductCategory table should be one-to-many because each product belongs to one category, but each category can have multiple products.

• Cross-filter direction: Setting the cross-filter direction to both allows filtering in both directions, which is useful for analyzing sales by product category.

INFO:

In Power BI, cardinality and cross filter direction are important concepts related to data modeling, particularly in the context of relationships between tables. Understanding these concepts is crucial for effective data analysis and reporting.

Cardinality

Cardinality refers to the uniqueness of data values contained in a particular column of a table. In the context of relationships between tables in Power BI, cardinality defines the nature of the relationship in terms of how many values from one table can relate to values in another table. There are three main types of cardinality in Power BI:

1. One-to-One (1:1):

   • Each value in Table A corresponds to one and only one value in Table B, and vice versa. This is less common in data models.

Example: A table of employees and a table of employee IDs, where each employee has a unique ID.

2. One-to-Many (1: or 1:N)*:

   • A single value in Table A can relate to multiple values in Table B, but each value in Table B relates back to only one value in Table A.

Example: A table of customers (Table A) and a table of orders (Table B), where each customer can have multiple orders.

3. Many-to-Many (M:N):

   • Multiple values in Table A can relate to multiple values in Table B. This relationship is more complex and often requires a bridge table (junction table) to manage the connections.

Example: A table of students and a table of courses, where each student can enroll in multiple courses, and each course can have multiple students.

Cross Filter Direction

Cross filter direction determines how filters are applied across related tables in a Power BI data model. It defines how selections in one table affect the visibility of data in another table. There are two types of cross filter directions:

1. Single Direction:

   • In this setting, filtering can only flow in one direction—from the "one" side of a one-to-many relationship to the "many" side. This means that if you filter a value in the one side, it will filter related values in the many side, but not the other way around.

Example: If you filter a specific customer in a customers table, it will filter the corresponding orders in the orders table, but filtering an order will not affect the customer table.

2. Both Directions:

   • In this setting, filtering flows in both directions. This allows for more flexibility, as filters applied in either table will affect the other table.

Example: In a many-to-many relationship, if you filter students, it will filter the courses they are enrolled in, and vice versa. This is useful in scenarios where you want to analyze data dynamically across multiple dimensions.

Implications of Cardinality and Cross Filter Direction

• Performance: Using many-to-many relationships and both-directional filters can impact performance. Careful consideration is needed to maintain efficient data models.

• Data Integrity: Understanding cardinality helps ensure that relationships are correctly established, which is crucial for accurate data analysis.

• User Experience: Proper configuration of cross filter direction affects how users interpret reports and dashboards, especially in interactive visuals.

/INFO

---

QUESTION 23.

You have a dataset named Pencils that contains the following columns:

[1] Unit Price

[2] Quantity Ordered

You need to create a visualization that shows the relationship between Unit Price and Quantity Ordered. The solution must highlight orders that have a similar unit price and ordered quantity.

Which type of visualization and which feature should you use?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Visualization:
(a) A column chart of Quantity
 Ordered and Unit Price by 
year
(b) A line chart of Quantity 
Ordered and Unit Price by item
(c) A scatter plot of Quantity
 Ordered and Unit Price by 
item

[2] Feature:
(a) Automatically find 
clusters
(b) Explain the decrease
(c) Find where the 
distribution is different

.

.

.

To visualize the relationship between Unit Price and Quantity Ordered and highlight orders with similar unit prices and quantities, you should use the following options:

[1] Visualization: • (c) A scatter plot of Quantity Ordered and Unit Price by item

[2] Feature: • (a) Automatically find clusters

Reasons:

• Scatter plot: This type of visualization is ideal for showing the relationship between two numerical variables, such as Unit Price and Quantity Ordered.

• Automatically find clusters: This feature helps to identify and highlight groups of orders that have similar unit prices and quantities, making it easier to spot patterns and trends.

INFO:

Scatter Plot in Power BI

A scatter plot is a type of data visualization that displays values for two different variables as points on a two-dimensional graph. It is particularly useful for identifying relationships, trends, and patterns within the data.

Key Features of Scatter Plots

1. Axes:

   • The horizontal axis (X-axis) represents one variable, while the vertical axis (Y-axis) represents another. Each point on the graph corresponds to a data point's values for these two variables.

2. Data Points:

   • Each point on the scatter plot represents an observation in the dataset. The position of the point indicates the values of the two variables being analyzed.

3. Trend Analysis:

   • Scatter plots help visualize correlations between variables. For example, a positive correlation appears as points that trend upward, while a negative correlation trends downward.

4. Outliers:

   • Scatter plots can easily highlight outliers, or data points that deviate significantly from the general pattern of the data.

Finding Clusters in Power BI

In Power BI, the Find Clusters feature allows users to identify groupings or clusters of data points within a scatter plot. This can be useful for uncovering patterns or segments in the data that may not be immediately apparent.

How to Use the Find Clusters Feature

1. Create a Scatter Plot:

   • Start by creating a scatter plot visual in Power BI and adding your desired fields to the X and Y axes.

2. Enable Clustering:

   • With the scatter plot selected, go to the Visualizations pane and find the Analytics section. Here, you can select the Find Clusters option.

3. Configure Clusters:

   • Once enabled, Power BI will analyze the data points and group them into clusters based on their proximity in the scatter plot. You can often customize parameters such as the number of clusters or sensitivity.

4. Interpret Clusters:

   • Power BI will visually represent the clusters in the scatter plot, typically by coloring the points differently based on their assigned cluster. This allows you to see how data points are grouped and identify patterns or segments.

Benefits of Using Scatter Plots and Clustering

• Insightful Analysis: Scatter plots provide a clear visual representation of relationships between variables, making it easier to identify correlations, trends, and anomalies.

• Segmentation: Clustering helps in segmenting data into meaningful groups, which can be useful for targeted analysis, marketing strategies, or identifying customer segments.

• Data Exploration: The combination of scatter plots and clustering supports exploratory data analysis, allowing users to gain deeper insights into their datasets.

/INFO

---

QUESTION 24.

You have a table that contains the following three columns:

1. City

2. Total Sales

3. Occupation

You need to create a key influencers visualization that answers the question:

"What influences ? to ? when ?"

How should you configure the visualization?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Analyze:
    (a) City
    (b) Occupation
    (c) Total Sales

[2] Explain by:
    (a) City
    (b) Occupation
    (c) Total Sales

.

.

.

To configure the key influencers visualization to answer the question "What influences Total Sales to increase when Occupation is Professional?", you should set it up as follows:

[1] Analyze: • (c) Total Sales

[2] Explain by: • (b) Occupation

Reasons:

• Analyze: You want to analyze the Total Sales to understand what influences its increase.

• Explain by: You need to explain this by the Occupation to see how these factors impact the Total Sales, especially focusing on when the Occupation is Professional.

INFO:

Key Influencer Visualization in Power BI

The Key Influencer visual in Power BI is a powerful analytical tool that leverages machine learning to help users understand the factors influencing specific metrics. Here's a detailed overview based on your outline:

What It Does

• Identifies Top Variables: The Key Influencer visual ranks the variables that have the most significant impact on a selected target variable.

• Displays Key Influencers and Segments: It presents these variables visually, allowing users to easily see which factors are the most influential and how different segments of data behave.

How It Works

• Machine Learning Algorithms: The visual uses advanced machine learning techniques to analyze data patterns and identify drivers of the selected metric.

• Key Functions:

  • Determine Major Drivers: Users can pinpoint which variables most significantly impact the chosen metric.

  • Compare Influencer Importance: The visual allows for easy comparison of how different variables contribute to the outcome.

  • Explore Relationships: Users can investigate how various factors relate to each other and the target variable.

  • Support Data-Driven Decisions: By providing actionable insights, the visual enables informed decision-making based on data analysis.

How to Use It

1. Add the Visual:

   • Drag the Key Influencers icon onto your report canvas from the Visualizations pane.

2. Explain By Section:

   • Drag fields (variables) into the "Explain by" section. These are the potential influencers you want to analyze in relation to your target variable.

3. Populate the Visual:

   • After adding the relevant fields, the visual will automatically populate with insights derived from your data, displaying the key influencers and their impact on the selected metric.

Benefits of Using the Key Influencer Visual

• Understand Drivers: It helps users gain clarity on what drives changes in a metric, whether it’s an increase or decrease.

• Actionable Insights: The visual provides clear, actionable insights that can inform strategies and decisions.

• Intuitive Interface: Its user-friendly design makes it accessible for users with varying levels of data analysis experience.

/INFO

---

QUESTION 25.

You have two tables named Customers and Invoice in a Power BI model.

The Customers table contains the following fields:

1. CustomerID

2. Customer City

3. Customer State

4. Customer Name

5. Customer Address 1

6. Customer Address 2

7. Customer Postal Code

The Invoice table contains the following fields:

1. Order ID

2. Invoice ID

3. Invoice Date

4. Customer ID

5. Total Amount

6. Total Item Count

The Customers table is related to the Invoice table through the Customer ID columns. A customer can have many invoices within one month. The Power BI model must provide the following information:

• The number of customers invoiced in each state last month

• The average invoice amount per customer in each postal code

You need to define the relationship from the Customers table to the Invoice table. The solution must optimize query performance.

What should you configure?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Cardinality:
    (a) Many-to-many
    (b) Many-to-one
    (c) One-to-many
    (d) One-to-one

[2] Cross-filter direction:
    (a) Both
    (b) Single

.

.

.

To configure the relationship between the Customers table and the Invoice table, you should select the following options:

[1] Cardinality: • (c) One-to-many

[2] Cross-filter direction: • (b) Single

This setup ensures that each customer can have multiple invoices, and it optimizes query performance by filtering data in one direction.

---

QUESTION 26.

You have a Power BI report. You need to create a calculated table to return the 100 highest spending customers.

How should you complete the DAX expression?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Top 100 Customers =
Ⓟ (
    100,
    Ⓠ (
        FactTransaction,
        FactTransaction[CustomerID],
        "Sales", SUM ( FactTransaction[Sale] )
    ),
    [Sales], 
    Ⓡ
)

---------- Select Choices ---------- 
Ⓟ: (a)ASC[ (b)DESC( (c)FILTER( (d)SUMMARIZE[ (e)TOPN(
Ⓠ: (a)ASC (b)DESC (c)FILTER (d)SUMMARIZE (e)TOPN 
Ⓡ: (a)ASC (b)DESC (c)FILTER (d)SUMMARIZE (e)TOPN

.

.

.

To create a calculated table that returns the 100 highest spending customers in Power BI, you can use the following DAX expression. Here’s how you should complete it:

Top 100 Customers =
TOPN (
    100,
    SUMMARIZE (
        FactTransaction,
        FactTransaction[CustomerID],
        "Sales", SUM ( FactTransaction[Sale] )
    ),
    [Sales], 
    DESC
)

Here's a breakdown of the components:

[1] TOPN(100, ...): This function returns the top 100 rows of the table based on the specified expression.

[2] SUMMARIZE(FactTransaction, FactTransaction[CustomerID], "Sales", SUM(FactTransaction[Sale])): This part creates a summary table that groups by CustomerID and calculates the total sales for each customer.

[3] [Sales], DESC: This specifies that the top 100 rows should be selected based on the Sales column in descending order.

This DAX expression will create a calculated table with the 100 highest spending customers based on their total sales.

---

QUESTION 27.

Answer area

When a table visual is added 
to blank report page and populated 
using the StoreID field from the sales tab, 
Ⓟ is displayed.

Adding a page filter of Sales[StoreID]=1 
will filter the values displayed 
on the page from Ⓠ.

---------- Select Choices ---------- 
Ⓟ:
  (a) distinct count of the StoreID values
  (b) list of all the StoreID values
  (c) list of the distinct StoreID values
  (d) sum of the StoreID values

Ⓠ:
  (a) all the tables related to the Sales table
  (b) only the Sales table
  (c) only the Store table
  (d) the Sales table and the Customer table

.

.

.

When a table visual is added to a blank report page and populated using the StoreID field from the sales tab, Ⓟ is displayed:

Ⓟ: (c) list of the distinct StoreID values

Adding a page filter of Sales[StoreID]=1 will filter the values displayed on the page from Ⓠ:

Ⓠ: (a) all the tables related to the Sales table

Reasons:

• Ⓟ: List of the distinct StoreID values: When you add a field to a table visual, it typically shows the distinct values from that field.

• Ⓠ: All the tables related to the Sales table: Applying a page filter affects all visuals on the page that are related to the Sales table, including any related tables.

---

QUESTION 28.

You are creating a Microsoft Power BI imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories. You import a fact table named Sales. The related dimension tables are imported into the model.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

[1] The SalesRowID and AuditID
 columns can be removed from 
the model without impeding 
analysis goals (Y/N).

[2] Both the OrderDateKey and 
OrderDate columns are 
necessary to perform the 
basket analysis (Y/N).

[3] The TaxAmt column must 
retain the current number of 
decimal places to perform 
basket analysis (Y/N).

.

.

.

[1] The SalesRowID and AuditID columns can be removed from the model without impeding analysis goals. • Yes. These columns are primarily used for data integrity and auditing purposes. They do not contribute to the basket analysis, which focuses on identifying product combinations within transactions.

[2] Both the OrderDateKey and OrderDate columns are necessary to perform the basket analysis. • No. For basket analysis, you typically need only one date column to understand the timing of transactions. The OrderDateKey is often sufficient as it can be linked to a date dimension table for detailed analysis.

[3] The TaxAmt column must retain the current number of decimal places to perform basket analysis. • No. The precision of the TaxAmt column is not critical for basket analysis, which focuses on product combinations rather than financial precision.

---

QUESTION 29.

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

[1] The CONTOSO dataset is 
consumed directly by the:
(a) CONTOSO BIKES report 
(b) CONTOSO dashboard 
(c) Realtime dashboard

[2] The Realtime dashboard 
depends on:
(a) one dataset 
(b) two datasets 
(c) three datasets 
(d) four datasets

.

[1] The CONTOSO dataset is consumed directly by the: (a) CONTOSO BIKES report

[2] The Realtime dashboard depends on: (c) three datasets

---

QUESTION 30.

You are building a financial report by using Power BI. You have a table named financials that contains a column named Date and a column named Sales. You need to create a measure that calculates the relative change in sales as compared to the previous quarter.

How should you complete the measure?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Sales QoQ% =
IF (
    ISFILTERED ( 'financials'[Date] ),
    ERROR ( "Uh oh." ),
    VAR PREV_QUARTER =
        Ⓟ (
            SUM ( 'financials'[Sales] ),
            Ⓠ ( 'financials'[Date], -1, QUARTER )
        )
    RETURN
        Ⓡ ( SUM ( 'financials'[Sales] ) - PREV_QUARTER, PREV_QUARTER )
)

---------- Select Choices ---------- 
Ⓟ: (a)CALCULATE (b)CALCULATETABLE (c)DATEADD (d)DIVIDE (e)FILTER (f)FIND
Ⓠ: (a)CALCULATE (b)CALCULATETABLE (c)DATEADD (d)DIVIDE (e)FILTER (f)FIND
Ⓡ: (a)CALCULATE (b)CALCULATETABLE (c)DATEADD (d)DIVIDE (e)FILTER (f)FIND

.

.

.

Sales QoQ% =
IF (
    ISFILTERED ( 'financials'[Date] ),
    ERROR ( "Uh oh." ),
    VAR PREV_QUARTER =
        CALCULATE (
            SUM ( 'financials'[Sales] ),
            DATEADD ( 'financials'[Date], -1, QUARTER )
        )
    RETURN
        DIVIDE ( SUM ( 'financials'[Sales] ) - PREV_QUARTER, PREV_QUARTER )
)

So, the correct choices are:

• Ⓟ: (a) CALCULATE

• Ⓠ: (c) DATEADD

• Ⓡ: (d) DIVIDE

This measure will calculate the percentage change in sales from the previous quarter, helping you analyze the trend in sales over time.

Here, we declare a variable PREV_QUARTER which calculates the sales for the previous quarter.

Let's break down the components:

• CALCULATE: This function is used to modify the context in which the data is evaluated. In this case, it is used to calculate the sales for the previous quarter.

• DATEADD: This function shifts the dates by a specified interval. Here, it shifts the dates back by one quarter.

So, PREV_QUARTER will hold the sales data for the previous quarter.

Return Statement:

RETURN DIVIDE - PREV_QUARTER, PREV_QUARTER ) )

This part of the measure calculates the relative change in sales.

Let's break it down:

• DIVIDE: This function is used to perform division and handle division by zero errors. It takes two arguments: the numerator and the denominator.

---

QUESTION 31.

You have a power BI tenant that hosts the datasets shown in the following table.

You have the following requirements:

1. The export of reports that contain Personally Identifiable Information (Pll) must be prevented.

2. Data used for financial decisions must be reviewed and approved before use.

For each of the following statements, select Yes if the statement is true. Otherwise select No.

NOTE: Each correct selection is worth one point.

[1] The Sales dataset requires
 a sensitivity label (Y/N).

[2] The Operations dataset 
requires a sensitivity label 
and must be certified (Y/N).

[3] The Finance dataset 
requires a sensitivity label 
and must be certified (Y/N).

.

.

.

[1] The Sales dataset requires a sensitivity label (Y/N). • Yes. The Sales dataset includes employee salary data, which is considered Personally Identifiable Information (PII). To prevent the export of reports containing PII, a sensitivity label is necessary.

[2] The Operations dataset requires a sensitivity label and must be certified (Y/N). • No. The Operations dataset contains environmental sensor data, which typically does not include PII or data used for financial decisions. Therefore, it does not require a sensitivity label or certification.

[3] The Finance dataset requires a sensitivity label and must be certified (Y/N). • Yes. The Finance dataset includes financial transaction data used for budget planning and monthly board reports. This data must be reviewed and approved before use, necessitating both a sensitivity label and certification.

INFO:

Sensitivity Level in Power BI

Sensitivity levels in Power BI are part of the data governance features that help organizations classify and protect sensitive information. By labeling data based on its sensitivity, organizations can enforce appropriate access controls and compliance measures.

Key Features

1. Classification:

   • Sensitivity levels allow users to categorize data into different labels, such as "Public," "Internal," "Confidential," and "Highly Confidential." This classification helps identify the necessary protection level.

2. Data Protection:

   • Applying sensitivity labels can enforce security measures, such as restricting access to sensitive data or applying encryption, which is crucial for compliance with regulations like GDPR or HIPAA.

3. Integration with Microsoft Information Protection:

   • Sensitivity levels can integrate with Microsoft Information Protection (MIP), which ensures consistent labeling across Microsoft applications, enhancing overall data governance.

4. User Awareness:

   • When users create or view reports, they can see the sensitivity labels applied, making it clear how to handle the data appropriately.

5. Audit and Compliance:

   • Sensitivity levels provide an audit trail for interactions with sensitive data, helping organizations track usage and ensure compliance with policies and regulations.

Personally Identifiable Information (PII)

Personally Identifiable Information (PII) refers to any data that can be used to identify an individual. In the context of Power BI, handling PII is crucial for maintaining privacy and compliance with data protection regulations.

Key Points about PII

1. Types of PII:

   • Examples of PII include names, addresses, phone numbers, email addresses, Social Security numbers, and any other information that can directly or indirectly identify an individual.

2. Regulatory Compliance:

   • Organizations must comply with various laws and regulations regarding the handling of PII, such as GDPR, HIPAA, and CCPA. These regulations often require specific measures to protect PII.

3. Data Classification:

   • PII can be classified under sensitivity levels in Power BI, often labeled as "Confidential" or "Highly Confidential." This classification helps ensure that appropriate security measures are applied.

4. Data Governance:

   • Managing PII effectively is essential for data governance. Organizations need to implement policies and practices to protect PII, including limiting access, data anonymization, and secure data storage.

5. Impact on Reporting:

   • When creating reports in Power BI, special care must be taken to avoid exposing PII, especially in publicly accessible reports or dashboards.

/INFO

---

QUESTION 32.

You have a column named UnitsInStock. UnitsInStock has 75 non-null values, of which 51 are unique.

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

[1] When a table visual is created 
in a report and UnitsInStock is added 
to the values, there will be __ in 
the table. 
(a) 0 rows (b) 1 row (c) 51 rows (d) 75 rows

[2] Changing the "Summarize by" setting 
of the UnitsInStock column, and then 
adding the column to a table visual, 
will __ the number of rows in the 
table visual. 
(a) maintain (b) reduce (c) increase

.

.

.

[1] When a table visual is created in a report and UnitsInStock is added to the values, there will be __ in the table.

• Since UnitsInStock has 75 non-null values, adding it to the values section of a table visual will display all 75 rows.

• Answer: (d) 75 rows

[2] Changing the Summarize by setting of the UnitsInStock column, and then adding the column to a table visual, will __ the number of rows in the table visual.

• Changing the "Summarize by" setting (e.g., to Sum, Average, Count, etc.) will aggregate the data, which typically reduces the number of rows displayed in the table visual.

• Answer: (b) reduce

---

QUESTION 33.

You are creating a line chart in a Power BI report as shown in the following exhibit.

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

Refer to the Power BI Line Chart 
representing Employee Count By Month.

[1] The average dash line 
(representing the Year Average Employee Count) 
in Line Chart was created using __ . 
(a) a trend line 
(b) a secondary axis 
(c) an average reference line 
(d) two measures in the Values bucket

[2] To enable users to drill down 
to weeks or days, add the Weeks and Days 
field to the __ bucket. 
(a) Axis 
(b) Values 
(c) Legend 
(d) Secondary values

.

.

.

[1] The average dash line (representing the Year Average Employee Count) in Line Chart was created using: • (c) an average reference line

An average reference line is commonly used to display the average value across a specified period, such as a year, in a line chart.

[2] To enable users to drill down to weeks or days, add the Weeks and Days field to the: • (a) Axis

Adding the Weeks and Days fields to the Axis bucket allows users to drill down through different time granularities, such as from months to weeks or days.

INFO:

Line Chart in Power BI

A line chart is a type of data visualization used in Power BI to display trends over time or continuous data. It connects individual data points with lines, making it easy to see patterns, trends, and fluctuations in the data.

Key Features of Line Charts

1. Axes:

   • The horizontal axis (X-axis) typically represents time (e.g., days, months, years), while the vertical axis (Y-axis) represents the values of the variable being measured (e.g., sales, temperature).

2. Data Points:

   • Each point on the line chart corresponds to a specific value at a given time or category. The line connects these points, illustrating how the values change over the specified period.

3. Multiple Lines:

   • Line charts can display multiple lines on the same graph, allowing comparisons between different categories or groups (e.g., sales of different products over time).

4. Trend Analysis:

   • Line charts are particularly effective for identifying trends, seasonal patterns, and anomalies in data over time.

5. Interactive Features:

   • In Power BI, line charts support interactive features, such as tooltips that provide additional information when hovering over data points.

Reference Line in Power BI

A reference line is an additional line that can be added to a line chart (or other visualizations) to provide context or highlight specific values. Reference lines help users interpret the data more effectively.

Key Features of Reference Lines

1. Static or Dynamic:

   • Reference lines can be static (fixed at a specific value) or dynamic (calculated based on the data, such as average or median).

2. Types of Reference Lines:

   • Constant Line: A straight line that represents a fixed value (e.g., a sales target).

   • Average Line: A line representing the average value of the data set.

   • Percentage Line: A line showing a specific percentage of the maximum or another reference value.

3. Customization:

   • Users can customize reference lines by changing their color, style (solid, dashed), and thickness, making them visually distinct from the data lines.

4. Annotations:

   • Reference lines can include labels or annotations to provide additional context or information about what the line represents.

5. Enhanced Interpretation:

   • By adding reference lines, users can quickly assess performance against targets, identify trends relative to average values, and gain insights into the significance of data points.

/INFO

---

QUESTION 34.

Your company plans to use Power BI for 20 users in the sales department. The users will perform the following tasks:

1. Access a published Power BI app

2. Modify reports in an app workspace

3. Share dashboards created in My Workspace

You need to identify which Power BI licenses are required for the tasks. The solution must use the Power BI (free) licenses, whenever possible.

Which license should you identify for each task?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Access a published Power BI app: 
(a) Power BI (free) (b) Power BI PRO

[2] Modify report in an app workspace: 
(a) Power BI (free) (b) Power BI PRO

[3] Share dashboards created in My Workspace:
(a) Power BI (free) (b) Power BI PRO

.

.

.

[1] Access a published Power BI app: (a) Power BI Free

[2] Modify reports in an app workspace: (b) Power BI Pro

[3] Share dashboards created in My Workspace: (b) Power BI Pro

---

QUESTION 35.

You have a table that contains a column named Phone. The following is a sample of the data in the Phone column.

436-555-0160
385-555-0140
452-555-0179
290-555-0196
1 (11) 500 555-0122
128-555-0148
819-555-0186
996-555-0192
138-555-0156
556-555-0192

You need to add a new column that contains the data in the format of nnn-nnn-nnnn.

How should you complete the Query Editor formula?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

= Table.AddColumn(#"Previous Step", 
                "Custom", 
                each Text.Ⓟ(
                                    Text.Ⓠ([Phone],12)," ","-")
                                  )

Ⓟ:
(a)Insert
(b)Remove
(c)Replace
(d)ReplaceRange

Ⓠ:
(a)At
(b)End
(c)Middle
(d)Range

.

.

.

= Table.AddColumn(#"Previous Step", 
                "Custom", 
                each Text.Replace(
                                    Text.End([Phone],12)," ","-")
                                  )

The Query formula uses Text.Replace to replace spaces with hyphens and Text.End to ensure the last 12 characters of the phone number are formatted correctly.

---

QUESTION 36.

Based on the above exhibit, select the appropriate options in the answer area.

[1] A tenant administrator 
created a data classification 
that has a shortname of __.
(a) Customers
(b) Insurance
(c) Insurance Data
(d) Sale

[2] The dashboard uses a dataset named __.
(a) Customers
(b) Insurance
(c) Insurance Data
(d) Sale

.

.

.

[1] (d) Sale

[2] (d) Insurance Data

---

QUESTION 37.

Refer Power Bi Gauge Visual.

Select the answer choice that completes each statement based on the information presented in the graphic.

Note: Each correct selection is worth one point.

[1] How to set the goal in the Gauge visual?
(a) a calculated measure
(b) a DAX formula
(c) the Format settings

[2] To configure the visual to
 always display TotalPrice for
 a location e.g. Territory Of 
Canada, add the related column
 to __.
(a) the Tooltips field
(b) the Values field
(c) the Visual level filters field

.

.

.

[1] How to set the goal in a Power BI Gauge Visual?
• (c) the Format settings: You can set the goal in the Gauge visual by going to the Format pane and setting the Target value under the Gauge axis settings.

[2] To configure the visual to always display TotalPrice for a location (e.g., Territory Of Canada), add the related column to __.
• (c) the Visual level filters field: To ensure the visual always displays TotalPrice for a specific location, you should add the related column (e.g., Territory) to the Visual level filters field and set the filter to the desired location (e.g., Territory Of Canada).

---

QUESTION 38.

You are profiling data by using Power Query Editor.

The AddressLine2 column in a table named Address has the following information in Column Quality display.

Valid=2%

Empty=98%

Distinct=12

Unique=11

Select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.

[1] There are __ different 
values in the column including
 nulls.
(a) 2
(b) 11
(c) 12
(d) 23

[2] There are __ non-null 
values that occur only once in
 the column.
(a) 2
(b) 11
(c) 12
(d) 23

.

.

.

Based on the information provided:

[1] There are __ different values in the column including nulls.

• Since the column has 12 distinct values and 98% of the values are empty, we need to include the nulls in the count. Therefore, the total number of different values, including nulls, is 12 (distinct values) + 1 (null) = 13. However, since 13 is not an option, the closest logical answer would be:

• (c) 12

[2] There are __ non-null values that occur only once in the column.

• The column has 11 unique values, which means these values occur only once. Therefore, the number of non-null values that occur only once is:

• (b) 11

So, the correct answers are:

[1]=(c) 12

[2]=(b) 11

---

QUESTION 39.

You are creating reports in Power BI Desktop. The model has the following tables:

There is a relationship between the tables. You plan to publish a report to the Power BI service that displays Order_amount by Order_date by Full_name. You need to ensure that only the columns required for the report appear in Report View. The solution must minimize the size of the dataset that is published.

How should you configure the columns in Power BI Desktop?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Customer_ID:
(a) From Query Editor, select 
the column and click Remove 
Columns.
(b) From Query Editor, select 
the column and click Remove 
Duplicates.
(c) From Query Editor, select 
the column and click Remove 
Other Columns.
(d) From the model, select the
 column and click Hide.

[2] Customer_Photo:
(a) From Query Editor, select 
the column and click Remove.
(b) From Query Editor, select 
the column and click Remove 
Duplicates.
(c) From Query Editor, select 
the column and click Remove 
Other Columns.
(d) From the model, select the
 column and click Hide.

.

.

.

To ensure that only the necessary columns appear in Report View and to minimize the size of the dataset, you should configure the columns as follows:

[1] Customer_ID:

• (d) From the model, select the column and click Hide.

• Hiding the column in the model will ensure it is not visible in the Report View but still available for relationships and calculations.

[2] Customer_Photo:

• (a) From Query Editor, select the column and click Remove.

• Removing the column in the Query Editor will exclude it from the dataset entirely, reducing the size of the dataset.

These configurations will help you achieve a streamlined and efficient report.

---

QUESTION 40.

You have a Power BI model that has the following tables:

Product (Product_id, Product_Name)

Sales (Order_id, Order_Date, Product_id, Salesperson_id, Sales_Amount)

Salesperson (Salesperson_id, Salesperson_name, address)

You plan to create the following measure.

Measure1 = DISTINCTCOUNT(Sales[ProductID])

You need to create the following relationships:

1. Sales to Product

2. Sales to Salesperson

The solution must ensure that you can use Measure1 to display the count of products sold by each salesperson.

How should you configure the relationships?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Cardinality:
(a) Many to One
(b) One to Many
(c) One to One

[2] Cross filter direction:
(a) Both
(b) Single

.

.

.

To configure the relationships in your Power BI model to ensure that you can use Measure1 to display the count of products sold by each salesperson, you should set up the relationships as follows:

[1] Sales to Product
• Cardinality: (a) Many to One

• Cross filter direction: (b) Single

[2] Sales to Salesperson
• Cardinality: (a) Many to One

• Cross filter direction: (b) Single

Reasons:
• Cardinality: The Sales table will have many records for each Product and each Salesperson, so the relationships should be Many to One.

• Cross filter direction: Setting the cross filter direction to Single ensures that the filter context flows from the Product and Salesperson tables to the Sales table, which is necessary for the DISTINCTCOUNT measure to work correctly.

---

QUESTION 41.

You have the Power Bi dashboard containing the following tiles:

• Total Sales and Total Quantity tiles

• Total Sales by Parent Category tile

• Subtotal map tile

You need to ensure that when users view the dashboard on a mobile device, the tiles are visible and fully displaying the contained data. At the moment the Total Sales by Parent Category tile does not fully show the chart.

What should you do?

[1] Update the layout in the __.
(a) Dashboard mobile layout.
(b) Dashboard web layout.
(c) Report mobile layout.

[2] Resize and move __.
(a) The SubTotal map tile.
(b) The Total Sales and Total Quantity tiles.
(c) The Total Sales by Parent Category tile.

.

.

.

To ensure that the tiles are visible and fully displaying the contained data on a mobile device, you should:

[1] Update the layout in the __.
(a) Dashboard mobile layout.

[2] Resize and move __.
(c) The Total Sales by Parent Category tile.

Updating the layout in the Dashboard mobile layout and resizing/moving the Total Sales by Parent Category tile will help ensure that all the necessary information is displayed properly on mobile devices.

Try resizing the chart in the mobile layout to ensure all bars are visible. You might need to make the chart slightly smaller or adjust its aspect ratio.

---

QUESTION 42.

You have a Power Bl visual (Line Chart) that uses indicators to show values that are out of range.

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

[1] The visual type is __ chart.
(a) a line
(b) a line and a clustered column
(c) an area

[2] The visual indicators that
 show values out of range are 
created by using __.
(a) a custom visual
(b) a trendline
(c) anomaly detection
(d) line chart markers

.

.

.

[1] The visual type is __ chart.

(a) a line

[2] The visual indicators that show values out of range are created by using __.

(c) anomaly detection

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-anomaly-detection

---

QUESTION 43.

You are profiling data by using Power Query Editor. You have a table named Reports that contains a column named State. The distribution and quality data metrics for the data m State is shown in the following exhibit.

Column name=State

Valid=96%

Error=0%

Empty=4%

Distinct=69

Unique=4

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

.

.

.

[1] There are different values in State including nulls.
• There are 69 different values in the State column, excluding nulls.

[2] There are non-null values that occur only once in State.

• There are 4 non-null values that occur only once in the State column.

---

QUESTION 44.

You have a folder of monthly transaction extracts. You plan to create a report to analyze the transaction data. You receive the following email message:

"Hi. I've put 24 files of monthly transaction data onto the shared drive. File Transactions201701.csv through Transactions201712.csv have the latest set of columns, but files Transactions201601.csv to Transactions201612.csv have an older layout without the extra fields needed for analysis. Each file contains 10 to 50 transactions."

You get data from the folder and select Combine & Load. The Combine Files dialog box is shown in the exhibit (Sample file=first file with columns(ID, Date, CustomerID, Amount) ).

.

.

.

[1] The resulting query will contain all the columns from 2016 transactions. • Yes. Since the columns are based on the entire dataset, the resulting query will include all columns from both 2016 and 2017 transactions.

[2] The resulting query will contain all the columns from 2017 transactions. • Yes. The additional columns from the 2017 files will be detected and included in the resulting query.

[3] Setting Data Type Detection to Based on first 200 rows will improve import times. • Yes. Limiting data type detection to the first 200 rows can still reduce processing time, thereby improving import times.

---

QUESTION 45.

You have two CSV files named Products and Categories.

The Products file contains the following columns:

ProductID, ProduetName, SupplierID, CategoryID

The Categories file contains the following columns:

CategoryID, CategoryName, CategoryDescription

From Power Bl Desktop, you import the files into Power Query Editor. You need to create a Power Bl dataset that will contain a single table named Product. The Product will table includes the following columns:

1. ProductID

2. ProductName

3. SupplierID

4. CategoryID

5. CategoryName

6. CategoryDescription

How should you combine the queries, and what should you do on the Categories query?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Combine the queries by performing a:
(a) Append
(b) Merge
(c) Transpose

[2] On the Categories query:
(a) Delete the query
(b) Disable the query load
(c) Exclude the query from report refresh

.

.

.

To create a single table named Product that includes the specified columns, you should follow these steps:

[1] Combine the queries by performing a: (b) Merge

Merging the queries will allow you to combine the columns from both the Products and Categories tables based on the common CategoryID.

[2] On the Categories query: (b) Disable the query load

Disabling the query load will prevent the Categories table from being loaded into the Power BI model, as its data will already be included in the merged Product table.

---

QUESTION 46.

You have a Power Bl report that contains a measure named Total Sales. You need to create a new measure that will return the sum of Total Sales for a year up to a selected date.

How should you complete the DAX expression?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Measure = Ⓟ ([Total Sales], Ⓠ)

Ⓟ:
(a) TOTALYTD
(b) CALCULATE
(c) SUM
(d) EVALUATE

Ⓠ:
(a) 'Date'[Date]
(b) TODAY()
(c) EOMONTH('Date'[Date])
(d) LASTDAYE('Date'[Date')

.

.

.

Measure = TOTALYTD([Total Sales], 'Date'[Date])

Ⓟ: (a) TOTALYTD

Ⓠ: (a) 'Date'[Date]

The TOTALYTD function in DAX (Data Analysis Expressions) is used to calculate the year-to-date total of a measure. It sums up the values from the beginning of the year to the specified date.

In the above answer, the expression will sum up the Total Sales from the start of the year to the selected date.

---

QUESTION 47.

You have a dataset that has the permissions shown in the following exhibit

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point

[1] Users in the finance group can __ the dataset.
(a) assign sensitivity labels to
(b) use Analyze in Excel with
(c) delete

[2] Users in the corp group can __ the dataset.
(a) grant the Build permission for
(b) grant the Read permission for
(c) remove a table from

.

.

.

Based on the permissions provided:

[1] Users in the finance group can (b) use Analyze in Excel with the dataset.

• The finance group has Read and Build permissions, which allow them to use features like Analyze in Excel.

[2] Users in the corp group can (a) grant the Build permission for the dataset.
• The corp group has Read, Reshare, and Build permissions, enabling them to grant the Build permission to others.

INFO:
Owner: • Description: The highest level of permission. Owners have full control over the dataset. They can read, modify, delete, share, and manage permissions for the dataset.

Reshare: • Description: Users with this permission can share the dataset with others and manage permissions, but they cannot modify or delete the dataset.

Build: • Description: Users with Build permission can create new content (like reports or dashboards) using the dataset. They can also save a copy of the dataset to their workspace.

Read: • Description: The most basic level of permission. Users can view and interact with the dataset but cannot modify, share, or create new content using it.

/INFO

---

QUESTION 48.

You have two Azure SQL databases that contain the same tables and columns. For each database, you create a query that retrieves data from a table named Customers. You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support scheduled refresh in powerbi.com.

What should you do?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Option to use to combine 
Customer tables:
(a) Append Queries
(b) Append Queries as New
(c) Merge Queries
(d) Merge Queries as New

[2] Action to perform on the 
original two SQL database 
queries:
(a) Delete the queries
(b) Disable including the query in report refresh
(c) Disable loading the query to the data model
(d) Duplicate the queries

.

.

.

To combine the Customer tables from two Azure SQL databases into a single table while minimizing the size of the data model and supporting scheduled refresh in Power BI, you should:

[1] Option to use to combine Customer tables: (b) Append Queries as New

Appending the queries as new will create a new table that combines the data from both Customer tables without altering the original queries.

[2] Action to perform on the original two SQL database queries: (c) Disable loading the query to the data model

Disabling the loading of the original queries to the data model will help minimize the size of the data model, as only the combined table will be loaded.

---

QUESTION 49.

You have two Power Bl workspaces named WorkspaceA and WorkspaceB. WorkspaceA contains two datasets named Sales and HR.

You need to provide a user named User1 with access to the workspaces.

The solution must meet the following requirements:

1. Create reports that use the HR dataset.

2. Publish the reports to WorkspaceB.

3. Prevent the ability to modify the HR dataset.

4. Prevent the ability to add users to WorkspaceB.

[1] To access the HR dataset:
(a) Assign User1 the 
Contributor role for 
WorkspaceA.
(b) Grant User1 the Build 
permission for the HR dataset.
(c) Grant User1 read 
permissions for the HR 
dataset.
(d) Grant User1 share 
permissions for the HR 
dataset.



[2] To publish reports to 
WorkspaceB:
(a) Assign User1 the Admin 
role for Workspaces.
(b) Assign User1 the 
Contributor role for 
WorkspaceA.
(c) Assign User1 the 
Contributor role for 
WorkspaceB.
(d) Assign User1 the Member 
role for WorkspaceB.

.

.

.

To meet the requirements, you should:

[1] To access the HR dataset: (b) Grant User1 the Build permission for the HR dataset. This allows User1 to create reports using the HR dataset without modifying it.

[2] To publish reports to WorkspaceB: (c) Assign User1 the Contributor role for WorkspaceB. This allows User1 to publish reports to WorkspaceB without the ability to add users.

---

QUESTION 50.

You are using Power Bi Desktop to connect to an Azure SQL database based on the following settings:

Server: mydb.database.windows.net
Database (optional): db1
Data Connectivity mode:
    [x] Import
    [ ] DirectQuery 
Advanced options:
    Command timeout in minutes (optional): __
    SQL statement (optional, requires database): __
    [x] Include relationship columns
    [ ] Navigate using full hierarchy
    [ ] Enable SQL Server Failover support

Select the answer choice that completes each statement based on the information presented in the graphic

NOTE: Each correct solution is worth one point.

[1] The default timeout for the 
connection from Power BI Desktop 
to the database will be:
(a) unlimited
(b) one minute
(c) 10 minutes

[2] The Navigator will display:
(a) all the tables
(b) only tables that contain data
(c) only tables that contain hierarchies

.

.

.

Based on the provided settings and information:

[1] The default timeout for the connection from Power BI Desktop to the database will be: • (c) 10 minutes

[2] The Navigator will display: • (a) all the tables

---

QUESTION 51.

You plan to create a Power Bl dataset to analyze attendance at a school. Data will come from two separate views named View1 and View2 in an Azure SQL database.

View1

View2

The views can be related based on the Class ID column. Class ID is the unique identifier for the specified class, period, teacher, and school year. For example, the same class can be taught by the same teacher during two different periods, but the class will have a different class ID.

You need to design a star schema data model by using the data in both views.

The solution must facilitate the following analysis:

1. The count of classes that occur by period

2. The count of students in attendance by period by day

3. The average number of students attending a class each month

In which table should you include the Teacher First Name and Period Number fields?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Teacher First Name:
(a) Attendance fact
(b) Class dimension
(c) Teacher dimension
(d) Teacher fact

[2] Period Number:
(a) Attendance fact
(b) Class dimension
(c) Teacher dimension
(d) Teacher fact

.

.

.

[1] Teacher First Name: (c) Teacher dimension.

[2] Period Number: (b) Class dimension.

Reasons:

Teacher First Name -> Teacher Dimension • Logical Grouping: The Teacher First Name is an attribute that describes the teacher. It logically belongs in the Teacher dimension, which contains all the attributes related to teachers.

• Normalization: Placing the Teacher First Name in the Teacher dimension helps in normalizing the data. This avoids redundancy and ensures that each teacher's information is stored in one place.

• Ease of Analysis: When analyzing data related to teachers, having all teacher-related attributes in one dimension simplifies queries and reporting.

Period Number -> Class Dimension • Class Schedule: The Period Number is an attribute that describes when a class occurs. It is directly related to the class schedule and should be part of the Class dimension.

• Contextual Relevance: Including the Period Number in the Class dimension helps in understanding the context of when each class is held, which is crucial for scheduling and attendance analysis.

• Dimensional Modeling: In a star schema, dimensions should contain descriptive attributes. Since the Period Number describes the class timing, it fits well in the Class dimension.

This setup ensures that the data model is well-organized, supports efficient querying, and facilitates the required analysis.

INFO:

Fact Table and Dimension Table in Power BI

In Power BI and data warehousing, fact tables and dimension tables are fundamental components of a star schema or snowflake schema used for organizing data. Understanding these concepts is crucial for effective data modeling and analysis.

Fact Table

A fact table contains quantitative data that can be analyzed. It typically includes measurable metrics or facts, which are often numerical values.

Key Characteristics:

1. Contains Measures:

   • Fact tables store metrics that are important for analysis, such as sales revenue, order quantity, or profit margin.

2. Foreign Keys:

   • Fact tables usually include foreign keys that link to dimension tables. These keys help to connect the facts to the context provided by the dimensions.

3. Granularity:

   • The granularity of a fact table refers to the level of detail in the data. For example, a sales fact table might store data at the transaction level, meaning each row represents a single sale.

4. Large Volume of Data:

   • Fact tables often contain a large amount of data, as they record every transaction or event that occurs.

Example:

A sales fact table might include columns like:

• SalesID (Primary Key)

• ProductID (Foreign Key)

• CustomerID (Foreign Key)

• DateID (Foreign Key)

• SalesAmount

• QuantitySold

Dimension Table

A dimension table contains descriptive attributes related to the facts in the fact table. Dimension tables provide context to the data and are used to filter, group, and label the facts.

Key Characteristics:

1. Descriptive Attributes:

   • Dimension tables store attributes that describe the entities involved in the facts, such as product details, customer demographics, or time periods.

2. Primary Keys:

   • Each dimension table has a primary key that uniquely identifies each record. This key is referenced in the fact table as a foreign key.

3. Smaller Size:

   • Dimension tables are generally smaller than fact tables, as they contain fewer records but more descriptive information.

4. Hierarchies:

   • Dimension tables can include hierarchies (e.g., year → quarter → month) that facilitate drill-down analysis.

Example:

A product dimension table might include columns like:

• ProductID (Primary Key)

• ProductName

• Category

• Brand

• Price

Relationships Between Fact and Dimension Tables

• Star Schema: In a star schema, a central fact table is surrounded by dimension tables. This design simplifies queries and enhances performance.

• Snowflake Schema: In a snowflake schema, dimension tables can be normalized into multiple related tables, which can reduce redundancy but may complicate queries.

/INFO

---

QUESTION 52.

You have a Power Bl report. You have the following tables.

You have the following DAX measure.

Accounts := CALCULATE ( DISTINCTCOUNT (Balances[AccountID]), LASTDATE ('Date'[Date])

For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

[1] A table visual that 
displays the date hierarchy at
 the year level and the 
[Accounts] measure will show 
the total number of accounts 
that were live throughout the 
year (Y/N).

[2] A table visual that 
displays the date hierarchy at
 the month level and the 
[Accounts] measure that will 
show the total number of 
accounts that were live 
throughout the month (Y/N).

[3] A table visual that 
displays the date hierarchy at
 the day level and the 
[Accounts] measure will show 
the total number of accounts 
that were live that day (Y/N).

.

.

.

[1] A table visual that displays the date hierarchy at the year level and the [Accounts] measure will show the total number of accounts that were live throughout the year.

• No: The measure Accounts calculates the distinct count of AccountID for the last date in the context. At the year level, it will show the number of accounts that were live on the last day of the year, not throughout the year.

[2] A table visual that displays the date hierarchy at the month level and the [Accounts] measure will show the total number of accounts that were live throughout the month.

• No: Similarly, at the month level, the measure will show the number of accounts that were live on the last day of the month, not throughout the month.

[3] A table visual that displays the date hierarchy at the day level and the [Accounts] measure will show the total number of accounts that were live that day.

• Yes: At the day level, the measure will correctly show the number of accounts that were live on that specific day.

---

QUESTION 53.

You have a Power BI data model that contains a table named Stores. The table has the following columns:

1. Store Name

2. Open Date

3. Status

4. State

5. City

You need to create a calculated column named Active Store Name that meets the following requirements.

Active Store Name = 
    Ⓟ ([Status]="A",[Store Name],"Inactive - " Ⓠ [Store Name])

Ⓟ:
(a) IF
(b) SWITCH
(c) TRUE

Ⓠ:
(a) &
(b) &&
(c) +

.

.

.

Active Store Name = 
IF([Status] = "A", [Store Name], "Inactive - " & [Store Name])

Ⓟ: (a) IF

Ⓠ: (a) &

This formula checks if the Status is "A". If it is, it returns the Store Name; otherwise, it returns "Inactive - " concatenated with the Store Name.

---

QUESTION 54.

You need to create a Power Bl report. The first page of the report must contain the following two views:

1. Sales By Postal Code

2. Sales by Month

Both views must display a slicer to select a value for a field named Chain. The Sales By Postal Code view must display a map visual.

Users must be able to switch between the views by using buttons on the report page. The selected Chain field must be maintained when switching between views.

[1] What is the minimum number of bookmarks required, and

[2] which property should you apply to each bookmark?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

[1] Minimum number of bookmarks:
(a) 1
(b) 2
(c) 3
(4) 4

[2] Property:
(a) Data
(b) Display
(c) Current Page

.

.

.

To create the Power BI report with the specified requirements, you need to:

[1] Minimum number of bookmarks: (b) 2

You need one bookmark for each view (Sales By Postal Code and Sales by Month).

[2] Property: (c) Current Page

The "Current Page" property ensures that the bookmark captures the current state of the page, allowing users to switch between views while maintaining the selected Chain field.

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-bookmarks?tabs=powerbi-desktop

INFO:

Bookmark in Power BI

Bookmarks in Power BI are a feature that allows users to save the current state of a report page. This includes the filters, slicers, and visual selections present at the time the bookmark is created. Bookmarks enhance user interactivity, navigation, and storytelling within reports.

Key Functions of Bookmarks

1. Capture State:

   • Bookmarks save the exact configuration of a report page, allowing users to return to that specific view later.

2. Navigation:

   • Users can navigate between different bookmarks easily, creating a seamless experience when exploring data.

3. Storytelling:

   • Bookmarks can be used to guide users through a narrative or analysis by presenting data in a structured manner.

4. Dynamic Reporting:

   • Users can switch between different perspectives of the data quickly without altering the underlying report structure.

Bookmark Properties

Each bookmark in Power BI has associated properties that define its behavior and functionality. Here are the key properties of bookmarks:

1. Name:

   • Each bookmark has a unique name that helps identify it. Users can rename bookmarks for better organization.

2. Data:

   • This property captures the current data state, including filters and slicer selections. If the data property is enabled, the bookmark will also save the current filter context.

3. Display:

   • This property determines whether the bookmark will be visible in the bookmarks pane. Users can hide certain bookmarks to declutter the interface.

4. Current Page:

   • This property indicates which report page the bookmark is associated with. When a user selects a bookmark, it will navigate them to the specified page.

5. View State:

   • This property includes the state of visuals, such as whether they are expanded or collapsed, and the visibility of certain elements on the page.

6. Selection State:

   • This property controls the visibility of certain visuals or elements, allowing users to show or hide them based on the selected bookmark.

How to Use Bookmarks and Their Properties

1. Creating a Bookmark:

   • To create a bookmark, go to the View tab, open the Bookmarks pane, and click Add.

2. Editing Bookmark Properties:

   • Right-click on a bookmark in the bookmarks pane to access options for renaming or updating properties. You can choose to include or exclude certain properties based on how you want the bookmark to behave.

3. Linking to Buttons:

   • You can link bookmarks to buttons or images, allowing users to navigate to different bookmarks easily. This enhances interactivity.

4. Updating Bookmarks:

   • If changes are made to a report page after creating a bookmark, you can update the bookmark to reflect the new state by selecting the bookmark and clicking Update.

/INFO

---

QUESTION 55.

You plan to create the Power Bl model shown in the exhibit.

Sales is a Fact Table, Data and Customers are Dimension Table. SalesAggregate is the summary of the Sales table.

The data has the following refresh requirements:

(1) Customer must be refreshed daily.

(2) Date must be refreshed once every three years.

(3) Sales must be refreshed in near real time.

(4) SalesAggregate must be refreshed once per week.

You need to select the storage modes for the tables. The solution must meet the following requirements:

(1) Minimize the load times of visuals.

(2) Ensure that the data is loaded to the model based on the refresh requirements.

Which storage mode should you select for each table?

Select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Customer:
(a) DirectQuery
(b) Dual
(c) Import

Date:
(a) DirectQuery
(b) Dual
(c) Import

Sales:
(a) DirectQuery
(b) Dual
(c) Import

SalesAggregate:
(a) DirectQuery
(b) Dual
(c) Import

.

.

.

Customer: (b) Dual.

Date: (b) Dual.

Reasons: Both Date and Customer has relationship with both Sales and SalesAggregate tables are set to "Dual" because to support performance for DirectQuery(Sales) and Import(SalesAggregate).

Sales: (a) DirectQuery.

Reason: Near real-time refresh requirements necessitate using DirectQuery, allowing live data access.

SalesAggregate: (b) Dual. Reason: Weekly refresh with the ability to use Import when necessary balances the need for performance and refresh frequency.

Summary of Answers:

Customer: (c) Import

Date: (c) Import

Sales: (a) DirectQuery

SalesAggregate: (b) Dual

INFO:

In Power BI, data connectivity options are crucial for determining how data is accessed, stored, and managed. Here’s an overview of the different data access modes: Import, DirectQuery, Dual, and Composite.

1. Import Mode

Import mode allows users to import data from various sources into Power BI’s in-memory data model.

Key Features:

• Data Storage: The imported data is stored in Power BI’s in-memory engine (VertiPaq), allowing for fast query performance.

• Data Refresh: Users can schedule data refreshes to keep the imported data up to date. The refresh can be done daily or multiple times a day.

• Performance: Since the data is stored in-memory, queries are typically very fast, making this mode suitable for large datasets where performance is critical.

• Limitations: The dataset is static until the next refresh, and it may not reflect real-time data changes.

Use Cases:

• Ideal for scenarios where data does not change frequently and speed is essential, such as historical data analysis.

---

2. DirectQuery Mode

DirectQuery mode enables users to connect directly to the underlying data source without importing the data into Power BI.

Key Features:

• Real-Time Data: Queries are sent directly to the data source whenever a user interacts with the report, ensuring access to the most current data.

• Data Storage: No data is stored in Power BI; instead, the data remains in the original source (e.g., SQL Server, Oracle).

• Performance Considerations: Performance depends on the underlying data source, and complex queries may result in slower performance compared to import mode.

• Limitations: There are restrictions on certain features and DAX functions that can be used in DirectQuery mode.

Use Cases:

• Suitable for real-time dashboards and scenarios where immediate access to the latest data is critical, such as monitoring operational metrics.

---

3. Dual Mode

Dual mode is a hybrid option that allows a table to be used in both Import and DirectQuery modes, depending on the context of the query.

Key Features:

• Versatility: Dual mode allows for flexibility in how data is accessed; it can leverage the performance benefits of Import mode while still allowing real-time access when needed.

• Context-Sensitive: If a query can be satisfied with the in-memory data, it will use that; otherwise, it will query the underlying data source directly.

• Use of Composite Models: Dual mode is often used in composite models, where data from multiple sources is combined.

Use Cases:

• Useful in scenarios where some data can be pre-aggregated or cached for performance, while still needing to access real-time data for detailed analysis.

---

4. Composite Mode

Composite mode allows users to create models that combine data from both Import and DirectQuery sources.

Key Features:

• Hybrid Approach: Users can mix data sources, utilizing both imported data for performance and direct queries for real-time data.

• Flexibility: This mode allows for more complex data models that can leverage the strengths of both access methods.

• Data Relationships: Users can create relationships between tables from different sources, enhancing the analytical capabilities of the report.

Use Cases:

• Ideal for complex reporting scenarios where users need both fast access to historical data and real-time insights from live data sources.

/INFO

---

QUESTION 56.

You are profiling data by using Power Query Editor. You have a table that contains a column named column1. Column statistics and Value distribution for column1 are shown in the following exhibit.

Column statistics:

Count=277329

Error=0

Empty=0

Distinct=365

Unique=20

Empty string=0

Min=Alder

Max=unknown

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

There __ only once.
(a) are 20 values that occur
(b) are 365 values that occur
(c) are 277,229 values that occur
(d) is one value that occurs

The Pear, Flowering species is
 found more often in column1 
than the __ species.
(a) Ash, Green
(b) Crabapple, Flowering
(c) Elm, American
(d) Spruce, Blue

.

.

.

[1] There __ only once. (a) are 20 values that occur

[2] The Pear, Flowering species is found more often in column1 than the __ species. (c) Elm, American

---

QUESTION 57.

The data model must support the following analysis:

• Total sales by product by month in which the order was placed.

• Quantities sold by product by day on which the order was placed.

• Number of sales transactions by quarter in which the order was placed.

For each of the following statements, select Yes if the statement is true. Otherwise, select NO.

[1] Removing the LastUpdated column 
from the Sales table reduces the 
model size while still supporting 
the required analysis (Y/N).

[2] Removing the ProductID column 
from the Sales table reduces the 
model size while still supporting 
the required analysis (Y/N).

[3] Removing the ShipDate column 
from the Sales reduces the 
model size while still supporting 
the required analysis (Y/N).

.

.

.

[1] Removing the LastUpdated column from the Sales table reduces the model size while still supporting the required analysis (Y/N): Yes. The LastUpdated column is not necessary for calculating total sales, quantities sold, or the number of sales transactions based on the order date.

[2] Removing the ProductID column from the Sales table reduces the model size while still supporting the required analysis (Y/N): No. The ProductID column is essential for analyzing total sales and quantities sold by product.

[3] Removing the ShipDate column from the Sales table reduces the model size while still supporting the required analysis (Y/N): Yes. The ShipDate column is not required for the analysis since all calculations are based on the order date.

---

QUESTION 58.

You are creating a Line chart in a Power Bl report as shown in the following exhibit.

Select the answer choice that completes each statement based on the given information.

[1] The dashed line representing the 
Year Average Employee Count 
was created by using __.
(a) a trend line
(b) a secondary axis
(c) an average reference line
(d) two measures in the Values bucket

[2] To enable users to drill down to 
weeks or days, add the Weeks and 
Days field to the __ bucket.
(a) Axis
(b) Values
(c) Legend 
(d) Secondary values

.

.

.

[1] The dashed line representing the Year Average Employee Count was created by using: (c) an average reference line

[2] To enable users to drill down to weeks or days, add the Weeks and Days field to the: (a) Axis

---

QUESTION 59.

You have a column named UnitslnStock as shown in the following exhibit.

UnitsInStock has 75 non-null values, of which 51 are unique.

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

[1] When a table visual is 
created in a report and 
UnitsInStock is added to the 
values, there will be __ in 
the table.
(a) 0 rows.
(b) 1 row.
(c) 51 rows.
(d) 75 rows.

[2] Change the "Summarize by 
setting" of the UnitsInStock 
column, and then adding the 
column to a table visual, will
 __ the number of rows in the 
table visual.
(a) maintain
(b) reduce
(c) increase

.

.

.

[1] When a table visual is created in a report and UnitsInStock is added to the values, there will be:

• (d) 75 rows.

• This is because each non-null value in the UnitsInStock column will be represented as a separate row in the table visual.

[2] Changing the "Summarize by" setting of the UnitsInStock column, and then adding the column to a table visual, will:

• (b) reduce the number of rows in the table visual.

• Summarizing the column (e.g., by sum, average, count, etc.) will aggregate the data, thus reducing the number of rows displayed.

---

QUESTION 60.

You have a Power Bl report named Orders that supports the following analysis:

1. Total sales over time

2. The count of orders over time

3. New and repeat customer counts

The data model size is nearing the limit for a dataset in shared capacity. The model view for the dataset is shown in the following exhibit.

The data view for the Orders table is shown in the following exhibit. The Orders table relates to the Customers table by using the CustomerID column. The Orders table relates to the Date table by using the OrderDate column.

For each of the following statements, select Yes if the statement is true, Otherwise, select No.

NOTE: Each correct selection is worth one point.

[1] Summarizing Orders by the 
CustomerID, OrderID, and 
OrderDate columns will reduce 
the model size while still 
supporting the current 
analysis (Y/N).

[2] Removing the CustomerID 
column from Orders will reduce
 the model size while still 
supporting the current 
analysis (Y/N).

[3] Removing the UnitPrice and
 Discount columns from Orders 
will reduce the model size 
while still supporting the 
current analysis (Y/N).

.

.

.

[1] Summarizing Orders by the CustomerID, OrderID, and OrderDate columns will reduce the model size while still supporting the current analysis:

No. Summarizing by these columns will not necessarily reduce the model size significantly, and it might not support all the required analyses, especially for detailed order-level insights.

[2] Removing the CustomerID column from Orders will reduce the model size while still supporting the current analysis:

No. The CustomerID column is essential for analyzing new and repeat customer counts, so removing it would not support the current analysis.

[3] Removing the UnitPrice and Discount columns from Orders will reduce the model size while still supporting the current analysis:

Yes. These columns are not required for the analyses of total sales over time, the count of orders over time, or new and repeat customer counts. Removing them can help reduce the model size.

---

QUESTION 61.

You have a Power BI model that contains a table named Date. The table has the following columns.

You need to add a column that will be used to sort the Year Month column chronologically.

.

.

.

To sort the Year_Month column chronologically, you can add a new column that combines the Year and Month_Number columns. This new column will ensure that the Year_Month values are sorted in the correct order. Here's how you can do it:

[1] Open Power BI Desktop. 2. Go to the Modeling tab. 3. Click on New Column. 4. Enter the following DAX formula:

Sort_Year_Month = Date[Year] * 100 + Date[Month_Number]

This formula creates a new column named Sort_Year_Month that combines the year and month number in a way that ensures chronological sorting.

[2] After creating the new column, select the Year_Month column. 2. In the Column tools tab, set the Sort by Column to Sort_Year_Month.

---

QUESTION 62.

You have a report in Power BI Desktop. You add a key influencers visual as shown in the exhibit. (Click the Exhibit tab.)

What influences Attrition to Increase when Overtime is ...

Select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

[1] Identifying additional 
factors that increase 
attrition can be achieved by 
__.
(a) turning on Cross-report
(b) adding more fields to explain by
(c) adding more fields to expand by
(d) moving fields from Explain by to Expand by

[2] Employee attrition is __ 
times greater when employees 
work overtime.
(a) 0.11
(b) .2
(c) 1
(d) 3

.

.

.

To complete the statements based on the information presented in the graphic:

[1] Identifying additional factors that increase attrition can be achieved by: (b) adding more fields to explain by

[2] Employee attrition is __ times greater when employees work overtime: (d) 3

---

.