Tit-Bits of Power BI (PL-300)

👤 Authored by Lashmi Bai Ravindrapandian
📅 Published on Aug 15, 2022

We will learn a few DAX, Profiling Data, R visuals, Python Visuals, Incremental Refresh, Combine Files, Data Alerts, Row Level security, Azure Analysis Services and lot more topics.

Let’s get started.

1. CLOSINGBALANCEMONTH

CLOSINGBALANCEMONTH(<expression>,<dates>[,<filter>])

A scalar value that represents the expression evaluated at the last date of the month in the current context.

=CLOSINGBALANCEMONTH(SUMX(ProductInventory,ProductInventory[UnitCost]*ProductInventory[UnitsBalance]),DateTime[DateKey])

2. Combine files (binaries) in Power BI Desktop

If you have multiple files that have the same schema, combine them into a single logical table. This popular technique has been made more convenient and more expansive.

With the behavior of combine files, you can easily combine all files within a given folder if they have the same file type and structure (such as the same columns).

To start the process of combining files from the same folder, select Get data, choose File > Folder, and then select Connect.

Enter the folder path, select OK, and then choose Transform data to see the folder’s files in Power Query Editor.

To combine binary files in Power Query Editor, select Content (the first column label) and choose Home > Combine Files. Or you can just select the Combine Files icon next to Content.

The combine files transform behaves as follows:

• The combine files transform analyzes each input file to determine the correct file format to use, such as text, Excel workbook, or JSON file.
• The transform allows you to select a specific object from the first file, such as an Excel workbook, to extract.

3. Combine multiple tables into a single table

The ability to combine queries is powerful because it allows you to append or merge different tables or queries together. You can combine tables into a single table in the following circumstances:

• Too many tables exist, making it difficult to navigate an overly complicated semantic model.
• Several tables have a similar role.
• A table has only a column or two that can fit into a different table.
• You want to use several columns from different tables in a custom column.

Before you begin combining queries, you can remove extraneous columns that you don’t need for this task from your tables.

To complete this task, format each table to have only four columns with your pertinent information, and rename them so they all have the same column headers: ID, company, name, and phone.

• Left Outer – Displays all rows from the first table and only the matching rows from the second.
• Full Outer – Displays all rows from both tables.
• Inner – Displays the matched rows between the two tables.

4. Profile data in Power BI

Data anomalies are outliers within your data. Determining what those anomalies are can help you identify what the normal distribution of your data looks like and whether specific data points exist that you need to investigate further. Power Query Editor determines data anomalies by using the Column Distribution feature.

By default, Power Query examines the first 1000 rows of your data set. To change this, select the profiling status in the status bar and select Column profiling based on entire data set. ]

5. Column distribution:

It shows you the distribution of the data within the column and the counts of distinct and unique values, both of which can tell you details about the data counts.

Distinct values are all the different values in a column, including duplicates and null values, while unique values do not include duplicates or nulls.

Therefore, distinct in this table tells you the total count of how many values are present, while unique tells you how many of those values only appear once.

6. Column quality:

Column quality and Column distribution are shown in the graphs above the columns of data. 

7. Column quality shows you the percentages of data that is valid, in error, and empty. In an ideal situation, you want 100 percent of the data to be valid.

8. This row count will show how many rows that Power BI has deemed as being outliers, empty rows and strings, and the min and max, which will tell you the smallest and largest value in a column, respectively.

This distinction is particularly important in the case of numeric data because it will immediately notify you if you have a maximum value that is beyond what your business identifies as a “maximum.”

This value calls to your attention these values, which means that you can then focus your efforts when delving deeper into the data.

In the case where data was in the text column, as seen in the previous image, the minimum value is the first value and the maximum value is the last value when in alphabetical order.

9. Conduct time series analysis

To conduct a time series analysis in Power BI, you need to use a visualization type that is suitable for displaying trends and changes over time, such as a line chart, area chart, or scatter chart.

 Microsoft AppSource has an animation custom visual called Play Axis that works like a dynamic slicer and is a compelling way to display time trends and patterns in your data without user interaction. 

