PL - 300 Practice Questions

With a many-to-many relationship, how many options for cross-filter direction do you have? One Two Three

- Three

True or False: Column profile displays the minimum and maximum values within the first 1000 rows for a specific column. - True - False

- True

What is the Dual mode? What are some Pros?

- Uses both import Mode and DirectQuery Mode PRO - Enhanced Responsiveness - Efficient Data Handling

What does NORM.DIST(x, mean, standard_dev, cumulative) do?

Returns normal distribution.

Whare are some statistical functions

STDEV.P, RANKX, MEDIANX, NORM.DIST, T.DIST, VAR.S, COUNTBLANK, COUNTROWS, DISTINCTCOUNT, MIN, MAX

Bi-directional means?

both means the filter can propagate in both directions

What do Fact tables represent?

business processes, measurements, metrics or facts.

What do Dimension tables store?

contextual data or descriptive attributes about the facts. These tables are connected to the Fact table via key columns.

Which of the following are the benefits of using a shared dataset? Select all that apply. - The dataset is accessible from both office and remote locations. - The dataset can scale as the organization grows. - The dataset acts as a single source of truth. - The dataset is accessible at high speeds.

- The dataset is accessible from both office and remote locations. - The dataset can scale as the organization grows. - The dataset acts as a single source of truth.

You are importing a dataset of 10 rows that contains a Date type column. One of the values in the Date column is Bicycle. Which of the following statements is correct? Select all that apply. - Column profile will show 10% of values as Error. - You can replace the value using the Replace Values feature. - You can replace the value using the Replace Errors feature. - Column quality will show 10% of values as Error.

- You can replace the value using the Replace Errors feature. - Column quality will show 10% of values as Error.

What is the Reference Queries Query Pane?

- allow you to use another query as the base of a query. Using the previous example, you can apply the column rename transformations in one query. And then create two new queries which reference the first query to perform the subsequent operations to create the Sales and Country tables. Now, if you update anything in the first query, the dependent queries will be automatically updated.

What is the Import mode?

- data is copied in to power bi, - need to refresh if updated, - can use scheduled data refreshes

What is the DirectQuery mode? What are some Cons?

- import live queries CON - Data source transfer with every query - Can slow down transfer - Delayed Visualizations & reports

What is the Duplicate Queries Query Pane?

- you can duplicate a query to create a copy and perform different transformations on it, from the original query. This allows you to transform data into different formats and insert it into different tables.

The sales team at Adventure Works has two tables of data for tracking bicycle sales: Sales and Product. Each row in the Sales table is associated with one of the bicycles in the Product table. Which of the following cardinalities is most suitable between the Sales and the Product table? - Many-to-many -Many-to-one -One-to-one

-Many-to-one

A many-to-many relationship means?

A many-to-many relationship means both related columns can contain duplicate values.

What is a many-to-many relationship?

A many-to-many relationship would mean that each row in Table A is related to many rows in Table B, and each row in Table B is related to many rows in Table A. For example, if you had a table of books and a table of authors, a book can be written by multiple authors and an author can write multiple books.

What is many-to-one/one-to-many relationship?

A many-to-one relationship would mean that multiple rows in Table A can be related to a single row in Table B. The relationship from Table B to Table A is a one-to-many relationship. That is, each row in Table B is related to multiple rows in Table A.

What is data quality?

Data quality refers to the accuracy, completeness, and reliability of the data

What is a Schema?

Dataset tables framework

True or False: When performing an append query, the columns of both tables must be the same. True False

False

True or False: You can only manage the relationships between data tables in the Model view of Power BI Desktop? True False

False Power BI's Data view also allows you to select Manage relationship to open a dialog box where you can create and configure relationships.

USERELATIONSHIP (use relationship function ) Filter Arguments Onlyno row-level security allowed first define relationship Column Argument REQUIREDNest up to 10

Filter Arguments Only no row-level security allowed first define relationship Column Argument REQUIRED Nest up to 10

What are the 3 different dataset modes?

Import, DirectyQurey, Dual

You are importing a dataset containing 500 rows. Which of the following should be validated before importing the data? Select all that apply. Column Quality shows data is 100% valid. Column Data Types are correct. Column Distribution shows all values are unique. Column Profile shows that the total rows is equal to 500.

Column Quality shows data is 100% valid. Column Data Types are correct. Column Profile shows that the total rows is equal to 500.

Cross-filter direction refers to?

