DP-500: Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI

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

You can retrieve a column in a pandas DataFrame object by using the DataFrame object name, followed by the label of the column name in brackets.

So if the DataFrame object name is dataframe1 and the column we are trying to retrieve the 'X' column, then we retrieve the column using the statement, dataframe1['X'].

Here's a simple Python script that imports pandas and uses a data frame:

import pandas as pd data = [['Alex',10],['Bob',12],['Clarke',13]] df = pd.DataFrame(data,columns=['Name','Age'],dtype=float) print (df)

When run, this script returns:

Name Age

0 Alex 10.0

1 Bob 12.0

2 Clarke 13.0

Reference: http://www.learningaboutelectronics.com/Articles/How-to-retrieve-a-column-from-a-

Authentication for enumerating serverless SQL database resources

There are three places you'll need to set authentication to allow Microsoft Purview to enumerate your serverless SQL database resources:

The Azure Synapse workspace

The associated storage

The Azure Synapse serverless databases

The steps below will set permissions for all three.

Azure Synapse workspace

In the Azure portal, go to the Azure Synapse workspace resource.

On the left pane, select Access Control (IAM).

Select the Add button.

Set the Reader role and enter your Microsoft Purview account name, which represents its managed service identity (MSI).

Select Save to finish assigning the role

Azure Synapse Analytics serverless SQL pool catalog Purview Azure Purview managed service identity

Storage account

In the Azure portal, go to the Resource group or Subscription that the storage account associated with the Azure Synapse workspace is in.

On the left pane, select Access Control (IAM).

Select the Add button.

Set the Storage blob data reader role and enter your Microsoft Purview account name (which represents its MSI) in the Select box.

Select Save to finish assigning the role.

Azure Synapse serverless database

Go to your Azure Synapse workspace and open the Synapse Studio.

Select the Data tab on the left menu.

Select the ellipsis (...) next to one of your databases, and then start a new SQL script.

Add the Microsoft Purview account MSI (represented by the account name) on the serverless SQL databases. You do so by running the following command in your SQL script:

SQL

CREATE LOGIN [PurviewAccountName] FROM EXTERNAL PROVIDER;

Apply permissions to scan the contents of the workspace

You can set up authentication for an Azure Synapse source in either of two ways. Select your scenario below for steps to apply permissions.

Enable personalization in a report

You can enable the feature either in Power BI Desktop or the Power BI service. You can also enable it in embedded reports.

To enable the feature in Power BI Desktop, go to File > Options and settings > Options > Current file > Report settings. Make sure Personalize visuals is turned on.

Step 1: Install the on-premises data gateway (standard mode)

The personal mode is only for a single user, not to be used for a shared dataset.

Step 2: From powerbi.com, add a data source to the gateway clusters

After you install the on-premises data gateway, you can add data sources that can be used with the gateway.

Add a data source

After you fill in everything, select Create. You can now use this data source for scheduled refresh or

DirectQuery against a SQL Server that's on-premises. You see Created New data source if it succeeded.

Step 3: From powerbi.com, configure Dataset1 to use a data gateway.

Connect a dataset to a SQL Server database

In Power BI Desktop, you connected directly to your on-premises SQL Server database, but the Power

BI service requires a data gateway to act as a bridge between the cloud and your on-premises network. Follow these steps to add your on-premises SQL Server database as a data source to a gateway and then connect your dataset to this data source.

Sign in to Power BI. In the upper-right corner, select the settings gear icon and then select Settings.

On the Datasets tab, select the dataset AdventureWorksProducts, so you can connect to your onpremises

SQL Server database through a data gateway.

Expand Gateway connection and verify that at least one gateway is listed.

Under Actions, expand the toggle button to view the data sources and select the Add to gateway link.

On the Gateways management page, on the Data Source Settings tab, enter and verify the following information, and select Add.

On the Datasets tab, expand the Gateway connection section again. Select the data gateway you configured, which shows a Status of running on the machine where you installed it, and select Apply.

Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-personalmode

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-sql

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