Microsoft DP-500 Practice Test - Questions Answers, Page 9

Box 1: SELECTEDMEASURENAME()

SELECTEDMEASURENAME is used by expressions for calculation items to determine the measure that is in context by name.

Syntax: SELECTEDMEASURENAME()

No parameters.

Example:

The following calculation item expression checks if the current measure is Expense Ratio and conditionally applies calculation logic. Since the check is based on a string comparison, it is not subject to formula fixup and will not benefit from object renaming being automatically reflected. For a similar comparison that would benefit from formula fixup, please see the ISSLECTEDMEASURE function instead.

IF (

SELECTEDMEASURENAME = "Expense Ratio",

SELECTEDMEASURE (),

DIVIDE ( SELECTEDMEASURE (), COUNTROWS ( DimDate ) )

)

Box 2: SELECTEDVALUE()

SELECTEDVALUE returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.

Syntax:

SELECTEDVALUE(<columnName>[, <alternateResult>])

M1, M2, ... - A list of measures.

Reference: https://docs.microsoft.com/en-us/dax/selectedmeasurename-function-dax

https://docs.microsoft.com/en-us/dax/selectedvalue-function

Step 1: From Power BI Desktop, create a role named Project Managers.

Create roles

You can define roles within Power BI Desktop.

Step 2: Open Tabular Editor

Under Tables, select the table to which you want to apply a DAX rule.

In the Table filter DAX expression box, enter the DAX expressions. This expression returns a value of true or false. For example: [Entity ID] = "Value".

Step 3: From Power BI Desktop, add a DAX filter to the Project Managers role.

Step 4: For Table1, the Budget and Forecast columns, set the permissions to None.

Reference: https://docs.microsoft.com/en-us/power-bi/guidance/rls-guidance

Box 1: Sum

The Manage aggregations dialog shows a row for each column in the table, where you can specify the aggregation behavior. In the following example, queries to the Sales detail table are internally redirected to the Sales Agg aggregation table.

Box 2: GroupBy

Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced

Step 1: Create RangeStart and RangeEnd parameters.

Create parameters

In this task, use Power Query Editor to create RangeStart and RangeEnd parameters with default values. The default values apply only when filtering the data to be loaded into the model in Power BI Desktop. The values you enter should include only a small amount of the most recent data from your data source. When published to the service, these values are overridden by the incremental refresh policy.

Step 2: Apply a custom Date/Time filter to the data.

Filter data

With RangeStart and RangeEnd parameters defined, apply a filter based on conditions in the RangeStart and RangeEnd parameters.

Before continuing with this task, verify your source table has a date column of Date/Time data type.

Step 3: Define the incremental refresh policy for the table.

Define policy

After you've defined RangeStart and RangeEnd parameters, and filtered data based on those parameters, you define an incremental refresh policy. The policy is applied only after the model is published to the service and a manual or scheduled refresh operation is performed.

Step 4: Publish the model to the Power BI service.

Save and publish to the service When your RangeStart and RangeEnd parameters, filtering, and refresh policy settings are complete, be sure to save your model, and then publish to the service.

Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure

Box 1: CALCULATE

Box 2: CURRENTGROUP

CURRENTGROUP returns a set of rows from the table argument of a GROUPBY expression that belong to the current row of the GROUPBY result.

Remarks

This function can only be used within a GROUPBY expression.

This function takes no arguments and is only supported as the first argument to one of the following aggregation functions: AVERAGEX, COUNTAX, COUNTX, GEOMEANX, MAXX, MINX, PRODUCTX,

STDEVX.S, STDEVX.P, SUMX, VARX.S, VARX.P.

Note: COUNTX counts the number of rows that contain a non-blank value or an expression that evaluates to a non-blank value, when evaluating an expression over a table.

Reference: https://docs.microsoft.com/en-us/dax/currentgroup-function-dax

Box 1: three scatterplots