1. First install the Play axis visual from App source.  In the Visualizations pane, select the Get more visuals icon and then select Get more visuals. On the Power BI Visuals window that displays, search for play axis, and then select the Add button for the Play Axis (Dynamic Slicer) visual.
2. Select the page, and then select the Play Axis icon to add that visual to the page.
3. With the new visual selected, select the field (Quarter) that you want to use as the slicer in the Play Axis animation. Animation controls become available on the visual.
4. In the Animation Settings section, you can control the play functionality of the Play Axis visual, such as making the animation automatically start, continue looping, and then change the speed at which the animation occurs.
5. In the Colors section, you can change the appearance of the Play Axis visual by adjusting its overall color, or selecting the Show all option, and then changing the color of each control button.
6. The Enable Caption On section allows you to turn on/off the text that is displayed next to the visual or adjust the formatting of it.

10. Configure a semantic model scheduled refresh 

The Scheduled refresh feature in Power BI allows you to define the frequency and time slots to refresh a particular dataset.

Scheduling the refresh of your data will save you time because you don’t have to manually refresh the data. It also ensures that users can access the most up-to-date data.

You check the refresh status and notice that it was last refreshed 10 days ago, and no refresh is scheduled to take place.

To set up a refresh schedule for your semantic model, follow these steps:

1. Go to the semantic models + dataflows page.
2. Hover over the semantic model for which you want to set up the schedule and then select the Schedule refresh icon
3. On the Settings page, turn on the Scheduled refresh feature.
4. Select the Refresh frequency and ensure that the correct time zone is selected.
5. Add the time(s) that you want the refresh to occur. You can configure up to eight daily time slots, if your semantic model is on shared capacity, or 48 time slots on Power BI Premium.
6. When you have finished configuring the scheduled refresh, select Apply.

The goal is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service can’t allocate the required resources sooner.

Power BI deactivates your refresh schedule after four consecutive failures or when the service detects an unrecoverable error that requires a configuration update, such as invalid or expired credentials. It is not possible to change the consecutive failures threshold.

You should also check the refresh history occasionally to review the success or failure status of past synchronization cycles. To view the refresh history, open the semantic model’s settings page and then select Refresh history.

11. Configure incremental refresh settings

Incremental refresh should only be used on data sources and queries that support query folding.

If query folding isn’t supported, incremental refresh could lead to a bad user experience because, while it will still issue the queries for the relevant partitions, it will pull all data, potentially multiple times.

This process involves the following steps:

1. Define the filter parameters.
2. Use the parameters to apply a filter.
3. Define the incremental refresh policy.
4. Publish changes to Power BI service.

The policy you define in the Power BI Desktop will be applied to Power BI Service.

• Quicker refreshes – Only data that needs to be changed gets refreshed. Undoubtedly, the time it takes to refresh 10 days of data is much shorter than five years of data.
• More reliable refreshes – You no longer need to keep your long-running data connections open to schedule a refresh.
• Reduced resource consumption – Because you only need to refresh the smaller the amount of data, the overall consumption of memory and other resources is reduced.

Define filter parameters

To define the parameters for the incremental refresh, follow these steps:

1. Open your semantic model in Power Query Editor.
2. On the Home tab, select Manage Parameters.
3. On the Parameters window that displays, add two new parameters, RangeStart and RangeEnd, ensuring that for both parameters, the Type is set to Date/Time and the Suggested Value is set to Any value.
4. Regarding the Current Value, for the RangeStart parameter, enter the date on which you want to begin the import, and for the RangeEnd parameter, enter the date on which you want the import to end.

Apply the filter

When you have defined the new parameters, you can apply the filter by following these steps:

