PL-300 Microsoft Power BI Data Analyst Questions and Answers

Use Power Query to calculate calendar quarter and calendar month.

Scenario:

A single dataset must support all three reports:

- The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order month or quarter, product category, and sales region.

- The Top Products report will show the top 20 products based on the highest sales amounts sold in a selected order month or quarter, sales region, and product category.

The data model must minimize the size of the dataset as much as possible, while meeting the report requirements and the technical requirements.

Box 1: many-to-one

Each employee in the Sales Employees table is assigned to one sales region. Multiple employees can be assigned to each region.

The Suppliers table has a Region column.

Box 2: Suppliers table

Box 1: Top N

Scenario: The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order month or quarter, product category, and sales region.

Once you drag to SKU to Visual level filter you should get Top N option

Note: The two most common filter types: automatic and manual.

Then there are more advanced filters.

Box 2: Visual

Once you drag to SKU to Visual level filter you should get Top N option.

Scenario: The On-Time Shipping report will show the following metrics for a selected shipping month or quarter:

The percentage of orders that were shipped late by country and shipping region

Customers that had multiple late shipments during the last quarter

Note: Bar and column charts are some of the most widely used visualization charts in Power BI. They can be used for one or multiple categories. Both these chart types represent data with rectangular bars, where the size of the bar is proportional to the magnitude of data values.

The difference between the two is that if the rectangles are stacked horizontally, it is called a bar chart. If the rectangles are vertically aligned, it is called a column chart.

Box 1: CONTOSO BIKES report

Box 2: three datasets

Images, tweets and the Contoso datasets.

Open powerbi.com

Select sales report

Pin the page

You can add synonyms to tables and columns.

Note: This step applies specifically to Q&A (and not to Power BI reports in general). Users often have a variety of terms they use to refer to the same thing, such as total sales, net sales, total net sales. You can add these synonyms to tables and columns in the Power BI model.

This step applies specifically to Q&A (and not to Power BI reports in general). Users often have a variety of terms they use to refer to the same thing, such as total sales, net sales, total net sales. You can add these synonyms to tables and columns in the Power BI model.

case states there is already a report published and the datamodel contains measures. therefore and to be able to use the measures in the datamodel you should connect to the existing dataset (which was created when you plublished the report) instead of starting from scratch with the files in the SharePoint folder.

R visuals in the Power BI service have a few limitations including:

Data size limitations – data used by the R visual for plotting is limited to 150,000 rows. If more than 150,000 rows are selected, only the top 150,000 rows are used and a message is displayed on the image. Additionally, the input data has a limit of 250 MB.

You can create a custom tooltip page that shows more details about the selected category, such as this:

To create a custom tooltip page and prepare it for use, you need to perform these three actions34:

Add and configure visuals on the tooltip page. You can add any visuals, images, or other items that you want to show on the tooltip page. You can also format them as you like.

For the tooltip page, set Allow use as tooltip to On. This will enable Power BI to recognize this page as a tooltip page. You can also change the Page size to Tooltip to fit your content better.

For the target visual, set Tooltip type to Report page. This will allow you to select which report page you want to use as a custom tooltip for your visual. You can also filter your tooltip by fields from your target visual.

https://docs.microsoft .com/en-us/power-bi/admin/service-admin-rls

https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-build-permissions

"The analysts responsible for each business unit must see all the data the board sees, except the profit and loss data, which must be restricted to only their business unit's data. The analysts must be able to build new reports from the dataset that contains the profit and loss data" => one dataset and two separate workspaces Reason: All data can be imported into one dataset also if these are two logical models. Shared dimensions can be reconsumed in both models. Reports and additional materials can be shared to the board with an app. The "profit and loss" data model needs RLS for the analysts and the analysts must have just read access to the original workspace. In a separate workspace with contributer (or more rights) they can create new reports (with live connection to the dataset). It is also stated that the new reports mustn't be shared so therefore no need to include them into the app. Import vs. DirectQuery: Due to RLS requirements an imported dataset is needed. It is not possible with file sources and Sharepoint lists.

Box 1: The Viewer role to the workspace

The Viewer role gives a read-only experience to its users. They can view dashboards, reports, or workbooks in the workspace, but can’t browse the datasets or dataflows. Use the Viewer role wherever you would previously use a classic workspace set to “Members can only view Power BI content”.

Box 2: Build

The analysts must be able to build new reports from the dataset that contains the profit and loss data.

Scenario: The reports must be made available to the board from powerbi.com.

The analysts responsible for each business unit must see all the data the board sees, except the profit and loss data, which must be restricted to only their business unit's data. The analysts must be able to build new reports from the dataset that contains the profit and loss data, but any reports that the analysts build must not be included in the quarterly reports for the board. The analysts must not be able to share the quarterly reports with anyone.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-types-for-reports-and-q-and-a

Scenario: Revenue projections are set at the monthly level and summed to show projections for the quarter.

Box 1: IF

Box 2: ISFILTERED

ISFILTERED returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered then the function returns FALSE.

Box 3: SUM

Box 1: Using a workspace membership

Scenario:

The company wants to provide a single package of reports to the board that contains custom navigation and links to supplementary information.

Note: Workspace is a shared environment for a group of people. You can have multiple Power BI content in a workspace. One workspace can have hundreds of dashboards, reports, and datasets in it.

Box 2: A mail-enabled security group

Scenario: Security Requirements

The reports must be made available to the board from powerbi.com. A mail-enabled security group will be used to share information with the board.

Box 1: A DAX Calculated measure

Box 2: Date.EndofQuarter(#date([Year],[Mont],1))

ENDOFQUARTER returns the last date of the quarter in the current context for the specified column of dates.

The following sample formula creates a measure that returns the end of the quarter, for the current context.

= ENDOFQUARTER(DateTime[DateKey])

Scenario: At least one of the balance sheet reports in the quarterly reporting package must show the ending balances for the quarter, as well as for the previous quarter.

DATESQTD returns a table that contains a column of the dates for the quarter to date, in the current context.

Scenario: Executives require a visual that shows sales by region.

Need to change the sales_id column from Varchar to Whole Number (Integer).

https://powerbi.microsoft.com/en-us/blog/using-u sername-in-dax-with-row-level-security/

Scenario: Region managers require a visual to analyze weekly sales and returns.

To relate the two tables we need a common column.

Scenario: The customer service department requires a visual that can be filtered by both sales month and ship month independently.

The sale department requires reports that contain the number of sales transactions.

The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.

Scenario: The sales managers require a visual to analyze sales performance versus sales targets.

Box 1: KPI

A Key Performance Indicator (KPI) is a visual cue that communicates the amount of progress made toward a measurable goal.

Box 2: Sales[sales_amount]

Box 3: Date[month]

Time > FiscalMonth. This value will represent the trend.

Box 4: Targets[sales_target]