300-T2-26-63

You have the tables shown in the following table.The Impressions table contains approximately 30 million records per month.You need to create an ad analytics system to meet the following requirements:✑ Present ad impression counts for the day, campaign, and site_name. The analytics for the last year are required.Minimize the data model size.Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

• Create one-to-many relationships between the tables.

• Group the Impressions query in Power Query by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.

• Create a calculated table that contains Ad_id, Site_name, and Impression_date.

• Create a calculated measure that aggregates by using the COUNTROWS function.

Answer explanation

Incorrect:Not C: A calculated table would increase the data model size.Not D: Need Impression_date etc.

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. Each product has one product category.You need to ensure that you can analyze sales by product category.How should you configure the relationship from ProductCategory to Products? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.

• Cardinality: One-to-many

  Cross-filter direction: Single

• Cardinality: One-to-one

  Cross-filter direction: Single

• Cardinality: One-to-one

  Cross-filter direction: Both

• Cardinality: One-to-many

  Cross-filter direction: Both

Answer explanation

Box 1: One-to-many -The one-to-many and many-to-one cardinality options are essentially the same, and they're also the most common cardinality types.Incorrect: A many-to-many relationship means both columns can contain duplicate values. This cardinality type is infrequently used. It's typically useful when designing complex model requirements. You can use it to relate many-to-many facts or to relate higher grain facts. For example, when sales target facts are stored at product category level and the product dimension table is stored at product level.Box 2: Single -Incorrect:Bear in mind that bi-directional relationships can impact negatively on performance. Further, attempting to configure a bi-directional relationship could result in ambiguous filter propagation paths. In this case, Power BI Desktop may fail to commit the relationship change and will alert you with an error message.Reference:https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

You import a Power BI dataset that contains the following tables:✑ Date✑ Product✑ Product InventoryThe Product Inventory table contains 25 million rows. A sample of the data is shown in the following table.The Product Inventory table relates to the Date table by using the DateKey column. The Product Inventory table relates to the Product table by using theProductKey column.You need to reduce the size of the data model without losing information.What should you do?

• Change Summarization for DateKey to Don't Summarize.

• Remove the relationship between Date and Product Inventory

• Change the data type of UnitCost to Integer.

• Remove MovementDate.

Answer explanation

The DateKey and MovementDate columns have the same information. Movementdate can be removed.Incorrect:Not C: Integer data type would lose data.

You are enhancing a Power BI model that has DAX calculations.You need to create a measure that returns the year-to-date total sales from the same date of the previous calendar year.Which DAX functions should you use? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.

• Sales PYTD =

  VAR startyear =

  STARTOFYEAR (PREVIOUSYEAR ( 'Calendar'[Date]) )

  VAR enddate =

  LASTDATE( Sales[Date]) - 365

  RETURN

  CALCULATE( SUM (Sales[Sales]), SAMEPERIODLASTYEAR('Calendar'[Date],startyear, enddate))

• Sales PYTD =

  VAR startyear =

  STARTOFYEAR (PREVIOUSYEAR ( 'Calendar'[Date]) )

  VAR enddate =

  LASTDATE( Sales[Date]) - 365

  RETURN

  CALCULATE( SUM (Sales[Sales]), DATESBETWEEEN('Calendar'[Date],startyear, enddate))

• Sales PYTD =

  VAR startyear =

  STARTOFYEAR (PREVIOUSYEAR ( 'Calendar'[Date]) )

  VAR enddate =

  LASTDATE( Sales[Date]) - 365

  RETURN

  SUM( CALCULATE (Sales[Sales]), DATESBETWEEEN('Calendar'[Date],startyear, enddate))

• Sales PYTD =

  VAR startyear =

  STARTOFYEAR (PREVIOUSYEAR ( 'Calendar'[Date]) )

  VAR enddate =

  LASTDATE( Sales[Date]) - 365

  RETURN

  SUM( CALCULATE (Sales[Sales]), SAMEPERIODLASTYEAR('Calendar'[Date],startyear, enddate))

Answer explanation

SAMEPERIODLASTYEAR expects only one parameter. Since in between dates were provided, then it is logical that the answer is DATESBETWEEN.

Sales PYTD =

VAR startyear =

    STARTOFYEAR (PREVIOUSYEAR ( 'Date'[Date]) )

VAR enddate =

    LASTDATE( SalesOrder[OrderDate]) - 365

RETURN

CALCULATE( SUM (SalesOrder[Amount]), DATESBETWEEN('Date'[Date],startyear, enddate))

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records. During the development process, you need to import a sample of the data from the Order table. Solution: You add a report-level filter that filters based on the order date. Does this meet the goal?

• Yes

• No

Answer explanation

You want the raw data, not a report with the data.Instead add a WHERE clause to the SQL statement.Reference:https://docs.microsoft.com/en-us/power-query/native-database-query

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source.

The sales table has the following date foreign keys:

✑ Due Date

✑ Order Date

✑ Delivery Date

You need to support the analysis of sales over time based on all the date foreign keys.

Solution: For each date foreign key, you add inactive relationships between the sales table and the date table. Does this meet the goal?

• Yes

• No

Answer explanation

Instead: Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables.Reference:https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

The answer should be NO. You can reference an inactive relationship whit DAX function USERELATIONSHIP(), but using DAX is not mentioned here. So follow this refactory methodology: Create a copy of the role-playing table, providing it with a name that reflects its role. If it's an Import table, we recommend defining a calculated table. If it's a DirectQuery table, you can duplicate the Power Query query. Source: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys: ✑ Due Date ✑ Order Date ✑ Delivery Date You need to support the analysis of sales over time based on all the date foreign keys. Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables. Does this meet the goal?