1. Go to the applicable Date column and then right-click that column and select Custom Filter.
2. In the Filter Rows window that displays, to avoid the double counting of rows, make sure that you keep rows where OrderDate is after or equal to the RangeStart parameter and before the RangeEnd parameter.
3. Select Close and Apply from the Power Query Editor.
4. You should now see a subset of the semantic model in Power BI Desktop.

Define the incremental refresh policy

• When you have filtered the data, you can define the incremental refresh policy for the data table, which sets up the refresh process.
• On the Incremental refresh window that displays, turn on the Incremental refresh option.
• Then, configure the refresh as required. In this example, you will define a refresh policy to store data for five full calendar years, plus data for the current year up to the current date, and incrementally refresh 10 days of data.
• Right-click the applicable table and then select Incremental refresh.
• On the Incremental refresh window that displays, turn on the Incremental refresh option. Then, configure the refresh as required. In this example, you will define a refresh policy to store data for five full calendar years, plus data for the current year up to the current date, and incrementally refresh 10 days of data.
• The first refresh operation in Power BI service will load the historical data for the last five years.

• The subsequent refresh operations are incremental, and they will refresh the data that was changed in the last 10 days up to the current date. The incremental refreshes will also remove calendar years that are older than five years prior to the current date.

Publish to Power BI service

When you have defined the incremental refresh policy in Power BI Desktop, to apply that refresh policy, you need to publish the report to Power BI service.

12. Configure data alerts

Configuring data alerts is a simple process to complete for a dashboard in Power BI. Data alerts can notify you or a user that a specific data point is above, below, or at a specific threshold that you can set.

These alerts are features that are only available on Power BI service and they’re available on such report elements such as KPI visuals, gauges, and cards.

1. After you have uploaded your reports to Power BI service and have pinned your chosen visuals to a dashboard, select the ellipsis (…) in the corner of the tile you want to set an alert for and then select Manage Alerts.
2. Select + Add Alert Rule, which will add a new alert.
3. Ensure that the Active toggle switch is turned On, name the alert, and then set the condition.
4. Next, you can choose the threshold that you want to create the alert for, which includes options for Above or Below a specific threshold.
5. Finally, select how frequently you want alerts.
6. These alerts are sent directly to your Notification Center in Power BI, and you can also configure emails.

You need the below for using the Data Alert feature in Power BI Service.

1. You can set alerts on tiles in your My Workspace.
2. You can also set alerts if someone shares a dashboard that’s in a Premium capacity.
3. If you have a Power BI Pro license, you can set alerts on tiles in any other workspace, too.
4.  Alerts can be set on visuals created from streaming datasets that you pin from a report to a dashboard.
5. Alerts can’t be set on streaming tiles created directly on the dashboard by using Add tile > Custom streaming data.
6. You can set and view data alerts in the Power BI mobile apps and in the Power BI service. They aren’t available for Power BI Desktop.
7. Alerts only work on refreshed data. When data refreshes, Power BI looks to see whether an alert is set for that data. If the data has reached an alert threshold, Power BI triggers an alert.

There are many ways to manage your alerts:

• From the dashboard tile. – Manage alerts from the particular visual tile
• From the Power BI Settings menu– Under settings – Manage alerts
• On a tile in the Power BI mobile apps.

Considerations and troubleshooting

• Alerts aren’t supported for card tiles with date or time measures.
• Alerts only work with numeric data types.
• Alerts only work on refreshed data. They don’t work on static data.
• Because alerts are only sent if your data changes, you don’t receive duplicate alerts for an unchanged value.
• Alerts only work on streaming datasets if you build a KPI, card, or gauge report visual and then pin that visual to the dashboard.
• You can create up to 250 alerts across all your models.

13. Configure row-level security with the dynamic method

Which function will tell you the username of the person who is signed in to Power BI service?

The USERPRINCIPALNAME() function will tell you which user is signed in to view a report.

Where can you test RLS by using different security roles?

You can use Power BI Desktop and Power BI service to test RLS.

14. Steps to set up dynamic RLS