Compare Plots

Example, Draw two plots on the same figure:

import matplotlib.pyplot as plt

import numpy as np

#day one, the age and speed of 13 cars:

x = np.array([5,7,8,7,2,17,2,9,4,11,12,9,6])

y = np.array([99,86,87,88,111,86,103,87,94,78,77,85,86])

plt.scatter(x, y)

#day two, the age and speed of 15 cars:

x = np.array([2,2,8,1,15,8,12,9,7,3,11,4,7,14,12])

y = np.array([100,105,84,105,90,99,90,95,94,100,79,112,91,80,85])

plt.scatter(x, y)

plt.show()

Result:

Box 2: three marker symbols

One for each scatterplot. One default, and two defined.

Default is point.

v is triangle down.

^ is triangle up.

Reference: https://www.w3schools.com/python/matplotlib_scatter.asp

https://matplotlib.org/stable/api/markers_api.html

Box 1: stacked bar chart

matplotlib.pyplot.bar makes a bar plot.

The bars are positioned at x with the given alignment. Their dimensions are given by height and width. The vertical baseline is bottom (default 0).

Many parameters can take either a single value applying to all bars or a sequence of values, one for each bar.

Stacked bars can be achieved by passing individual bottom values per bar.

Stacked bar chart

This is an example of creating a stacked bar plot with error bars using bar. Note the parameters yeer used for error bars, and bottom to stack the women's bars on top of the men's bars.

import matplotlib.pyplot as plt

labels = ['G1', 'G2', 'G3', 'G4', 'G5']

men_means = [20, 35, 30, 35, 27]

women_means = [25, 32, 34, 20, 25]

men_std = [2, 3, 4, 1, 2]

women_std = [3, 5, 2, 3, 3]

width = 0.35 # the width of the bars: can also be len(x) sequence

fig, ax = plt.subplots()

ax.bar(labels, men_means, width, yerr=men_std, label='Men')

ax.bar(labels, women_means, width, yerr=women_std, bottom=men_means,

label='Women')

ax.set_ylabel('Scores')

ax.set_title('Scores by group and gender')

ax.legend()

plt.show()

Box 2: two items

Blue item and Green Item.

matplotlib.legend

The legend module defines the Legend class, which is responsible for drawing legends associated with axes and/or figures.

Note: A Diagram Legend is an element that you can add to your diagram to provide information about the colors and/or line thicknesses and styles that have been used in the current diagram, where those colors and other styles have some particular meaning.

Reference: https://matplotlib.org/stable/api/_as_gen/matplotlib.pyplot.bar.html

https://matplotlib.org/stable/gallery/lines_bars_and_markers/bar_stacked.html

https://matplotlib.org/stable/api/legend_api.html

Box 1: matplotlib.pyplot

Create a scatter plot

Let's create a scatter plot to see if there's a correlation between age and weight.

Under Paste or type your script code here, enter this code:

import matplotlib.pyplot as plt

dataset.plot(kind='scatter', x='Age', y='Weight', color='red')

plt.show()

Box 2: chart.show()

Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-pythonvisuals#create-a-scatter-plot

Box 1: main

Code must always be merged to the main branch before being published, and the main branch must be used for publishing resources.

Collaboration branch - Your Azure Repos collaboration branch that is used for publishing. By default, its master. Change this setting in case you want to publish resources from another branch. You can select existing branches or create new.

Each Git repository that's associated with a Synapse Studio has a collaboration branch. (main or master is the default collaboration branch).

Box 2: workspace_publish

A branch named dev123 will be created to support the development of a new feature.

The workspace templates must be stored in the publish branch.

Creating feature branches

Users can also create feature branches by clicking + New Branch in the branch dropdown.

By default, Synapse Studio generates the workspace templates and saves them into a branch called workspace_publish. To configure a custom publish branch, add a publish_config.json file to the root folder in the collaboration branch.

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/cicd/source-control