• Yes

• No

Answer explanation

Refactoring methodology -Here's a methodology to refactor a model from a single role-playing dimension-type table, to a design with one table per role.1. Remove any inactive relationships.2. Consider renaming the role-playing dimension-type table to better describe its role. In the example (not present here), the Airport table is related to theArrivalAirport column of the Flight table, so it's renamed as Arrival Airport.3. Create a copy of the role-playing table, providing it with a name that reflects its role. If it's an Import table, we recommend defining a calculated table. If it's aDirectQuery table, you can duplicate the Power Query query.In the example, the Departure Airport table was created by using the following calculated table definition.Departure Airport = 'Arrival Airport'Create an active relationship to relate the new table.4. Consider renaming the columns in the tables so they accurately reflect their role. In the example, all columns are prefixed with the word Departure or Arrival.These names ensure report visuals, by default, will have self-describing and non-ambiguous labels. It also improves the Q&A experience, allowing users to easily write their questions.5. Consider adding descriptions to role-playing tables. (In the Fields pane, a description appears in a tooltip when a report author hovers their cursor over the table.) This way, you can communicate any additional filter propagation details to your report authors.Reference:https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

You receive revenue data that must be included in Microsoft Power BI reports.You preview the data from a Microsoft Excel source in Power Query as shown in the following exhibit.You plan to create several visuals from the data, including a visual that shows revenue split by year and product.You need to transform the data to ensure that you can build the visuals. The solution must ensure that the columns are named appropriately for the data that they contain.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

• Select Department and Product and Unpivot Other Columns

  Rename Attribute column to Year and the Value column to Revenue

  Select Use First Row as Headers

• Select Use First Row as Headers

  Select Department and Product and Unpivot Other Columns

  Rename Attribute column to Year and the Value column to Revenue

• Select Department and Product and Unpivot Other Columns

  Rename Attribute column to Revenue and the Value column to Year

  Select Use First Row as Headers

• Select Department and Product and Unpivot Other Columns

  Rename Attribute column to Revenue and the Value column to Year

  Select Use Header as First Row

Answer explanation

Select Use First Row as Headers

Select Department and Product and Unpivot Other Columns

Rename Attribute column to Year and the Value column to Revenue

You have a Power BI report named Orders that supports the following analysis:✑ Total sales over time✑ The count of orders over time✑ New and repeat customer countsThe 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 columnSelect the correct statements.NOTE: Each correct selection is worth one point.

• Summarizing Orders by the CustomerID, OrderID, and OrderDate columns will reduce model size while still supporting the current analysis. [Yes]

  Removing the CustomerID column from Oders will reduce the model size while still supporting the current analysis. [Yes]

  Removing the UnitPrice and Discount columns from Oders will reduce the model size while still supporting the current analysis. [Yes]

• Summarizing Orders by the CustomerID, OrderID, and OrderDate columns will reduce model size while still supporting the current analysis. [Yes]

  Removing the CustomerID column from Oders will reduce the model size while still supporting the current analysis. [No]

  Removing the UnitPrice and Discount columns from Oders will reduce the model size while still supporting the current analysis. [Yes]

• Summarizing Orders by the CustomerID, OrderID, and OrderDate columns will reduce model size while still supporting the current analysis. [Yes]

  Removing the CustomerID column from Oders will reduce the model size while still supporting the current analysis. [No]

  Removing the UnitPrice and Discount columns from Oders will reduce the model size while still supporting the current analysis. [No]

• Summarizing Orders by the CustomerID, OrderID, and OrderDate columns will reduce model size while still supporting the current analysis. [No]

  Removing the CustomerID column from Oders will reduce the model size while still supporting the current analysis. [No]

  Removing the UnitPrice and Discount columns from Oders will reduce the model size while still supporting the current analysis. [Yes]

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? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.

• FILTER

  DATEADD

  DIVIDE

  Sales QoQ% =

  IF(

  ISFILTERED('financials'] [Date]),

  ERROR("Uh oh"),

  VAR PREV_QUARTER =

  FILTER

  (SUM('financials'] [Sales]),

  DATEADD('financials'] [Date]),-1,QUARTER))

  RETURN

  DIVIDE ((SUM('financials'] [Sales]) - PREV_QUARTER), PREV_QUARTER)

  )

• SUM

  DATEADD

  DIVIDE

  Sales QoQ% =

  IF(

  ISFILTERED('financials'] [Date]),

  ERROR("Uh oh"),

  VAR PREV_QUARTER =

  SUM

  (FILTER('financials'] [Sales]),

  DATEADD('financials'] [Date]),-1,QUARTER))

  RETURN

  DIVIDE ((SUM('financials'] [Sales]) - PREV_QUARTER), PREV_QUARTER)

  )

• FIND

  SUM

  DIVIDE

  Sales QoQ% =

  IF(

  ISFILTERED('financials'] [Date]),

  ERROR("Uh oh"),

  VAR PREV_QUARTER =

  FIND

  (SUM('financials'] [Sales]),

  DATEADD('financials'] [Date]),-1,QUARTER))

  RETURN

  DIVIDE ((SUM('financials'] [Sales]) - PREV_QUARTER), PREV_QUARTER)

  )

• CALCULATE

  DATEADD

  DIVIDE

  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)

  )

Answer explanation

Box 1: CALCULATE -Calculate the sum.

Box 2: DATEADD -DATEADD -1 QUARTER will give the previous month.

Box 3: DIVIDE -Use DIVIDE to get the relative change.