1. To create security, go to the Modeling tab, Manage Roles. Create a role and name it Sales Rep. and define a filter on the Sales Rep table as below
2. [Username] = USERPRINCIPALNAME()
3. Now Save, and publish your solution to Power BI.
4. In the Power BI service, go to the security setting of the data set you just published

Static RLS means that you define the logic of security inside the Power BI file (PBIX), and for every change in the logic, you have to open the PBIX file, apply the change, save the file, and publish it again.

Dynamic RLS means that you define the logic of security inside the data model (tables, their relationships, etc). For a change in the logic, you just need to add/edit/delete records in the tables, and that’s it!

If your report is hosted in the Power BI service, their login id is the email address that they use to login to the Power BI service. If the report is hosted in the Power BI report server, then the login id is the network account they use to login to the report server.

You can query this data from Office 365, or from Active Directory in many way, such as PowerShell scripts. Even in Power BI there is an option to get data from active directory.

If you are implementing dynamic RLS, you need to have;

• A users table with the login id of all users
• A roles table with the definition of all roles
• The users table should filter the roles table
• The roles table should filter other tables in the data model
• DAX Filter

15. Data Masking

Data Masking is not row-level security. But it can uses some of the measures we use for the dynamic row-level security to reveal only part of the information that is not confidential.

Add Masked Column

This means that only reveals the name of the user for the logged in user.