Cross-filter direction refers to the direction of filter propagation between two related model tables.

In the Power BI service, how do you add a visual from a report to a dashboard? Copy and paste the visual into the dashboard. Download the visual as an image and upload it to the dashboard. Use the "Embed" option on the visual. Pin the visual using the 'Pin' icon on the top right of the visual.

Pin the visual using the 'Pin' icon on the top right of the visual.

You are importing a data source containing two columns: SalePrice and Cost. You need to create a calculated column to represent the Profit. Which of the following DAX queries will achieve the desired result? Profit = SalePrice / Cost Profit = SalePrice - Cost SalePrice - Cost SalePrice / Cost

Profit = SalePrice - Cost

What is a one-to-one relationship?

Relationship between two tables where each record in the first table corresponds to exactly one record in the second table. A one-to-one relationship would mean that each row in Table A is directly related to only one row in Table B and vice versa. For example, if Table A contained countries and Table B contained capital cities, the relationship would be one to one, as each country has only one capital and each capital belongs to only one country

What does DATESINPERIOD( ) do?

Returns a table that contains a column of dates that begins on the starting date and continues for the specified number of intervals.

What does MEDIANX(<table>, <expression>) do?

Returns the median of an expression evaluated for each row of the table.

What does RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) do?

Returns the ranking of a number in a list of numbers for each row in the table argument.

What does STDEV.P(<columnname>) do?

Returns the standard deviation of the entire population.

Why is it important to ensure that data is good quality? Select all that apply. To help ensure accurate reporting To optimize visualizations To ensure effective decision-making To ensure correct column data types

To help ensure accurate reporting To ensure effective decision-making

Which of the following are limitations that must be considered when using dataflows? To refresh more than 10 dataflows, a Premium subscription is required. Dataflows are limited to small volumes of data. There is limited visibility of dependencies between dataflows. The maximum number of linked dataflows is 32.

To refresh more than 10 dataflows, a Premium subscription is required. There is limited visibility of dependencies between dataflows. The maximum number of linked dataflows is 32.

What is the DAX use relationship function?

Total Sales = CALCULATE (SUM ( 'Sales'[Sales Amount] ), USERELATIONSHIP ( 'Date'[Date], Sales[ShippingDate] ) )

You have created a duplicate query in Microsoft Power BI from a query that contained transformations. Which of the following statements is true? Select all that apply. Transformations copied from the original query will execute in both queries. Changes to transformations in the original query will not impact the duplicate query. Changes to transformations in the original query will automatically update the duplicate query. Transformations copied from the original query will execute only in the original query.

Transformations copied from the original query will execute in both queries. Changes to transformations in the original query will not impact the duplicate query.

True or False: Reducing columns and rows will reduce the required storage space for a table. True False

True

What does Reference Queries allow you to do?

allow you to use another query as the base of a query. Using the previous example, you can apply the column rename transformations in one query. And then create two new queries which reference the first query to perform the subsequent operations to create the Sales and Country tables. Now, if you update anything in the first query, the dependent queries will be automatically updated.

What are Append Queries

allows you to append multiple sources into a single table

How are the schemas data broken down?

data is broken down into fact and dimension tables

What is Column distribution?

displays the distribution of the data within the column and the counts of distinct and unique values. Distinct values are all the different values in a column, including duplicates and null values. Distinct tells you the total count of how many values are present. On the other hand, unique values do not include duplicates or nulls. Unique tells you how many of those values only appear once.

What is Column quality?

displays the percentage of data that is valid in error and empty. In an ideal situation, you want 100% of the data to be valid.

What are common types of inconsistencies and quality issues that can occur?.

duplicate, rows, empty or missing values, and errors, or invalid values

What are Merge Queries?

it is to merge two tables, you need to tell the merge query which type of join you would like to use. The Join type informs Power Bi how to merge the two tables. A join requires that there is a common column between the two tables.

What is Column profile?

it provides a more in depth look into the statistics within the columns for the first 1,000 rows of data, this column provides several different values, including the count of rows, which is important when verifying whether you imported your data successfully.

What does Creating a measure using the DAX use relationship function do?

it temporarily switches the inactive relationship to active.

What does one-to-one cardinality mean?

means both related columns contain unique values

What do Snowflake schema further normalizes?

the dimension tables. For example, the product table is broken down into product category and product subcategory tables based on category ID and subcategory ID.

Single cross-filtered direction means?