Current User =
if(
SUM(‘Sales Transactions'[Sales Amount])//only show the result when there is a transaction
&&
SELECTEDVALUE(‘Sales Rep'[Email])=USERPRINCIPALNAME(),
SELECTEDVALUE(‘Sales Rep'[Name])
)

Background color =
IF(not ISBLANK([Current User]),'light green','light grey')

16. Use AI Insights in Power BI Desktop

You can access AI Insights in the Power Query Editor.

You can find its associated features and functions through the Home and Add Column tabs in Power Query Editor.

Use Text Analytics and Vision

Using the Text Analytics or Vision features requires Power BI Premium.

With Text Analytics and Vision in Power BI, you can apply different algorithms from Azure Cognitive Services to enrich your data in Power Query.

The following services are currently supported:

• Sentiment Analysis – The Score sentiment function evaluates text input and returns a sentiment score for each document, ranging from 0 (negative) to 1 (positive). Score sentiment also accepts an optional input for a Language ISO code. This function is useful for detecting positive and negative sentiment in social media, customer reviews, and discussion forums. Scores closer to 1 indicate positive sentiment. Scores closer to 0 indicate negative sentiment. 
• Key Phrase Extraction – The Key phrase extraction function evaluates unstructured text, and for each text field, returns a list of key phrases. The function requires a text field as input. Key phrase extraction works best when you give it bigger chunks of text to work on, opposite from sentiment analysis. 
• Language Detection – The Detect language function evaluates text input, and for each field, returns the language name and ISO identifier. This function is useful for data columns that collect arbitrary text, where language is unknown.
• Image Tagging.

The transformations are executed on the Power BI service and don’t require an Azure Cognitive Services subscription.

Before you use Cognitive Services in Power BI, you must enable the AI workload in the capacity settings of the admin portal.

You can turn on the AI workload in the workloads section and define the maximum amount of memory you would like this workload to consume.

The recommended memory limit is 20%. Exceeding this limit causes the query to slow down.

Publish a report with Text Analytics or Vision functions

While editing in Power Query and performing refreshes in Power BI Desktop, Text Analytics and Vision use the Premium capacity that was selected in Power Query Editor.

After Text Analytics or Vision publishes the report, they use the Premium capacity of the workspace into which it was published.

Reports with applied Text Analytics and Vision functions should be published to a workspace that is on a Premium capacity, otherwise refreshing the semantic model fails.

17. Connect to semantic models in the Power BI service from Power BI Desktop

In Power BI Desktop, you can create a data model and publish it to the Power BI service.

Then you and others can establish a live connection to the shared semantic model that’s in the Power BI service, and create many different reports from that common data model.

You can use the Power BI service live connection feature to create multiple reports in .pbix files from the same semantic model, and save them to different workspaces.

 The Power BI service live connection addresses that challenge by using common semantic models to make it easier and more consistent to create, share, and expand on reports and dashboards.

The business analyst can use the Build permission setting to make the report available for anyone in or out of the workspace to see and use. 

You can set Build permission in the workspace itself, or when you share an app from the workspace.

1. In Power BI Desktop, on the Home tab, select Get data > Power BI semantic models.
2. Or, select Get data, and on the Get Data screen, select Power Platform in the left pane, select Power BI semantic models, and then select Connect.
3. The Data hub shows the workspaces you’re a member of, and all the shared semantic models you have Build permission for in any workspace.
4. Select a semantic model, and then select Connect to establish a live connection to the selected semantic model. Power BI Desktop loads the semantic model fields and their values in real time.

Considerations and limitations

When you use the Power BI service live connection, keep a few considerations and limitations in mind.

• Only users with Build permission for a semantic model can connect to a published semantic model by using the Power BI service live connection.
• Hidden columns will become visible to users with Build permissions when they create live connections to the semantic model in Power BI Desktop.
• Free users only see datasets that are in their My Workspace and in Premium or Fabric based workspaces.
• Because this connection is live, left navigation and modeling are disabled. The behavior is similar to a SQL Server Analysis Services (SSAS) connection. However, composite models in Power BI make it possible to combine data from different sources.
• Because this connection is live, row-level security (RLS) and similar connection behaviors are enforced. This behavior is the same as when connected to SSAS.
• If the owner modifies the original shared .pbix file, the shared semantic model and report in the Power BI service are overwritten. Reports based on the semantic model aren’t overwritten, but any changes to the semantic model reflect in the report.
• Members of a workspace can’t replace the original shared report. If they try to do so, they get a prompt to rename the file and publish.
• If Members are required to publish, they need to download using the option A Copy of your report and data. Make the necessary changes then publish the report.
• If you delete the shared semantic model in the Power BI service, reports based on that semantic model will no longer work properly or display visuals. You can no longer access that semantic model from Power BI Desktop.
• Reports that share a semantic model on the Power BI service don’t support automated deployments that use the Power BI REST API.

18. Consume Power BI reports by using accessibility features

Power BI has many built-in features to help people with disabilities more easily consume and interact with Power BI reports. These tools help users get the same information from a report as those who don’t use assistive technology.

• Focus is where your mouse is on the page. Focus is usually indicated by a blue border surrounding an object.
• Canvas is the page area of your report.

This feature is available in both Power BI desktop and Power BI service.

Keyboard navigation

1. When you launch Power BI Desktop or the Power BI service, as soon as you press Tab, a tooltip appears in the upper-right hand corner.
2. The link titled Show screen reader tips directs you to this article, providing information on how to consume a report with accessibility tools. Clicking on the Skip to main content link takes you to the report canvas.
3. Pressing ? opens a dialog with the most frequently used keyboard shortcuts in Power BI. 
4. You can switch focus between the report page tabs or objects on a given report page by using Ctrl + F6. When the focus is on a loaded report page, use the Tab key to shift focus to each object on the page, which includes all textboxes, images, shapes, and charts.
5. To navigate through the first level when you navigate to a visual, press Ctrl + right arrow to enter that visual. Once you enter that visual, you can press Tab to cycle through the main areas of the visual. Those main areas you can cycle through are the data plot area, the axis categories (if applicable to the visual), and the legend if the visual has one.
6. Slicers also have accessibility functionality built in. When you select a slicer, to adjust the value of a slicer use Ctrl + Right arrow to move through the various controls within the slicer. 
7. You can move through the controls in a slicer by pressing Tab. Pressing Tab when on the eraser moves to the dropdown button.
8. When focus is on report page tabs, use the Tab or Arrow keys to move focus from one report page to the next. The screen reader reads out the title of the report page and whether it’s currently selected. To load the report page currently under focus, use the Enter key or spacebar.
9. You can press Alt + Shift + F11 to present an accessible version of the Show data window.

18. Steps to create a Date Table

Ways that you can build a common date table are:

• Source data
• DAX - You can use the Data Analysis Expression (DAX) functions CALENDARAUTO() or CALENDAR() to build your common date table.
• Power Query – In the blank space of the left Queries pane, right-click to open the following drop-down menu, where you will select New Query > Blank Query.= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))

• you notice that you have a list of dates instead of a table of dates.
• To correct this error, go to the Transform tab on the ribbon and select Convert > To Table.
• As the name suggests, this feature will convert your list into a table. You can also rename the column to DateCol.

Mark as the official date table

Your first task in marking your table as the official date table is to find the new table on the Fields pane. Right-click the name of the table and then select Mark as date table, as shown in the following figure.

20. Create a responsive slicer you can resize in Power BI

•  In Power BI Desktop and in the Power BI service, you can make tile slicers and date/range slicers responsive.
• Date/range slicers also have improved touch areas, so it’s easier to change them with a fingertip.
• You can make responsive slicers as small or as large as you want.
• They also resize automatically to fit well on reports in the Power BI service and also in the Power BI mobile apps.
• Expand the Slicer settings section, then for Style, select Tile.

1. With the slicer selected, in the Visualizations pane, select the Format tab.
2. Under Properties, expand Advanced options, and then slide Responsive to On.
3. Now you can play with it. Drag the corners to make it short, tall, wide, and narrow. If you make it small enough, it becomes just a filter icon.
4. On the View menu, select Mobile layout – Drag all the visuals you want in the phone report to the grid. When you drag the responsive slicer, make it the size you want–in this case, just a filter icon.
5. You can follow the same steps to make a time or range slicer responsive. After you set Responsive to On, you notice a few things:
6. Visuals optimize the order of input boxes depending on the size allowed on the canvas.
7. The data-element display is optimized to make the slicer as usable as possible, based on the size it’s allowed on the canvas.
8. New round handlebars on the sliders optimize touch interactions.
9. When a visual becomes too small to be useful, it becomes an icon representing the visual type in its place.
10. To interact with it, just double-tap to open it in focus mode. This saves valuable space on the report page without losing the functionality.

21. Allow contributors to update the app

Access the Allow contributors to update the app setting in one of two ways:

In the Create a workspace pane when you first create it. In the nav pane, select the arrow next to Workspaces, select More options (…) next to the workspace name > Workspace settings. The Settings pane opens.

Under Advanced, expand Security settings. Select Allow contributors to update the app for this workspace.

When these features are enabled, contributors can:

• Update app metadata such as name, icon, description, support site, and color.
• Add or remove items included in the app, like adding reports or semantic models.
• Change the visibility of the items for all the audience groups in the audience tab.

contributors can’t:

• Create or publish the app for the first time.
• Add users to the app or change who has permission to the app.
• Enable or disable automatic installation of app for app users.
• Enable or disable advance settings under Manage audience access pane. These settings include share and build permissions for the semantic models in the audience groups.
• Allow or prevent app consumers saving a copy of reports included in the app.

22. Set a workspace OneDrive

The Workspace OneDrive feature allows you to configure a Microsoft 365 Group whose SharePoint document library is available to workspace users.

With Power BI Desktop, you can use online links for Excel files stored in OneDrive for work or school to create reports and visuals. You can use a OneDrive for work or school group account or your individual OneDrive for work or school account.

Navigate to your OneDrive for work or school location using a browser. Select the ellipses (…) to open the More menu, then select Details.

Your browser interface might not look exactly like this image. There are many ways to select Open in Excel for files in your OneDrive for work or school browser interface. You can use any option that allows you to open the file in Excel.

In the More details pane that appears, select the copy icon next to Path.

1. In Power BI Desktop, select Get data > Web.
2. With the Basic option selected, paste the link into the From Web dialog.
3. If Power BI Desktop prompts you for credentials, choose either Windows for on-premises SharePoint sites or Organizational Account for Microsoft 365 or OneDrive for work or school sites.
4. To use a OneDrive for work or school file as a data source in the Power BI service, with Service Refresh enabled for that file, make sure you select OAuth2 as the Authentication method when you configure refresh settings.
5. Otherwise, you might encounter an error when you attempt to connect or to refresh, such as, Failed to update data source credentials. Selecting OAuth2 as the authentication method avoids that credentials error.

23. Premium capacity settings

On the Premium tab in the Settings pane, you can choose settings related to Premium capacities.

1. Set Premium capacity to On.
2. Choose either Premium capacity or Premium Per User
3. Choose the Default storage limit. 

24. Create Power BI visuals using R

With Power BI Desktop, you can use R to visualize your data. R is a language and environment for statistical computing and graphics.

To run R scripts in Power BI Desktop, you must separately install R on your local computer.

You can download and install R for free from many locations, including from the CRAN Repository. 

25. Connect to Analysis Services tabular data in Power BI Desktop

there are two ways you can connect to and get data from your SQL Server Analysis Services tabular models:

• Explore by using a live connection – When you connect live, no data from the tabular model is imported into Power BI Desktop. Each time you interact with a visualization, Power BI Desktop queries the tabular model and calculates the results that you see.
• When you publish a Power BI Desktop file with a live connection to a tabular model to your Power BI site, an on-premises data gateway must be installed and configured by an administrator.
• Select items and import them into Power BI Desktop –  When you connect with this option, you can select items like tables, columns, and measures in your tabular model or perspective and load them into a Power BI Desktop model. If you choose to select items and get data, you import tabular model data directly into your Power BI Desktop file, so no gateway is necessary.

1. In Power BI Desktop, on the Home ribbon, select Get Data > More > Database.
2. Select SQL Server Analysis Services database, and then select Connect.
3. In the SQL Server Analysis Services database window, enter the Server name, choose a connection mode, and then select OK.
4. This step in the Navigator window depends on the connection mode you selected: If you’re connecting live, select a tabular model or perspective. If you chose to select items and get data, select a tabular model or perspective, and then select a particular table or column to load.

26. FAQ on Live Connection

Question: If I created a live connection, can I connect to another data source in the same Power BI Desktop file?

Answer: No. You can’t explore live data and connect to another type of data source in the same file. If you’ve already imported data or connected to a different data source in a Power BI Desktop file, you need to create a new file to explore live.

Question: If I created a live connection, can I edit the model or query in Power BI Desktop?

Answer: You can create report-level measures in the Power BI Desktop, but all other query and modeling features are disabled when exploring live data.

Question: If I created a live connection, is it secure?

Answer: Yes. Your current Windows credentials are used to connect to the Analysis Services server. You can’t use basic or stored credentials in either the Power BI service or Power BI Desktop when exploring live.

Question: In Navigator, I see a model and a perspective. What’s the difference?

Answer: A perspective is a particular view of a tabular model. It might include only particular tables, columns, or measures depending on a unique data analysis need.

A tabular model always contains at least one perspective, which could include everything in the model. If you’re unsure which perspective you should select, check with your administrator.

Question: Are there any features of Analysis Services that change the way Power BI behaves?

Answer: Yes. Depending on the features your tabular model uses, the experience in Power BI Desktop might change. Some examples include:

• You may see measures in the model grouped together at the top of the Fields pane list rather than in tables alongside columns. 

27. To change the server name after initial connection

After you create a Power BI Desktop file with an explore live connection, there might be some cases where you want to switch the connection to a different server.

For example, if you created your Power BI Desktop file when connecting to a development server, and before publishing to the Power BI service, you want to switch the connection to the production server.

To change the server name:

1. Select Transform data > Data source settings from the Home tab.
2. In the SQL Server Analysis Services database window, enter the new Server name, and then select OK.

28. Error: Couldn’t load model schema:

1. The following list describes all known issues when connecting to SQL Server Analysis Services (SSAS) or Azure Analysis Services:
2. Error: Couldn’t load model schema: This error usually occurs when the user connecting to Analysis Services doesn’t have access to the database/model.

29. Create and use R visuals in Power BI

1. R visuals currently can only be created in Power BI Desktop, and then published to the Power BI service.
2. R is a language and environment for statistical computing and graphics.
3. You can download and install R for free from many locations, including from the CRAN Repository. 
4.  Visuals created with R scripts, commonly called R visuals, can present advanced data shaping and analytics such as forecasting, using the rich analytics and visualization power of R.
5. In the service, not all of the R packages are supported
6. R visuals that are created in Power BI Desktop, and then published to the Power BI service, usually behave like any other visual in the Power BI service. You can interact, filter, slice, or share them with others. 
7. R visuals can’t show tool tips, can’t be pinned to dashboards, and can’t be used to filter other visuals.
8. R visuals require a Power BI Pro or Premium Per User (PPU) license to render in reports, refresh, filter, and cross-filter.
9. Free users of Power BI can only consume tiles shared with them in Premium workspaces
10. 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.
11. If the input semantic model of an R Visual has a column that contains a string value longer than 32766 characters, that value is truncated.
12. Calculation time limitation – if an R visual calculation exceeds 60 seconds the script times out, resulting in an error.
13. R visuals are refreshed upon data updates, filtering, and highlighting. However, the image itself isn’t interactive and doesn’t support tool tips.
14. R visuals respond to highlighting other visuals, but you can’t select elements in the R visual in order to cross filter other elements
15. R visuals are currently not supported for the Time data type. Use Date/Time instead.
16. R visuals don’t display when using Publish to web.
17. R visuals are currently not supported in the DirectQuery mode of Analysis Services.
18. Install the R package showtext and all of its dependencies. You can do install it by running the following script:install.packages("showtext")

R scripts error experience

1. When an R script encounters an error, the R visual isn’t plotted and an error message is displayed. For details on the error, select See details from the R visual error on the canvas

2. the following image shows the error message that appears when an R script failed to run properly due to a missing R package in Azure.

Enable R visuals in Power BI Desktop

After you’ve installed R, Power BI Desktop enables it automatically. To verify that Power BI Desktop has enabled R in the correct location, follow these steps:

1. From the Power BI Desktop menu, select File > Options and settings > Options.
2. On the left side of the Options page, under Global, select R scripting.
3. Under R script options, verify that your local R installation is specified in Detected R home directories and that it properly reflects the local R installation you want Power BI Desktop to use. In the following image, the path to the local installation of R is C:\Program Files\R Open\R-3.4.4\.
4. When you add an R visual to a report, Power BI Desktop makes the following changes: A placeholder R visual image appears on the report canvas. The R script editor appears along the bottom of the center pane.

30. Create Power BI visuals with Python

Work through Run Python scripts in Power BI Desktop to:

• Install Python on your local machine.
• Enable Python scripting in Power BI Desktop.
• Install the pandas and Matplotlib Python libraries.
• Import the following Python script into Power BI Desktop:
• After you import the Python script, select the Python visual icon in the Power BI Desktop Visualizations pane.
• The data the Python visual uses 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 appears on the image. The input data also has a limit of 250 MB.
• If a Python visual calculation exceeds five minutes, the execution times out, which results in an error.
• Python visuals refresh upon data updates, filtering, and highlighting. The image itself isn’t interactive.
• Python visuals don’t support renaming input columns. Columns are referred to by their original names during script execution.
• Python visuals require a Power BI Pro or Premium Per User (PPU) license to render in reports, refresh, filter, and cross-filter. Users of free Power BI can consume only tiles that are shared with them in Premium workspaces.