the filter propagates unidirectionally from one table to the other within the relationship

what is Cardinality defines?

the nature of the connection between two data tables

What is Composite Model?

when connected to multiple data sources

what is CROSSFILTER Function?

with it you can change the cross-filter direction for a specific measure while maintaining the original settings

One-to-Many Support what cross-filter direction

Both & Single

What does TOTALYTD( ) do?

Calculates the total year-to-date value for a given expression.

What can Duplicate Queries allow you to do?

you can duplicate a query to create a copy and perform different transformations on it, from the original query. This allows you to transform data into different formats and insert it into different tables.

You have created a duplicate query in Microsoft Power bi from a query that contains transformations. Which of the following statements is true? Select all that apply. - Transformations copied from the original query will execute only in the original query. - Changes to transformations in the original query will automatically update the duplicate query. - Changes to transformations in the original query will not impact the duplicate query. - Transformations copied from the original query will execute in both queries.

- Changes to transformations in the original query will not impact the duplicate query. - Transformations copied from the original query will execute in both queries.

What is Query Parameter Values?

- Changing between a test data source and a production data source, or filtering data from your data source - Home / Manage Parameters

Which of the following data profiling tools allows you to inspect the percentage of valid, error, and empty data in a column? - Column profile - Column distribution - Column quality

- Column quality

Which DAX function can be used to compute the sales values between two specified dates? DATEADD DATESYTD DATESBETWEEN DATESINPERIOD

- DATESBETWEEN

Question 3 Which of the following storage modes will copy data to Microsoft Power BI? Select all that apply. - DirectQuery mode - Dual mode - Import mode

- Dual mode - Import mode

What are the two Query Panes?

- Duplicate Queries - Reference Queries

True or False: A shared dataset is more secure than a local dataset. - True - False

- False

True or False: Distinct values refer to the number of values that only occur once in the dataset. - True - False

- False

True or False: If your data model does not contain a date table, you will not be able to perform time intelligence calculations using DAX. True False

- False Power BI's Auto date/time functionality allows you to create time intelligence calculations with DAX even if your data model does not have a common date dimension table.

Question 3 True or False: The DAX CALENDAR function automatically detects the data range of your dataset and creates a calculated date column. True False

- False The DAX CALENDARAUTO function automatically detects the date range from your dataset, while CALENDAR needs you to provide a date range.

True or False: In Microsoft Power BI you can create a calculated column using DAX in the Power Query Editor. True False

- False You can create calculated columns in the Report view, Data view or Model view of Power BI Desktop.

True or False: To activate multiple inactive relationships within your data model, you need to write separate DAX code using the USERELATIONSHIP function in Power BI. True False

- False You can nest up to 10 USERELATIONSHIP functions in a single expression to activate multiple inactive relationships.

Which of the following elements of a DAX formula is optional while writing a CALCULATE expression? Column Name Table Name Filter

- Filter

You are formatting the column properties of your data tables within your data model. In which section of Properties can you change the data type of the column? General Advanced Formatting

- Formatting The Formatting section allows you to change the data type of the column as per your analytical needs.

The inventory team at Adventure Works has two tables of data for tracking inventory levels: Stock and Product. If a product is not in stock, there is no row in the Stock table for the product. The team wants to merge the tables into a single table containing products that are in stock. If Stock is the left table and Product is the right table, which of the following join types will achieve the desired outcome? Select all that apply. - Inner Join - Full Outer Join - Right OuterJoin - Left Outer Join

- Inner Join - Left Outer Join

You have a Product table as a left table and a Category table as a right table. You want to merge these tables so that you have a table containing only products and each product row containing its category name or null. Which of the following joins is most suitable? - Right Outer Join - Inner Join - Full Outer Join - Left Outer Join

- Left Outer Join

Which statement about measures in Microsoft Power BI is correct? Measures can only be created using DAX. Measures are stored information in the data model. Measures can be referenced by other measures. Measures are stored in the data model.

- Measures can be referenced by other measures.

What are the 3 main Table Relationship?

- One To One - Many To One - Many to Many

Which of the following components of Microsoft Power BI allow you to add a description about your table? Select all that apply. Data view Report view Model view Power Query Editor

- Power Query Editor In Power Query Editor, you can rename the table as query and add a description about the query (table). - Model view Model view in Power BI Desktop provides plenty of options to configure and format both table and column properties.

Which language can you use to create a common date table in Microsoft Power BI? Select all that apply. DAX C++ M Python

- Python You can create a date table using Pandas in Python and import it to a Power BI data model. - DAX DAX is the most common way to generate a common date table within your data model. - M M and Query Editor is another way to create a universal date table, especially by implementing parameters so you can use the same date table for a variety of different data models.

You're preparing a dataset for import into Microsoft Power BI and have accidentally changed a column to the wrong data type. What is the next action you can take to correct the mistake? Select all that apply. - Append a new step to the Applied Steps list to change the data type. - Remove the step from the Applied Steps list. - Edit the Changed Type step in the Applied Steps list.

- Remove the step from the Applied Steps list. - Edit the Changed Type step in the Applied Steps list.

You need to create a calculated column for the tax amount per sale. The tax amount is 25% of SalesAmount column, Which of the following DAX formulas will create the column. - TaxAmount = 0.25 * SalesAmount - TaxAmount = 25 * SalesAmount - 0.25 * SalesAmount - 25 * SalesAmount

- TaxAmount = 0.25 * SalesAmount

Question 2 How many table storage modes does Microsoft Power BI Desktop support? 4 1 3

3

If a dataflow links to another dataflow, the maximum number of linked data flows in the chain would be?

32

At the beginning of the data analysis process, which of the following activities are important to ensuring a successful analysis outcome? Select all that apply. - Connecting to the data sources - Identifying which data sources can provide the required data - Inspecting the data to ensure it meets requirements - Identifying the required data for the analysis

ALL OPTIONS APPLY

One-to-one Support what cross-filter direction

Both

Many-to-Both Support what cross-filter direction

Both & Single

What do you use for Calculated columns?

Data Analysis Expressions, or DAX formula to create new values for each row in the table.

What does SAMEPERIODLASTYEAR( )do?

DAX function that returns a set of dates for the same period in the previous year.

What does DATESBETWEEN( ) do?

DAX function that returns a table of dates that fall between two specified dates.

What does DATEADD( ) do?

DAX function that returns a table that contains a set of dates shifted by a specified number of intervals.

Which DAX function helps you to handle semi-additive measures? AVERAGE SUMX DIVIDE LASTDATE

LASTDATE

You have a Product table as a left table and a Category table as a right table. You want to merge these tables so that you have a table containing only products and each product row containing its category name or null. Which of the following joins is most suitable? Right Outer Join Left Outer Join Full Outer Join Inner Join

Left Outer Join

What is Microsoft Dataverse?

Microsoft Dataverse lets you securely store and manage data used by business applications. Data within Dataverse is stored within a set of tables.

Adventure Works' head office must keep track of employee keycards. For security reasons, each employee can only have one keycard. There are two tables: Employees and Keycards. What is the most suitable relationship for these two tables? - Many-to-one (Employees to Keycards) - Many-to-many - One-to-one - One-to-many (Employees to Keycards)

One-to-one

Adventure Works' head office must keep track of employee keycards. For security reasons, each employee can only have one keycard. There are two tables: Employees and Keycards. What is the most suitable relationship for these two tables? One-to-one Many-to-one (Employees to Keycards) One-to-many (Employees to Keycards) Many-to-many

One-to-one

Many-to-Many Support what cross-filter direction?

Single (Table A to Table B) Single (Table B to Table A) BOTH

What are the 2 type of Schemas

Star Snowflake

what are some DAX FUNCTIONS FOR TIME INTELLIGENCE?

TOTALYTD, DATESBETWEEN, DATESINPERIOD, SAMEPERIODLASTYEAR, DATEADD

You need to create a calculated column for the tax amount per sale. The tax amount is 25% of the SalesAmount column. Which of the following DAX formulas will create the column? TaxAmount = 0.25 * SalesAmount 25 * SalesAmount TaxAmount = 25 * SalesAmount 0.25 * SalesAmount

TaxAmount = 0.25 * SalesAmount

When a connector in Microsoft Power BI does not support DirectQuery mode, which of the following statements is true? - The connector will default to Import mode. - The connector will default to Dual mode. - All connectors support DirectQuery mode.

The connector will default to Import mode.

What is a role-playing dimension?

a single dimension that can be used for different purposes in the same data model

What is a star schema?

a single fact table is typically related to one or more dimension tables

What does one-to-many and many-to-one cardinality types do?

one-to-many and many-to-one cardinality types. These two types are essentially the same,where each value in one table column is related to multiple values in another.