DA-100 Power BI

Ways to create a common date table:

- source data (best if it's an option) - DAX - Power Query

Workspace roles

1. Admin 2. Member 3. Contributor 4. Viewer

What to do before running Performance Analyzer?

1. Clear Visual Cache 2. Clear Data Engine Cache

AI Capabilities of Power BI

1. Q&A Visual 2. Key Influencers Visual 3. Decomposition Tree Visual (break down a measure)

Data filter directions (cross-filter)

1. Single Cross-Filter Direction 2. Both Cross-Filter / Bi-Directional Cross Filtering

Performance Analyzer

A tool in Power BI Desktop to help you find out how each of your report elements are performing when users interact with them. For example, you can determine how long it takes for a particular visual to refresh when it is initiated by a user interaction. Performance analyzer will help you identify the elements that are contributing to your performance issues, which can be useful during troubleshooting.

Where are dataset-scheduled refreshes configured? A. Power BI service B. Power BI Desktop C. AppSource

A. Power BI service

Which function will tell you the username of the person who is signed in to Power BI service? A. USERPRINCIPALNAME() B. LOOKUPVALUE() C. USEROBJECTID()

A. USERPRINCIPALNAME()

You have several reports and dashboards in a workspace. You need to grant all organizational users read access to a dashboard and several reports. Solution: You assign all the users the Viewer role to the workspace. Does this meet the goal? A. Yes B. No

A. Yes

You create the following step by using Power Query Editor. -Table.ReplaceValue(SalesLT_Address,"1318","1319",Replacer.ReplaceText,{"AddressLine1"}) A row has a value of 21318 Lasalle Street in the AddressLine1 column. What will the value be when the step is applied? A. 1318 B. 1319 C. 21318 Lasalle Street D. 21319 Lasalle Street

D. 21319 Lasalle Street Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

What levels are filters applied?

Filters on this visual Filters on this page Filters on all pages Drillthrough -- filters that apply to a single entity in a report

When are slicers not supported?

For input fields and drilldown functions

What does SUMMARIZE do in DAX?

SUMMARIZE returns a summary table for the requested totals over a set of groups.

DAX: SUMX()

SUMX returns the sum of an expression evaluated for each row in a table. The following sample creates a measure with the sales of the top 10 sold products.= SUMX(TOPN(10, SUMMARIZE(Product, [ProductKey], "TotalSales", SUMX(RELATED(InternetSales_USD[SalesAmount_USD]), InternetSales_USD[SalesAmount_USD]) + SUMX(RELATED(ResellerSales_USD[SalesAmount_USD]), ResellerSales_USD[SalesAmount_USD]))

Using visuals to ID outliers

Scatter Charts are the best -- you can easily see isolated items away from the bulk of the data

AI Insights

allows you to connect to a collection of pretrained machine learning models that you can apply to your data to enhance your data preparation efforts.

What is the general information flow in the lineage view?

data sources > datasets/dataflows > reports > dashboards.

Artifacts

data sources, datasets and dataflows, reports, and dashboards

Grouping data

used for categories of data

Data classification steps (dashboards)

1. ensure your orgs custom data classification settings are added into Power BI (an admin does data classification) 2. Three classifications to choose from (DO NOT SHARE, ASK FOR PERMISSION, OK TO SHARE) to access data classification on a dashboard however over the ellipsis by the name of the dashboard and then select settings --> click Dashboards then use the drop down menu under data classification to choose how you want the data to be classified after you have made the selection, the dashboard will follow the default data rules or the rules that you have established under Tenant settings.

Share Permissions

Allow users to share underlying datasets and reports in an app

Power BI paginated reports is an evolved technology that was built from which original tool? A. SQL Server Analysis Services B. SQL Server Reporting Services C. Microsoft SharePoint

B. SQL Server Reporting Services

Core components of analytics

Descriptive Diagnostic Predictive Prescriptive Cognitive

Inner Join

Displays the matched rows between the two tables

If you identify the data model as the bottleneck leading to poor performance, what can you do?

If the DAX query is displaying a high duration value, it is likely that a measure is written poorly or an issue has occurred with the data model. The issue might be caused by the relationships, columns, or metadata in your model, or it could be the status of the Auto date/time option

Can you still use custom visuals from Microsoft App Source if your company does not allow them?

If they are not allowed, you can still create reports in Power BI Desktop with them, but they will not render in Power BI service.

Accessibility Features in Power BI Desktop you DO NOT need to configure

Keyboard navigation Screen-reader compatibility High contrast colors view Focus mode Show data table

Matrix Visualization

Looks similar to the table visualization; however, it allows you to select one or more elements (rows, columns, values) in the matrix to cross-highlight other visuals on the report page. In the following image, notice that a new field called Product Color was added to the columns, and the available colors are now spanning across the table, with the categories listed in rows.

Deployment Pipeline (Premium)

Manages content in dashboards, reports, and datasets between different environments in the development life cycle Advantages: 1. Increased productivity - Through this feature, you can reuse previous deployment pipelines, ensuring that efforts aren't duplicated. 2. Faster delivery of content - Report development becomes more streamlined, meaning that it takes less time to get to production. 3. Lower human intervention required - Having the ability to reuse deployment pipelines means a decreased chance of error associated with moving content from one environment to another.

What does the CALCULATE function in DAX do?

Method of creating a DAX measure that will override certain portions of the context that are being used to express the correct result. For instance, if you want to create a measure that always calculates the total sales for 2015, regardless of which year is selected in any other visual in Power BI, you would create a measure that looks like the following sample: Total Sales for 2015 = CALCULATE(SUM('Sales OrderDetails'[Total Price]), YEAR('Sales OrderDetails'[orderdate]) = 2015)

Is it possible to create a relationship between two columns if they are different DATA TYPE columns?

No, both cols in a relationship must share the same data type

How to enable cross report drillthrough?

Once you've validated your data models you enable this in power bi desktop Go to File > Options and settings > Options, then scroll down the Current File settings and select Report settings. In the Cross-report drillthrough section, select the check box for Allow visuals in this report to use drillthrough targets from other reports and then select OK. Notice that the Cross-report drillthrough targets are formatted as Page name [Report name].

Where are data alerts available?

Only Power BI service Available on report elements like KPI visuals, gauges, cards Can notify a user that a specific data point is above, below, or at a specific threhsold

Use Power BI to connect to on-prem data sources via gateways: what are the two types?

Organization mode Personal mode Before you can connect to your on-premises data source, you need to install the on-premises data gateway, and then configure it to suit your organizational needs. Usually, this task is completed by an admin in your organization. Once installed you can start the gateway and sign in w/ microsoft 365 organization account

What are the three elements of Power BI?

Power BI Desktop Power BI Service Power BI Mobile

Query Reduction

Power BI Desktop gives you the option to send fewer queries and to disable certain interactions that will result in a poor experience if the resulting queries take a long time to run. Applying these options prevents queries from continuously hitting the data source, which should improve performance.

Where can you publish and export reports?

Publish: Power BI Workspace Export: Excel

Who can use the Lineage view?

Requires Power BI Pro License and is only available for app workspaces Admin, Contributor, and Member roles To access the Lineage view, go to the workspace, and then select Lineage from the View drop-down menu on the top ribbon.

Testing stage

Select Deploy to test, which will create a new workspace. This workspace, by default, has the same name as the initial workspace but includes the [Test] suffix. In the resulting Settings window, select the correct dataset.

How to publish a paginated report

Select File > Save as and then select Power BI Service. Your report will now appear in Power BI service.

How to access page settings? What can you configure from here?

Select the white space on your report canvas to open the Format pane You can configure: Page information, Page alignment, Page size, Wallpaper, Page background, and Filter pane.

Table Visualization

Selected by default. The table is a grid that contains related data in a logical series of rows and columns. The table supports two dimensions and the data is flat, which means that duplicate values are displayed and not aggregated. It can also contain headers and a row for totals. The values section of the visualization shows the two items you selected

Add navigation buttons

Start by adding a button, as you did previously. This time, however, when you expand the Actions section in the Visualizations pane, select Page navigation as the action type, and then select the page in your report that is the Destination for the button.

Parameters and paginated reports

The most popular reason to add a parameter is to affect which data is retrieved from the data source. Consider the scenario where you are creating a report that retrieves data from a sales database. You only want sales data from between a begin date and an end date. In this case, you would create two parameters and then modify the dataset query to include those parameters in the WHERE clause of the query. Your first step in this situation is to add a parameter.

How are table relationships defined?

Through primary and foreign keys

Data Engine Cache

When a query is run, the results are cached, so the results of your analysis will be misleading. You need to clear the data cache before rerunning the visual. To clear the data cache, you can either restart Power BI Desktop or connect DAX Studio to the data model and then call Clear Cache.

Sensitivity labels

specify which data can be exported. These labels are configured externally to Power BI, and Power BI allows you to quickly use them in your reports and dashboards. These labels allow you to define and protect content, even outside of Power BI. Datasets, dataflows, reports, and dashboards can use this mechanism all users in your corporation can use this feature unless exceptions have been defined.

Cardinality & Cross Filtering for many to many relationships

you can choose to filter in a single direction or in both directions by using bi-directional cross-filtering. The ambiguity that is associated with bi-directional cross-filtering is amplified in a many-to-many relationship because multiple paths will exist between different tables. If you create a measure, calculation, or filter, unintended consequences can occur where your data is being filtered and, depending on which relationship that the Power BI engine chooses when applying the filter, the final result might be different. This situation is also true for bi-directional relationships and why you should be cautious when using them

On demand refresh

you can refresh a dataset at any time by performing an on-demand refresh. This type of refresh doesn't affect the next scheduled refresh time To perform an on-demand refresh, on the Datasets + dataflows page, hover over the dataset that you want to refresh and then select the Refresh now icon.

You have several reports and dashboards in a workspace. You need to grant all organizational users read access to a dashboard and several reports. Solution: You create an Azure Active Directory group that contains all the users. You share each report and dashboard to the group. Does this meet the goal? A. Yes B. No

B. No Instead assign all the users the viewer role to the workspace Note: 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".

Do you need to import custom visuals each time you want to use them when you are developing a new report? A. No, custom visuals are always available for selection under the Visualization pane. B. Yes, custom visuals must be imported from AppSource each time you start developing a new report. C. No, custom visuals only need to be imported once and will always remain in Power BI for future use in a new report.

B. Yes, custom visuals must be imported from AppSource each time you start developing a new report.

Can you access the Q&A feature by using buttons? A. No, to use the Q&A feature, you will need to add the Q&A visual to your reporting canvas. B. Yes, you can access the Q&A feature by selecting Q&A button type. C. Yes, you can, but you will need to add the Q&A visual to your reporting canvas and then link your button with the visual that you have added.

B. Yes, you can access the Q&A feature by selecting Q&A button type.

Which one of the following options is the best description of a workspace? A. A workspace is a feature in Power BI service that allows you to view reports only. B. A workspace is a feature of Power BI Desktop that allows you to build reports only. C. A workspace is a centralized location or repository that allows you to collaborate with colleagues and teams to create collections of reports, dashboards, and so on. D. A workspace is a feature that allows you to view and edit the data model, build visualizations, and transform the data.

C. A workspace is a centralized location or repository that allows you to collaborate with colleagues and teams to create collections of reports, dashboards, and so on.

Which of the following options is not one of the four components of the Q&A visualization? A. The question box, where users enter their question and are shown suggestions to help them complete their question. B. A pre-populated list of suggested questions C. Automatic creation of a custom tooltip D. An icon that users can select to convert the Q&A visual into a standard visual. E. An icon that users can select to open Q&A tooling, which allows designers to configure the underlying natural language engine.

C. Automatic creation of a custom tooltip Automatic creation of a custom tooltip is not one of the four components in the Q&A visualization.

Where can you test RLS by using different security roles? A. Power BI Desktop only B. Power BI service only C. Both Power BI Desktop and Power BI service

C. Both Power BI Desktop and Power BI service

Data classification (dashboards)

- raises security awareness to viewers of a dashboard - does not enforce policies b/c data protection does - feature that can be turned on and off - done by an administrator

What is the difference between Promotion and Certification when you are endorsing a dataset? A. Promotion does not need specific permissions while Certification requires permission from the dataset owner to access to the dataset. B. Promotion is for broad usage while Certification needs permission granted on the Admin Tenant settings.

A. Promotion does not need specific permissions while Certification requires permission from the dataset owner to access to the dataset.

Selections

Allow you to determine what items in the report are visible and what items are hidden. Selections are used alongside bookmarks and buttons.

Accessibility Features in Power BI Desktop you DO need to configure

Alt text Tab order Titles and labels Markers (on graphs) Themes (ensure there's a color contrast)

How to assign user roles

Go to the workspace that you've created in Power BI Service and, in the upper-left corner of the ribbon, select Access. In the resulting Access window, you can add email addresses of individual users, mail-enabled security groups, distribution lists, Microsoft 365 groups, and regular security groups, and then assign them to their specific roles. You can also change the user's assigned role at the bottom of the page and delete the user from the workspace by selecting the ellipsis (...) next to their name.

Power BI Autodetect feature (under Manage Relationships)

Help you establish relationships between columns that are named similarly. Relationships can be inactive or active. Only one active relationship can exist between tables, which is discussed in a future module.

How to analyze the entire dataset vs just the top 1000 rows (default)?

If you are reviewing a large dataset with more than 1,000 rows, and you want to analyze that whole dataset, you need to change the default option at the bottom of the window. Select Column profiling based on top 1000 rows > Column profiling based on entire data set.

How to remove conditional formatting?

If you want to remove the conditional formatting that you set, select the Values tab on the Visualizations pane and right-click the value (field) that you set the formatting for. Select Remove conditional formatting and then select the type of formatting that you want to remove, for example All or Background color

Where is the performance analyzer located?

In the View tab --> click performance analyzer To begin analysis click "Start Recording" Interact with your report as you would expect a user to and then stop the recording. The results of your interactions will display in the Performance Analyzer pane as you work. When finished select Stop Performance for each action will display in milliseconds under the duration column

What does the data source card tell you in lineage view?

Name and Type (Text/CSV etc.) and the Gateway which tells you the source of your data

Who are usage metrics available for?

Power BI Pro users with role types of Admin, Member, or Contributor

Heads up on Scheduled Refresh (timing)

Power BI starts scheduled refreshes on a best effort basis. 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.

What happens when you select the compare button

Reveals if the report differs between the Development and Test environments The difference is typically registered as added or removed objects. If you decide that the changes shouldn't be deployed to the next phase, you can choose to ignore the changes. For instance, the other developer has added a report called AdditionalOrderInfo in the Development environment, but you don't want to deploy these changes. By selecting a specific report and then selecting Deploy to test, you can effectively choose which reports that you want to move from environment to environment, as shown in the following figure.

Use a TOPN DAX Function Example

Start by creating a new measure called Top 10 Products. Then, use the TOPN function, along with the SUMX function, to calculate your top 10 products by total sales, as follows: Top 10 Products = SUMX ( TOPN ( 10, Product, Product[Product Name], ASC ), [Total Sales] ) TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]"¦]) E.g. Sort in DESC or ASC order is the last top N command

Static vs Dynamic RLS

Static Method in RLS uses a fixed value in the DAX filter while the Dynamic method uses a Dax function

Query Timeout expired:

This error indicates that you've pulled too much data according to your organization's policies. Administrators incorporate this policy to avoid slowing down a different application or suite of applications that might also be using that database.

When does the Analyze Power BI feature not work?

This feature does not work if you have non-numeric filters applied to your visual and/or if you have measure filters applied.

How does auto date/time feature impact performance?

This feature is enabled globally, Power BI Desktop automatically creates a hidden calculated table for each date column, provided that certain conditions are met. The new, hidden tables are in addition to the tables that you already have in your dataset. If your data source already defines a date dimension table, that table should be used to consistently define time within your organization, and you should disable the global Auto date/time option. Disabling this option can lower the size of your data model and reduce the refresh time.

Parameter Visibility Options

To show the parameter on the toolbar at the top of the report, select Visible. To hide the parameter so that it does not display on the toolbar, select Hidden. To hide the parameter and protect it from being modified on the report server after the report is published, select Internal. The report parameter can then only be viewed in the report definition. For this option, you must set a default value or allow the parameter to accept a null value.

Sort data

To sort a visual, start by selecting the More options... button in the upper-right corner of the visual, where you will have three sorting options: 1. Sort Descending 2. Sort Ascending 3. Sort by -- sorts by a specific column

How to use Power Query to create a Date Table

Use M 1. Select Transform Data in PBI --> this opens Power Query 2. Right click in blank space on Queries pane, to open drop down menu 3. Select New Query --> Blank Query 4. Type in this : = List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0) 5. This gives you a LIST of dates - to turn it into a table go to Transform tab on ribbon and select Convert --> To Table 6. Change column type to Date 7. Add Column --> can add year, month, etc.

Visual Cache

When you load a visual, you can't clear this visual cache without closing Power BI Desktop and opening it again. To avoid any caching in play, you need to start your analysis with a clean visual cache. To ensure that you have a clear visual cache, add a blank page to your Power BI Desktop (.pbix) file and then, with that page selected, save and close the file. Reopen the Power BI Desktop (.pbix) file that you want to analyze. It will open on the blank page.

Comment on reports

When you publish your report to Power BI web service, the consumers of your report can add comments to it. To add or view comments on a report, open the report in Power BI web service. In the upper-right corner, select Comments. In the Comments pane, you can view existing comments and write your own comments, and then select Post Comment.

How to validate roles for static RLS

You can validate that the filter is working by selecting the Modeling tab and then selecting View as Roles. In the View as roles window, select the Game role. The report now renders as if you were in that role, and you will only see the records that are included in the Game department. (Power BI Desktop)

how to optimize performance w/ DirectQuery?

You need to have control over, or access to, the source database.

What happens when you pin a report page?

all visuals on the report will be pinned to a dashboard and they are also live, meaning that any changes you make on the report will be immediately reflected on the dashboard that you have pinned the report to To pin a live page, select the ellipsis (...) on the navigation bar of the report and then select Pin a live Page. After selecting Pin live, you will be redirected to a new window where you can see your dashboard. On the dashboard, you can modify the visuals as needed. Note that all your slicers and filters still work and that the visuals have the same data as in the report. In Power BI Desktop, you can make changes to your visuals or data as needed and then deploy to the appropriate workspace file, which will update the report and simultaneously update the dashboard as well.

Drill Through

create a page in your report that focuses on a specific entity, such as a product, category, or region. You can then access this page when you drill through from the related visuals that are on other pages in your report. The information that displays on the drillthrough page will be specific to the item that you select on the visual

Card Visualization

displays a single value: a single data point. This type of visualization is ideal for visualizing important statistics that you want to track on your Power BI dashboard or report, such as total value, YTD sales, or year-over-year change. The multi-row card visualization displays one or more data points, with one data point for each row.

Top N analysis (TOPN)

returns the top N rows of a specified table. The Top N analysis is a great way to present data that might be important, such as the top 10 selling products, top 10 performers in an organization, or top 10 customers

Member

- Can complete all tasks that are associated with admins but can't add or remove users - Cannot delete the workspace - Cannot update the metadata about the workspace

Button Options/Actions

1. Back - Returns the user to the previous page of the report. This option is useful for drillthrough pages or pages that are accessed from one main page. 2. Bookmark - Presents the report page that's associated with a bookmark that is defined for the current report. 3. Drill through - Brings the user to a drillthrough page that is filtered to their selection, without using bookmarks. 4. Page navigation - Brings the user to a different page within the report, also without using bookmarks, which is an effective way to create a navigation experience for your report users. This type of button is discussed later in this module. 5. Q&A - Opens a Q&A Explorer window, where users can enter questions to quickly find the information that they are looking for and specify the type of visual that they want to see the information displayed in. This option can be useful if you want to save space in the report but still offer Q&A functionality to the user. 6. Web URL - Opens a website in a new browser window. For example, you might want to give users quick access to your organization's website or intranet from within a report.

If you identify VISUALS as the bottleneck leading to poor performance, what can you do?

1. Consider the number of visuals on the report page; less visuals means better performance. 2. Rather than using multiple visuals on the page, consider other ways to provide additional details, such as drill-through pages and report page tooltips. 3. Examine the number of fields in each visual. The upper limit for visuals is 100 fields (measures or columns) so a visual with 100+ fields will be slow

How to visualize real-time data in Power BI?

1. Create a new tile 2. Select Custom Streaming Datasets under Real time data 3. Select NEXT which redirects you to a window where you can choose an existing streaming dataset or get new ones 4. Select NEXT and enter the details for your streaming dataset then add a new straeming dataset tile

RLS Configuration Steps

1. Create a report in Microsoft Power BI Desktop. 2. Create RLS roles in Power BI Desktop by using DAX. 3. Test the roles in Power BI Desktop. 4. Deploy the report to Microsoft Power BI service. 5. Add members to the role in Power BI service. 6. Test the roles in Power BI service.

What does the decomposition tree not enable you to do? A. Conduct root cause analysis to understand a measure better. B. Conduct what-if analysis with built-in parameters. C. Automatically analyze selected dimensions to find where a measure is highest or lowest

B. Conduct what-if analysis with built-in parameters.

You need to create a visualization that compares revenue and cost over time. Which type of visualization should you use? A. stacked area chart B. donut chart C. line chart D. waterfall chart

C. Line Chart A line chart or line graph displays the evolution of one or several numeric variables. Data points are connected by straight line segments. A line chart is often used to visualize a trend in data over intervals of time "" a time series "" thus the line is often drawn chronologically.

What reserved parameters configure the start and end of where Incremental refresh should occur? A. Start and End parameters B. StartRange and EndRange C. RangeStart and RangeEnd

C. RangeStart and RangeEnd

Bookmarks

Capture the currently configured view of a report page so you can quickly return to that view later. You can use bookmarks for different reasons. For example, you can use them to keep track of your own progress when creating reports. You can also use them to build a PowerPoint-like presentation that goes through the bookmarks in order, thereby telling a story with your report.

The Products table is related to the ProductCategory table through the ProductCategoryID column.You need to ensure that you can analyze sales by product category. How should you configure the relationships from Products to ProductCategory? Cardinality: A. One to many B. One to one C. Many to many Cross Filter Direction: A. Single B. Both

Cardinality: A. One to Many Cross Filter Direction: B. Both For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional).

Cross-filtering vs cross-highlighting

Cross-filtering removes data that doesn't apply. Selecting Moderation in the doughnut chart cross-filters the line chart. The line chart now only displays data points for the Moderation segment. Cross-highlighting retains all the original data points but dims the portion that does not apply to your selection. Selecting Moderation in the doughnut chart cross-highlights the column chart. The column chart dims all the data that applies to the Convenience segment and highlights all the data that applies to theModeration segment.

Using DAX to ID outliers

Example: Outliers = CALCULATE ( [Order Qty], FILTER ( VALUES ( Product[Product Name] ), COUNTROWS ( FILTER ( Sales, [Order Qty] >= [Min Qty] ) ) > 0 ) ) When you have created a new outlier measure, you can group your products into categories by using the grouping feature, as you previously did when creating a histogram When you've added the scatter chart, populate it with the fields that are associated with your DAX formula and outlier measure.

What visuals are the most common way to display statistics about your datasets?

Histograms and bell curves In Power BI terms, you can represent a histogram with one of the bar or column chart visuals and represent a bell curve with an area chart visual A typical bar or column chart visual in Power BI relates two data points: a measure and a dimension. A histogram differs slightly from a standard bar chart in that it only visualizes a single data point.

How to design a mobile report layout

In Power BI Desktop, open the specific report page, select the View tab, and then select Mobile layout. This selection will result in showing a scrollable canvas that is shaped like a phone and a Visualizations pane that lists all visuals on the original report page. Each visual appears with its name, for easy identification, and a visibility indicator that is useful when you are working with bookmarks. The visibility indicator of a visual will change depending on the visibility status of the visual in the current state of the web report view.

How to access conditional formatting?

In this example, you will select the table visualization and then, in the Format pane, expand the Conditional formatting section. Turn on the Background color option, and then select the Advanced controls option. In the window that displays, set a condition to change background color to red for cells that have low values and green for cells with high values.

Distinct values count

The total number of different values found in a given column. A column that has a lot of repeated values in its range (distinct count is low) will have a low level of cardinality.

Unique Values Count

The total number of values that only appear once in a given column. a column that has a lot of unique values in its range (unique count is high) will have a high level of cardinality.

How to navigate to mobile view? (Power BI Service)

To see a dashboard in mobile view, select the ellipsis (...) on the home ribbon and select Mobile view This selection will take you to the following view, where you can choose which tiles that you want to see on the phone view This phone view is customizable for each person who uses the dashboard; selecting Phone view will allow you to create a new view that you can see on your phone when signing in to Power BI service.

How to get more info from the Quick Insights results?

To take a closer look at a particular insight card on the Quick Insights page, select an insight card to open. The insight screen opens in Focus mode this allows you to: 1. Filter the visualization by using the available options in the Filters panel. 2. Pin the insight card to a dashboard by selecting Pin visual. 3. Run insights on the card (scoped insights) by selecting Get insights in the upper-right corner. The scoped insights allow you to drill into your data. 4. Return to the original insights canvas by selecting Exit Focus mode in the upper-left corner.

What is the Q&A feature? (power bi service)

Tool in Power BI Desktop that allows you to ask natural language questions about data To locate the Q&A feature go to your dashboard in Power BI Service -- along the top ribbon is the Ask a question about your data search box. This re-reoutes you to a new page with 3 elements: 1. Question box 2. Pre-populated suggestion tiles: pre-populated suggestions for questions that the user can consider asking. When the user selects one of these tiles, they will be shown analysis. 3. Pin visual icon -- allow you to pin the visual onto a new or existing dashboard

You need to calculate the last day of the month in the balance sheet data to ensure that you can relate the balance sheet data to the Date table.Which type of calculation and which formula should you use? To answer, select the appropriate options in the answer area. Type of Calculation: A. A DAX Calculated Column B. A DAX Calculated Measure C. An M Custom Column Formula: A. Date.EndofMonth(#date([Year],[Month],1)) B. Date.EndofQuarter(#date([Year],[Month],1)) C. ENDOFQUARTER(DATE('BalanceSheet'[Year],BalanceSheet['Month'],1),0)

Type of Calculation: B. A DAX Calculated measure Formula: Date.EndofQuarter(#date([Year],[Month],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])

What appears in the Quick insights page ?

Up to 32 separate insight cards, and each card has a chart or graph plus a short description If you like a QI add it to your report: On the Quick Insights page, hover over the card, then select the pin icon. The visual is added to your dashboard, where you can reposition it as required

What is an effective technique to minimize model size?

Use a summary table from the data source. Where a detail table might contain every transaction, a summary table would contain one record per day, per week, or per month. It might be an average of all of the transactions per day, for instance. For example, a source sales fact table stores one row for each order line. Significant data reduction could be achieved by summarizing all sales metrics if you group by date, customer, and product, and individual transaction detail is not needed.

When should you analyze metadata?

When you load data into Power BI desktop you should analyze it so you can ID any inconsistencies w/ your dataset and normalize the data before you start building reports This improves performance b/c you will identify unnecessary columns, errors within your data, incorrect data types, the volume of data being loaded (large datasets, including transactional or historic data, will take longer to load) etc.

R and Python Visuals

You can create these in powerbi or import them for R you need to verify your R installation before you an create the R visual

What is the benefit of pinning live report pages to a dashboard?

You can ensure that you aren't using old data and the visuals on your dashboards reflect changes live

Certifying a dataset

You can only certify a dataset if you've been listed as a user in the tenant settings. The certification option will appear dimmed for other users. To certify a dataset, you would start the same way as you did to promote the dataset. This time, however, you will select the Certified option in the Endorsement settings. When you apply your change, the Certified setting will update to display a message regarding who certified the dataset and when they did so.

Drill Through Example

You can start by creating a page in your report and rename it to Details Page. On that page, you will add a visual for the entity that you want to provide the drillthrough for (a table that displays data for the Category, SubCategory, Country, Gross Sales, and Net Sales Then, from the Values section of the Visualizations pane, drag the field (Category Name) for which you want to enable drillthrough in the Drill through filters well. Ensure that the Keep all filters option is set to ON, so when you drill through from a visual, the same filters will be applied on the Details page. Power BI Desktop automatically creates a Back button visual on the page for you. This button is for navigation purposes, so your report users can return to the report page from which they came. You can reposition and resize this button on the report page or replace it with your own type of button. To use drillthrough, right-click a data point on a visual in another report page, select Drill through, and then select the focused page (Details page) to get details that are filtered to that context.

How to troubleshoot service connectivity

You only need to provide your authorization credentials to set up a data source connection. If your report fails to refresh, ensure that your data source credentials are up to date. If your data source credentials are not up to date, you'll need to take further action to investigate and resolve the issue.

Gauge Chart

a circular arc and displays a single value that measures progress toward a goal or target. The value at the end of the arc represents the defaulted maximum value, which will always be double the actual value. To create a realistic visual, you should always specify each of the values. You can accomplish this task by dropping the correct field that contains an amount into the Target value, Minimum value, and Maximum value fields on the Visualization pane. The shading in the arc represents the progress toward that target. The value inside the arc represents the progress value. Power BI spreads all possible values evenly along the arc, from the minimum (left-most value) to the maximum (right-most value).

Clustering

allows you to identify a segment (cluster) of data that is similar to each other but dissimilar to the rest of the data find groups of similar data points in a subset of your data. It analyzes your dataset to identify similarities and dissimilarities in the attribute values, and then it separates the data that has similarities into a subset of the data. These subsets of data are referred to as clusters.

Lineage

allows you to quickly refresh datasets and see the relationships between the artifacts in a workspace and their external dependencies - Simplifies the troubleshooting process because you can see the path that the data takes from source to destination and determine pain points and bottlenecks. - Allows you to manage your workspaces and observe the impact of a single change in one dataset to reports and dashboards. - Saves time by simplifying your task of identifying reports and dashboards that haven't been refreshed.

Incremental Refresh

allows you to refresh large datasets quickly and as often as you need, without having to reload historical data each time. 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.

Line and area chart visualizations

beneficial in helping you present trends over time. The basic area chart is based on the line chart, with the area between axis and line filled in. The main difference between these two chart types is that the area chart highlights the magnitude of change over time.

Promoting a dataset

can only be done if you're a Power BI Admin user or the owner of that dataset To promote a dataset, go to your workspace in Power BI service, and then open the settings page for the dataset that you want to promote. Select Endorsement setting --> Promoted --> apply When you return to your workspace, a badge in the Endorsement column for that dataset will appear, indicating that it's ready for viewing by all of your users.

Full Outer Join

displays all the rows from both tables

How to identify an outlier

first determine the logic behind what constitutes an outlier. You can use trigger points, such as calculations, around what you would consider the outlier to be. The process of identifying outliers involves segmenting your data into two groups: one group is the outlier data and the other group is not. You could use calculated columns to identify outliers, but the results would be static until you refresh the data. A better way to identify outliers is to use a visualization or DAX formula because these methods will ensure that your results are dynamic When you have identified the outliers in your data, you can then use slicers or filters to highlight those outliers. Additionally, you can add a legend to your visuals so that the outliers can be identified among the other data. You can then drill in to the outlier data for more detailed analysis.

Admin

- Add/remove other users - Publish, update, and/or share an app in a workspace - Create, edit, delete, and publish reports and content in a workspace - View and interact with reports and dashboards in a workspace - Configure data refreshes

Organization Mode (gateway)

- Allows multiple users to connect to multiple on-premises data sources and is suitable for complex scenarios. The gateway must be installed on the same server as the data source.

Personal Mode (gateway)

- Allows one user to connect to data sources. This type of gateway can be used only with Power BI and it can't be shared with other users, so it is suitable in situations where you're the only one in your organization who creates reports. You will install the gateway on your local computer, which needs to stay online for the gateway to work.

Viewer

- Cannot add or remove users - Can only view a report or dashboard in a workspace - Can read data that is stored in workspace dataflows If the workspace is backed by a Premium capacity, a non-Pro user can view content within the workspace under the Viewer role

Contributor

- Cannot add or remove users - Cannot publish, update, or edit an app in a workspace unless given this ability by admins/members - Can create, update, and publish content and reports within a workspace - Can schedule data refreshes

When would you want to build a report vs a dashboard?

- Dashboards can be created from multiple datasets or reports. - Dashboards do not have the Filter, Visualization, and Fields panes that are in Power BI Desktop, meaning that you can't add new filters and slicers, and you can't make edits. - Dashboards can only be a single page, whereas reports can be multiple pages. - You can't see the underlying dataset directly in a dashboard, while you can see the dataset in a report under the Data tab in Power BI Desktop. - Both dashboards and reports can be refreshed to show the latest data. - Dashboards allow a user to pin visuals from different reports and datasets onto a single canvas, making it simple to group what's important to the user. - Reports, on the other hand, are more focused on being able to visualize and apply transformations to a single dataset. Consider dashboards as the next step that you want to take after building your reports in Power BI Desktop.

What options do you have in Power Query to examine cols, rows, and values of raw data and make changes to them?

. 1. Unnecessary columns - Evaluates the need for each column. If one or more columns will not be used in the report and are therefore unnecessary, you should remove them by using the Remove Columns option on the Home tab. 2. Unnecessary rows - Checks the first few rows in the dataset to see if they are empty or if they contain data that you do not need in your reports; if so, it removes those rows by using the Remove Top Rows option on the Home tab. 3. Data type - Evaluates the column data types to ensure that each one is correct. If you identify a data type that is incorrect, change it by selecting the column, selecting Data Type on the Transform tab, and then selecting the correct data type from the list. 4. Query names - Examines the query (table) names in the Queries pane. Just like you did for column header names, you should change uncommon or unhelpful query names to names that are more obvious or names that the user is more familiar with. You can rename a query by right-clicking that query, selecting Rename, editing the name as required, and then pressing Enter. 5. Column details - Power Query Editor has the following three data preview options that you can use to analyze the metadata that is associated with your columns. You can find these options on the View tab, as illustrated in the following screenshot. 5a. Column quality - Determines what percentage of items in the column are valid, have errors, or are empty. If the Valid percentage is not 100, you should investigate the reason, correct the errors, and populate empty values. 5b. Column distribution - Identifies how many distinct items you have and how many are unique. This information is useful when you want to identify the cardinality of a column. You will investigate this further later in this module. 5c. Column profile - Shows more statistics for the column and a chart showing the distribution of the unique items.

What information do you need to use a KPI?

1. A unit of measurement that you want to track, for instance total sales, number of employee hires, number of loans serviced, or number of students enrolled. (Indicator) 2. A goal for the measurement so that you can compare your progress with that goal. (Target goals) 3. A time series, for instance daily, monthly, or yearly. (Trend Axis)

How to use Key Influencers Visual

1. Add the Key Influencers visual to repor from the visualization pane 2. Populate it with metrics you want to measure in the Analyze field 3. In the Explain by field add the fields you want to see that influenced your field in question You can now use the What influences... drop-down list to see what caused the data to decrease or increase.

How to configure alerts?

1. After you've pinned your chosen visuals to the dashboard select the ellipsis in the corner of the tile on the dashboard and click Manage Alerts 2. Click +Add Alert Rule, ensure that the Active toggle is turned on, name the alert 3. Set the threshold and condition 4. Set maximum notification frequency (At most every 24 hours or At most once an hour) -- alerts will be sent directly to your notification center in Power BI, but you can also configure emails to be sent if the threshold is crossed 5. Select Save & Close

Incremental Refresh Policy Process:

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. (note you do everything else in power bi desktop)

Key guidelines for creating a well-designed report

1. Draw a sketch of your report layout. This approach will help you get an idea of what it will look like before you spend considerable time physically designing it. Alternatively, you could draw multiple sketches, where you try out different ideas and then discuss these ideas with your team to help select the best layout design. 2. Focus on the most important information. Highlight key parts of your report with a bright color or summary icon so that it stands out and draws users to the most critical metrics. 3. Select the right background for the context of your report. A white background can make your report look clean and professional, whereas a black background can draw attention to colorful highlights on the report. Using images as a background can add visual interest.

Workspace benefits

1. Focused collaboration efforts. You can use workspaces to house reports and dashboards for use by multiple teams. 2. Ability to share and present reports and dashboards in a single environment. 3. Assurance that the highest level of security is maintained by controlling who can access datasets, reports, and dashboards.

How to create a workspace

1. Go to PowerBI Service 2. Select the Workspaces 3. Select the Create a Workspace 4. In the Create a workspace window, enter information in the Workspace name and Description fields and then upload a Workspace image 5. In the Advanced drop-down menu, you can create a Contact list of users who will receive notifications if issues with the workspace occur 6. After you have filled out pertinent fields on the Create a workspace window, select Save.

How to configure query caching/

1. Go to a dataset in your workspace and open its Settings page. In this example, you will enable query caching for SalesDataset. 2. Select the Datasets tab and expand the Query Caching options 3. On the Query Caching page, choose one of the available options. The default option is that query caching is turned off; however, you can also select Off, which turns off query caching for the specific dataset in question. If you select On, query caching will be turned on for this specific dataset only.

How to Publish a Power BI report?

1. Go to the Home Ribbon, from inside the Share group, click Publish 2. In the Publish to Power BI window, select your Sales Analysis Workspace 3. Click Select 4. When the file has been successfully published click Got It 5. Close power bi desktop 6. Go to PowerBI Service (internet) and review the context of your (sales analysis) workspace. The publication has added a report and a dataset. If you don't see them, press F5 to reload the browser, and then expand the workspace again. The data model has been published to become a dataset. The report—used to test your model calculations—has been added as a report

How to view metric reports

1. Go to the pertinent workspace 2. Find the report / dashboard 3. Select the ellipsis then View usage metrics report

How do DirectQuery Connections behave in Power BI?

1. If you connect to a relational source, you can select a set of tables and each one will define a query that logically returns a set of data. If you select a multidimensional source, such as SAP BW, you can only select the source. 2. No data is imported into the Power BI Desktop, only the schema is loaded. When you build a visual within Power BI Desktop, queries are sent to the underlying source to retrieve the necessary data. 3. If changes are made to the underlying data, they won't be immediately reflected in the existing visuals in Power BI due to caching. You need to carry out a refresh to see those changes 4. When you publish the report to the Power BI service, it will result in a dataset in Power BI service, the same as for import. However, no data is included with that dataset. 5. When you open an existing report in Power BI service, or build a new one, the underlying source is again queried to retrieve the necessary data. Depending on the location of the original source, you might have to configure an on-premises data gateway. 6. You can pin visuals, or entire report pages, as dashboard tiles. The tiles are automatically refreshed on a schedule, for example, every hour. You can control the frequency of this refresh to meet your requirements.

How can you generate an aggregation?

1. If you have access to the database, you could create a table with the aggregation and then import that table into Power BI Desktop. 2. If you have access to the database, you could create a view for the aggregation and then import that view into Power BI Desktop. 3. In Power BI Desktop, you can use Power Query Editor to create the aggregations step-by-step.

What are the advantages to using variables in your data model? (use variables in your DAX expressions)

1. Improved performance - Variables can make measures more efficient because they remove the need for Power BI to evaluate the same expression multiple times. You can achieve the same results in a query in about half the original processing time. 2. Improved readability - Variables have short, self-describing names and are used in place of an ambiguous, multi-worded expression. 3. Simplified debugging - You can use variables to debug a formula and test expressions, which can be helpful during troubleshooting. 4. Reduced complexity - Variables do not require the use of EARLIER or EARLIEST DAX functions, which are difficult to understand. These functions were required before variables were introduced, and were written in complex expressions that introduced new filter contexts. Now that you can use variables instead of those functions, you can write fewer complex formulas.

What are the benefits to query caching?

1. Improvement of the performance of reports, dashboards, and dashboard tiles by reducing loading time and increasing query speed; this notion is especially true for datasets that are not refreshed often and are accessed frequently. 2. It respects bookmarks and default filters, so even if you enable query caching, any bookmarks that you have created still exist. 3. Cached query results are specific to the user. 4. All security labels are followed. 5. It reduces the load on your dedicated capacity because query caching allows for usage of dedicated capacity and not on the dataset.

How to create a paginated report?

1. Need to add a visual to the design surface, Select Insert tab to see your options 2. From the Report Data window, drag fields from the dataset to the table on the design surface 3. Notice that the field is added to the lower portion of the table in the square brackets. The header will also be added. You can rename or apply formatting to the headers, such as bolding or underlining the text 4. To test the report select Run under the Home tab

Things to consider when formatting visuals on a page

1. Number of visuals --> rather than using multiple visuals you can use drillthrough pages and report page tooltips 2. Position of visuals --> most important item should be in the upper-left 3. Size of visuals 4. Interaction of visuals 5. Hierarchies in visuals --> you can set how these are presented in visuals, you can also determine the path of visuals so you have full control over what level of detail can be accessed

How to configure deployment pipelines

1. On the ribbon on the left side of the page, select Deployment pipelines, as shown in the following screenshot. 2. On the resulting page, select Create a pipeline. 3. Create a deployment pipeline called SalesPipeline. Enter the Pipeline name as SalesPipeline and enter a description, if necessary 4. Select Create, which will take you to the following screen 5. To create your pipeline, assign workspaces to each of these stages to facilitate where your reports and dashboards will be housed during each stage. 6. Select Assign a workspace to begin. 7. You will be directed to the Assign the workspace to a deployment stage window, where you can add the Tailwind Traders workspace to the Development environment. 8. If you already have Development, Test, and Production workspaces, choose one that you want to work with and then select Assign. 9. To view all objects that constitute the workspace, select Show more.

How do you configure a data source for a Power BI paginated report?

1. Open PBI Report Builder 2. Select New Report 3. Go to Report Data Window (on the left) 4. Right Click Data Sources folder --> Add Data Source 5. On the general tab, name the data source then choose the correct connection string by selecting the Build button 6. Next the connection properties screen appears (unique for each data source) 7. Select OK

How to get quick insights on your dataset?

1. Open your Power BI web service and then select the Content tab. 2. Locate your report for which you want to get quick insights, which in this case is TailwindTraders. 3. Then, select More options (...) > Quick insights.

When to use paginated reports?

1. Operational reports with tables of details and optional headers and footers 2. When you expect to print the report on paper or when you want an e-receipt, a purchase order, or an invoice

Limitations of DirectQuery Connections

1. Performance -- depends heavily on performance of underlying data source 2. Security -- identify if security rules are applicable to the data in your underlying source because, in Power BI, every user can see that data 3. Data transformation -- Compared to imported data, data that is sourced from DirectQuery has limitations when it comes to applying data transformation techniques within Power Query Editor. For example, if you connect to an OLAP source, such as SAP BW, you can't make any transformations at all; the entire external model is taken from the data source. If you want to make any transformations to the data, you will need to do this in the underlying data source. 4. Modeling -- Some of the modeling capabilities that you have with imported data aren't available, or are limited, when you use DirectQuery. 5. Reporting -- Almost all the reporting capabilities that you have with imported data are also supported for DirectQuery models. However, when the report is published in Power BI service, the Quick Insights and Q&A features are not supported. Also, the use of the Explore feature in Excel will likely result in poorer performance.

How to endorse a dataset?

1. Promotion - Promote your datasets when they're ready for broad usage. Any workspace member with Write permissions can promote your datasets. 2. Certification - Request certification for a promoted dataset from an admin user that is defined in the Dataset Certification tenant admin setting. This certification adds another layer of security for your datasets. Certification can be a highly selective process, so only the truly reliable and authoritative datasets are used across the organization.

Benefits of incremental refresh

1. Quicker refreshes - Only data that needs to be changed gets refreshed. For example, if you have five years' worth of data, and you only need to refresh the last 10 days because that is the only data that has changed, the incremental refresh will refresh only those 10 days of data. Undoubtedly, the time it takes to refresh 10 days of data is much shorter than five years of data. 2. More reliable refreshes - You no longer need to keep your long-running data connections open to schedule a refresh. 3. 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.

How to create and configure a dataset in a PBI Paginated Report

1. Right click Datasets in the Report View window and select Add Dataset 2. Ensure the correct data source is selected 3. From the window that displays, you can: - Name the query. - Choose whether to use a text command or a stored procedure. - Enter a query into the text box.

How to add a parameter to a paginated report

1. Right click Parameters --> Add Parameter 2. Report Parameter Properties dialog box opens --> General tab --> Name the parameter, select the data type (select whether you can allow blank values, null values, or multiple values), enter the prompt the user will see, set the visibility option, Select OK 3. On the Available Values tab enter options the user can choose from 4. The Default Values tab has the initial value of the parameter when the report loads, but it can be changed by the user. 5. After you have created a parameter, you can use it to interact with the report. If you return to the dataset, you can connect that parameter with the query. 6. The parameter reference starts with the at (@) symbol. Add the parameter name to the query text. Now, when the report refreshes, the data will be pulled from the data source according to the WHERE clause and the parameter value

How to create RLS roles in PBI Desktop

1. Select the Modeling tab, and then select Manage Roles. 2. On the Manage roles page, select Create. 3. On the Manage roles page, create a role for each department and then add a DAX expression to it. For instance, you can create a role called Game and then add the DAX expression [department] = "Game". Then, whenever a member of that role interacts with the report, Power BI will add that filter to their interactions, thus limiting what they see. A fixed value is used in the filter on the right side of the equal sign (in this case, "Game"). The intention is that, if you ever need to add a category, you will need to create a new role with a new value in the DAX expression.

How to pin a tile to a dashboard?

1. Select the visual 2. Select the pin visual icon 3. After you have selected the icon, a window will appear, where you can choose to pin this visual to a new or existing dashboard 4. After you have selected Pin, you will be redirected to your new dashboard, where you have just pinned a tile from your report. You can resize and move this visual around the dashboard by selecting the visual, dragging, and then dropping it

Slicer Options Go to Format --> Selection Controls

1. Single select - This option is Off by default. It ensures that only one item can be selected at a time. 2. Multi-select with CTRL - This option is On by default. It allows you to select multiple items by pressing the Ctrl key. 3. Show "Select all" - This option is Off by default. If you turn on this option, a Select all check box is added to the slicer. You might want to add this option so that you can quickly select or clear all items in the list. If you select all items, selecting an item will clear it, allowing an is-not type of filter.

What three tools can edit and configure interactions between visualizations added to reports?

1. Slicers 2. Filters -- allow you to remove data you don't need, you can apply filtering by using the Filters pane or by using a slicer 3. Sorting -- allows you to highlight important info w/o removing any data

What are the two ways of implemented row-level security in Power BI?

1. Static Method 2. Dynamic Method

You have an API that returns more than 100 columns. The following is a sample of column names. You plan to include only a subset of the returned columns.You need to remove any columns that have a suffix of sourceid.How should you complete the Power Query M code? let Source = ..., rawData = Source{[tableID="clientData"]}[Data], removeSources= __________(rawData, ________ (Table.ColumnNames(rawData), each _________ (_, "sourceid"))) in remove Sources

1. Table.RemoveColumns --> When you do "Remove Columns" Power Query uses the Table.RemoveColumns function 2. List.Select --> Get a list of columns. 3. Text.Contains

Charts in Power BI Paginated Reports

1. Two ways to add a chart to your report are: Select the Chart button, select Insert Chart, and then draw your table on the canvas OR Right-click the report canvas, select Insert, and then select Chart. 2. Next, choose the type and style of your chart. After you have selected a chart type, the chart will be added to the design surface. 3. When you select the chart, a new window appears to the right. The Chart Data screen allows you to format the chart according to the values and axis properties. Select the plus (+) sign beside each section to select the required columns.

What can you see in the report performance tab? (power bi pro only under view usage metrics report)

1. Typical opening time - How long it takes, at the fiftieth percentile, to open the report. 2. Opening time trend - How the typical opening time changes over time. This metric can tell you how the report is performing as the number of users starts to grow. 3. Daily/7-Day Performance charts - Highlight the performance for 10, 50, and 90 percent of the open-report actions every day and over a seven-day period. 4. Filters for date, so you can see how the performance changes according to the day.

How does Power BI help w/ data protection?

1. Use Microsoft sensitivity labels to label dashboards, reports, datasets, and dataflows by using the same taxonomy that is used to classify and protect files in Microsoft 365. 2. Add more protection measures such as encryption and watermarks when you are exporting the data. 3. Use Microsoft Cloud App Security to monitor and investigate activities in Power BI.

What can you see in the report usage tab? (power bi pro only under view usage metrics report)

1. Viewers per day, Unique viewers per day (which doesn't include users who returned to the same reports multiple times), and Shares per day charts 2. Total Views, Total Viewers, and Total Shares KPI cards 3. Total views and shares ranking (compares how your report is doing in comparison to other reports in the app) 4. Views by Users (details about each specific user that viewed the dashboard)

How to create a dynamic report for individual values

1. Write your SQL query 2. Use Get Data feature in PBI desktop to connect to DB 3. After you have entered your server details, in the SQL Server database window, select Advanced options. 4. Paste SQL statement, select OK 5. When the connection is made the data will be shown in a preview window, Select Edit to open the data in Power Query Editor 6. Next create a parameter: 6a. On the Home tab, select Manage parameters > New parameter. 6b. On the Parameters window, change the default parameter name to something more descriptive so that its purpose is clear. In this case, you will change the name to SalesPerson. 6c. Select Text from the Type list and then select Any value from the Suggested value list. 6d. Select OK. 7. Adjust the code in SQL to access your new parameter 7a. Right-click Query1 and then select Advanced editor. 7b. Replace the existing value in the execute statement with an ampersand (&) followed by your parameter name (SalesPerson) 7c. Make sure that no errors are shown at bottom of the window and then select Done.

Build Permissions

1. you can allow your users to connect to underlying datasets so that they can reuse and build their own reports by using the same dataset. 2. required if your users want to export the underlying data or build new content on top of the data 3. allow your users to only create a copy of the report to view in another workspace, where they can modify and delete visuals

Differences between a calculated column and a measure

A calculated column creates a value for each row in a table. Calculated column can only operate over columns that exist in the same table. For example, if the table has 1,000 rows, it will have 1,000 values in the calculated column. Calculated column values are stored in the Power BI .pbix file. Each calculated column will increase the space that is used in that file and potentially increase the refresh time. Measures are calculated on demand. Power BI calculates the correct value when the user requests it. When you previously dragged the Total Sales measure onto the report, Power BI calculated the correct total and displayed the visual. Measures do not add to the overall disk space of the Power BI .pbix file. Measures are calculated based on the filters that are used by the report user. These filters combine to create the filter context.

Data source vs Dataset

A data source is the connection information to a particular resource, like SQL Server. A dataset is the saved information of the query against the data source, not the data. The data always resides in its original location.

What is an app? What does it require?

A published, read-only window into your data for mass distribution and viewing. When ready to share apps with your users, you can publish the app. This process requires a Power BI Pro license. Consuming and viewing an app requires a Pro license or it must be backed by a Premium capacity.

How is the Admin workspace role different from other types of workspace roles? A. Admin is the only role that can add and remove users. B. Admin is the only role that can publish or update apps. C. Admin is the only role that can create, edit, or delete content in a workspace. D. Admin is the only role that can publish content to a workspace.

A. Admin is the only role that can add and remove users.

You plan to relate the balance sheet to a standard date table in Power BI in a many-to-one relationship based on the last day of the month. 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. Which DAX expression should you use to get the ending balances in the balance sheet reports? A. CALCULATE ( SUM( BalanceSheet [BalanceAmount] ), DATESQTD( 'Date'[Date] ) ) B. CALCULATE ( SUM( BalanceSheet [BalanceAmount] ), LASTDATE( 'Date'[Date] ) ) C. FIRSTNONBLANK ( 'Date' [Date] SUM( BalanceSheet[BalanceAmount] ) ) D. CALCULATE ( MAX( BalanceSheet[BalanceAmount] ), LASTDATE( 'Date' [Date] ) )

A. CALCULATE ( SUM( BalanceSheet [BalanceAmount] ), DATESQTD( 'Date'[Date] ) ) DATESQTD returns a table that contains a column of the dates for the quarter to date, in the current context.

Which two functions will help you compare dates to the previous month? A. CALCULATE and PREVIOUSMONTH B. TOTALYTD and PREVIOUS MONTH C. CALCULATE and TOTALYTD

A. CALCULATE and PREVIOUSMONTH

You need to modify the chart to meet the following requirements:✑ Identify months that have order counts above the mean.✑ Display the mean monthly order count. Which three actions should you perform in sequence? A. Create a 12 month rolling avg quick measure and add the measure to the line chart value B. From the Analytics pane, add a median line C. Select the line chart D. From the Analytics pane, add an average line E. Turn on data labels for the new line

A. Create a 12 month rolling avg quick measure and add the measure to the line chart value C. Select the line chart B. From the Analytics pane, add a median line

Where can you configure and set data alerts? A. Data alerts can be set only in Power BI service on specific visuals such as KPI cards, gauges, and cards. B. Data alerts can be set in both Power BI service and Power BI Desktop on any kind of visual. C. Data alerts can be set in Power BI service on any kind of visual. D. Data alerts can be set only in Power BI Desktop on specific kinds of visuals such as KPI cards and gauges.

A. Data alerts can be set only in Power BI service on specific visuals such as KPI cards, gauges, and cards.

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. A. Group the impressions by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function. B. Create one-to-many relationships between the tables. C. Create a calculated measure that aggregates by using the COUNTROWS function. D. Create a calculated table that contains Ad_id, Site_name, and Impression_date.

A. Group the impressions by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function. B. Create one-to-many relationships between the tables.

You import two Microsoft Excel tables named Customer and Address into Power Query. Customer contains the following columns:✑ Customer ID✑ Customer Name✑ Phone✑ Email Address✑ Address ID Address contains the following columns:✑ Address ID✑ Address Line 1✑ Address Line 2✑ City✑ State/Region✑ Country✑ Postal Code The Customer ID and Address ID columns represent unique rows. You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer. What should you do? A. Merge the Customer and Address tables. B. Transpose the Customer and Address tables. C. Group the Customer and Address tables by the Address ID column. D. Append the Customer and Address tables.

A. Merge the Customer and Address tables. There are two primary ways of combining queries: merging and appending. ✑ When you have one or more columns that you"™d like to add to another query, you merge the queries. ✑ When you have additional rows of data that you"™d like to add to an existing query, you append the query.

In the Date table, the date_id column has a format of yyyymmdd and the month column has a format of yyyymm.The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww.The sales_id column in the Sales table represents a unique transaction.The region_id column can be managed by only one sales manager. Data Concerns -You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts. Reporting Requirements -Litware identifies the following technical requirements:Executives require a visual that shows sales by region.Regional managers require a visual to analyze weekly sales and returns.Sales managers must be able to see the sales data of their respective region only.The sales managers require a visual to analyze sales performance versus sales targets.The sale department requires reports that contain the number of sales transactions.Users must be able to see the month in reports as shown in the following example: Feb 2020.The customer service department requires a visual that can be filtered by both sales month and ship month independently. You need to review the data for which there are concerns before creating the data model.What should you do in Power Query Editor? A. Transform the sales_amount column to replace negative values with 0. B. Select Column distribution. C. Select the sales_amount column and apply a number filter. D. Select Column profile, and then select the sales_amount column.

A. Transform the sales_amount column to replace negative values with 0. You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts. How to convert negative numbers into positive numb, editor and right click, select transform, and choose absolute value. That would give the positive number outcome you're looking for.

You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as numerical amount representing US dollars. You need to create a reference line to show which employees are above the median salary. Solution: You create a percentile line by using the Salary measure and set the percentile to 50%.Does this meet the goal? A. Yes B. No

A. Yes

You create a parameter named DataSourceExcel that holds the file name and location of a Microsoft Excel data source.You need to update the query to reference the parameter instead of multiple hard-coded copies of the location within each query definition. Solution: You modify the source step of the queries to use DataSourceExcel as the file path.Does this meet the goal? A. Yes B. No

A. Yes Parameterising a Data Source could be used in many different use cases. From connecting to different data sources defined in Query Parameters to load different combinations of columns.

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 WHERE clause to the SQL statement. Does this meet the goal? A. Yes B. No

A. Yes The WHERE clause has its effects before the data is imported.

Why would you want to override the default context? A. To create measures that behave according to your intentions, regardless of what the user selects. B. To create measures that do not summarize or average. C. To create new relationships.

A. You would want to override the default context to create measures that behave according to your intentions, regardless of what the user selects.

The customer service department requires a visual that can be filtered by both sales month and ship month independently. You need to create relationships to meet the reporting requirements of the customer service department. What should you create? A. an additional date table named ShipDate, a one-to-many relationship from Date[sales_date_id] to Sales[date_id], and a one-to-many relationship from ShipDate[sales_ship_date_id] to Sales[date_id] B. an additional date table named ShipDate, a many-to-many relationship from Sales[sales_date_id] to Date[date_id], and a many-to-many relationship from Sales[sales_ship_date_id] to ShipDate[date_id] C. a one-to-many relationship from Date[date_id] to Sales[sales_date_id] and another one-to-many relationship from Date[date_id] to Weekly_Returns[week_id] D. a one-to-many relationship from Sales[sales_date_id] to Date[date_id] and a one-to-many relationship from Sales[sales_ship_date_id] to Date[date_id]

A. an additional date table named ShipDate, a one-to-many relationship from Date[sales_date_id] to Sales[date_id], and a one-to-many relationship from ShipDate[sales_ship_date_id] to Sales[date_id] In Power BI Desktop, only one relationship can be active between a Fact table and Dimension table, so we need an extra table.Use one-to-many relationship to be able to filter. Incorrect Answers:C: Cannot make a relation between a date_id and a week_id.D: The one-to-many relationships between the Sales and the Date tables goes in the other direction: for each date there can be many sales or shipments.

You are developing a report page. Some users will navigate the report by using a keyboard, and some users will consume the report by using a screen reader. You need to ensure that the users can consume the content on a report page in a logical order. What should you configure in Microsoft Power BI Desktop? A. the tab order B. the layer order C. the bookmark order D. the X position

A. the tab order If you find yourself unable to navigate to an object or visual while using a keyboard, it may be because the report author has decided to hide that object from the tab order. Report authors commonly hide decorative objects from the tab order. If you find that you cannot tab through a report in a logical manner, you should contact the report author. Report authors can set the tab order for objects and visuals.

Data for the reports comes from three sources. Detailed revenue, cost, and expense data comes from an Azure SQL database. Summary balance sheet data comes from Microsoft Dynamics 365 Business Central. The balance sheet data is not related to the profit and loss results, other than they both relate dates.Monthly revenue and expense projections for the next quarter come from a Microsoft SharePoint Online list. Quarterly projections relate to the profit and loss results by using the following shared dimensions: date, business unit, department, and product category. The definitions and attributes of products, departments, and business units must be consistent across all reports.The board must be able to get the following information from the quarterly reports: Revenue trends over time Ending balances for each account A comparison of expenses versus projections by quarter Changes in long-term liabilities from the previous quarter A comparison of quarterly revenue versus the same quarter during the prior year. What is the minimum number of Power BI datasets needed to support the reports? A. two imported datasets B. a single DirectQuery dataset C. two DirectQuery datasets D. a single imported dataset

A. two imported datasets

You have a report that contains four pages. Each page contains slicers for the same four fields. Users report that when they select values in a slicer on one page, the selections are not persisted on other pages. You need to recommend a solution to ensure that users can select a value once to filter the results on all the pages. What are two possible recommendations to achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point. A. Replace the slicers with report-level filters. B. Sync the slicers across the pages. C. Create a bookmark for each slicer value. D. Replace the slicers with page-level filters. E. Replace the slicers with visual-level filters.

AB A: Add a report-level filter to filter an entire report.The visuals on the active page, and on all pages in the report, change to reflect the new filter. B: You can sync a slicer and use it on any or all pages in a report.

You have a line chart that shows the number of employees in a department over time. You need to see the total salary costs of the employees when you hover over a data point. What are two possible ways to achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point. A. Add a salary to the tooltips. B. Add a salary to the visual filters. C. Add salary to the drillthrough fields.

AB A: When a visualization is created, the default tooltip displays the data point's value and category. There are many instances when customizing the tooltip information is useful. Customizing tooltips provides additional context and information for users viewing the visual. Custom tooltips enable you to specify additional data points that display as part of the tooltip. B: Visual Filter applies to a single visual/tile on a report page. You can only see visual level filters selected visual on the report canvas. Incorrect Answers: C: With drill through in Power BI reports, you can create a page in your report that focuses on a specific entity such as a supplier, customer, or manufacturer.When your report readers use drill through, they right-click a data point in other report pages, and drill through to the focused page to get details that are filtered to that context. You can also create a button that drills through to details when they click it.

Decomposition Tree Example

Add the Decomposition Tree visual to your report by selecting the Decomposition Tree icon on the Visualization pane. Then, in the Analyze field well, add the measure or aggregate that you want to analyze. In the Explain by field well, add the dimension(s) that you want to drill down into. In this case, you want to analyze the Sales field by drilling down into a number of dimensions, such as Country, City, and Product The visual updates according to the fields that you added and displays the analysis summary result. You can select the plus (+) sign, which will present the drill-down options that you have added. You can select any of the fields in the drop-down list to drill down into the data and see how it contributed to the overall result.

What are paginated reports ?

Allow report developers to create Power BI artifacts that have tightly controlled rendering requirements. Paginated reports are ideal for creating sales invoices, receipts, purchase orders, and tabular data Paginated reports give a pixel-perfect view of the data. Pixel perfect means that you have total control of how the report renders. If you want a footer on every sales receipt that you create, a paginated report is the appropriate solution. If you want a certain customer's name to always appear in green font on a report, you can do that in a paginated report. Power BI paginated reports are descendants of SQL Server Reporting Services (SSRS)

DAX: USERELATIONSHIP

Allows you to override default behavior Used to specify a relationship to be used in a specific calculation and is done without overriding any existing relationships. It is a beneficial feature in that it allows developers to make additional calculations on inactive relationships by overriding the default active relationship between two tables in a DAX expression, as shown in the following example: Sales by Ship Date = CALCULATE(Sales[TotalPrice], USERELATIONSHIP('Calendar'[Date], Sales[ShipDate]))

How does storage mode relate to performance?

An effective technique to reduce the model size is to set the Storage Mode property for larger fact-type tables to DirectQuery.

Use a Top N filter type

An option on the Filters pane Select the field you want to analyze (e.g. Product Name) In the filters pane, expand the filter type list and select Top N Select how you show the items (e.g. Top, 10) and the value you want to filter the field by (e.g. Cost of Sales)

If you identify DAX Queries as the bottleneck leading to poor performance, what can you do?

Any DAX query longer than 120 milliseconds you should look at and try to reduce the time Use DAX studio to investigate in more detail Once you make a change, clear the data cache and re-run the Performance Analyzer process

AI Splits (in Decision Tree)

At the top of the list of dimensions that you added are two additional options that are marked with lightbulb icons --> automatically find the High value and Low Value in the data for you AI splits work by considering all available fields and determining which one to drill into to get the highest/lowest value of the measure that is being analyzed. You can use the results of these splits to find out where you should look next in the data.

Decomposition Tree

Automatically aggregates your data and lets you drill down into your dimensions so that you can view your data across multiple dimensions Because Decomposition Tree is an AI visual, you can use it for improvised exploration and conducting root cause analysis.

What does the AI splits feature do? A. AI splits work by considering all available fields and determining which one to drill into and will only return the highest or lowest value within your dataset. B. AI splits work by considering all available fields and determining which one to drill into to get the highest/lowest value of the measure that is being analyzed. C. AI splits only display the difference between highest and lowest value of the measure that is being analyzed.

B. AI splits work by considering all available fields and determining which one to drill into to get the highest/lowest value of the measure that is being analyzed.

How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown? A. Add a measure to the visual that uses the following DAX expression. Measure1 = PERCENTILEX.INC (Sales,Sales[Total Sales],0.40) B. Add a new percentile line that uses Total Sales as the measure and 40% as the percentile. C. Create a horizontal line that has a fixed value of 24,000. D. Add a measure to the visual that uses the following DAX expression. Measure1 = PERCENTILEX.EXC (Sales,Sales[Total Sales],0.40)

B. Add a new percentile line that uses Total Sales as the measure and 40% as the percentile. Incorrect Answers: A, D: There are two main percentile functions in Power BI: PERCENTILE.EXC(column, kth percentile) PERCENTILE.INC(column, kth percentile) The first parameter is the column which you want the percentile value for. The second parameter is the kth percentile where k percentage of values will fall below. Both formulas use a slightly different algorithm. The second algorithm works for any value of k between 0 and 1 (the 0th and 100th percentile). In the EXC version the data excludes both lower and upper bounds, while INC includes them.

You have a table named Customer that has the following measure.Customer Count = DISTINCTCOUNT(Customer[CustomerID])Users frequently refer to customers as subscribers.You need to ensure that the users can get a useful result for "subscriber count" by using Q&A. The solution must minimize the size of the model.What should you do? A. Set Summarize By to None for the CustomerID column. B. Add a synonym of "subscriber" to the Customer table. C. Add a synonym of "subscriberID" to the CustomerID column. D. Add a description of "subscriber count" to the Customer Count measure.

B. Add a synonym of "subscriber" to the Customer table. 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.

You have a Microsoft Power BI data model that contains three tables named Orders, Date, and City. There is a one-to-many relationship between Date andOrders and between City and Orders. The model contains two row-level security (RLS) roles named Role1 and Role2. Role1 contains the following filter.City[State Province] = "Kentucky" Role2 contains the following filter.Date[Calendar Year] = 2020 - If a user is a member of both Role1 and Role2, what data will they see in a report that uses the model? A. The user will see data for which the State Province value is Kentucky and the Calendar Year is 2020. B. The user will see data for which the State Province value is Kentucky or the Calendar Year is 2020. C. The user will see only data for which the State Province value is Kentucky. D. The user will receive an error and will not be able to see the data in the report.

B. Both When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters.

How can you analyze performance of each of your report elements? A. By analyzing your metadata B. By using performance analyzer C. By deleting unnecessary rows and columns to reduce your dataset size

B. By using performance analyzer

Your company has employees in 10 states. The company recently decided to associate each state to one of the following three regions: East, West, and North. You have a data model that contains employee information by state. The model does NOT include region information. You have a report that shows the employees by state. You need to view the employees by region as quickly as possible. What should you do? A. Create a new aggregation that summarizes by employee. B. Create a new group on the state column and set the Group type to List. C. Create a new group on the state column and set the Group type to Bin. D. Create a new aggregation that summarizes by state.

B. Create a new group on the state column and set the Group type to List. With grouping you are normally working with dimensional attributes.Here we add three new groups (East, West, and North) and add each state to the appropriate group.Incorrect Answers:C: You can set the bin size for numerical and time fields in Power BI Desktop.

Reporting Requirements -Litware identifies the following technical requirements:Executives require a visual that shows sales by region.Regional managers require a visual to analyze weekly sales and returns.Sales managers must be able to see the sales data of their respective region only.The sales managers require a visual to analyze sales performance versus sales targets.The sale department requires reports that contain the number of sales transactions.Users must be able to see the month in reports as shown in the following example: Feb 2020.The customer service department requires a visual that can be filtered by both sales month and ship month independently. You need to provide a solution to provide the sales managers with the required access.What should you include in the solution? A. Create a security role that has a table filter on the Sales_Manager table where username = UserName(). B. Create a security role that has a table filter on the Region_Manager table where sales_manager_id = UserPrincipalName(). C. Create a security role that has a table filter on the Sales_Manager table where name = UserName(). D. Create a security role that has a table filter on the Sales_Manager table where username = sales_manager_id.

B. Create a security role that has a table filter on the Region_Manager table where sales_manager_id = UserPrincipalName(). The region_id column can be managed by only one sales manager. You can use Username() or userprincipalname() in DAX with Row-Level Security. Within Power BI Desktop, username() will return a user in the format of DOMAIN\User and userprincipalname() will return a user in the format of [email protected].

Your company has training videos that are published to Microsoft Stream. You need to surface the videos directly in a Microsoft Power BI dashboard. Which type of tile should you add? A. video B. custom streaming data C. text box D. web content

B. Custom Streaming Data The only way to visualize a streaming dataset is to add a tile and use the streaming dataset as a custom streaming data source.

You need to create a relationship between the Weekly_Returns table and the Date table to meet the reporting requirements of the regional managers.What should you do? A. Add the Weekly_Returns data to the Sales table by using RELATED DAX functions. B. In the Weekly_Returns table, create a new calculated column named date_id in a format of yyyymmdd and use the calculated column to create a relationship to the Date table. C. Create a new table based on the Date table where date_id is unique, and then create a many-to-many relationship to Weekly_Return.

B. In the Weekly_Returns table, create a new calculated column named date_id in a format of yyyymmdd and use the calculated column to create a relationship to the Date table. Scenario: Regional managers require a visual to analyze weekly sales and returns.To relate the two tables we need a common column.

You build a report to help the sales team understand its performance and the drivers of sales. The team needs to have a single visualization to identify which factors affect success. Which type of visualization should you use? A. Line and clustered column chart B. Key influencers C. Q&A D. Funnel chart

B. Key Influencers The key influencers visual helps you understand the factors that drive a metric you're interested in. It analyzes your data, ranks the factors that matter, and displays them as key influencers.The key influencers visual is a great choice if you want to:✑ See which factors affect the metric being analyzed.✑ Contrast the relative importance of these factors. For example, do short-term contracts have more impact on churn than long-term contracts?

You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as numerical amount representing US dollars.You need to create a reference line to show which employees are above the median salary. Solution: You create a constant line and set the value to .5.Does this meet the goal? A. Yes B. No

B. No Instead create a percentile line by using the Salary measure and set the percentile to 50%. Note: The 50th percentile is also known as the median or middle value where 50 percent of observations fall below.

You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as numerical amount representing US dollars. You need to create a reference line to show which employees are above the median salary. Solution: You create an average line by using the Salary measure. Does this meet the goal? A. Yes B. No

B. No Instead create a percentile line by using the Salary measure and set the percentile to 50%.Note: The 50th percentile is also known as the median or middle value where 50 percent of observations fall below.

You create a parameter named DataSourceExcel that holds the file name and location of a Microsoft Excel data source.You need to update the query to reference the parameter instead of multiple hard-coded copies of the location within each query definition. Solution: You create a new query that references DataSourceExcel.Does this meet the goal? A. Yes B. No

B. No Instead modify the source step of the queries to use DataSourceExcel as the file path.

You create a parameter named DataSourceExcel that holds the file name and location of a Microsoft Excel data source.You need to update the query to reference the parameter instead of multiple hard-coded copies of the location within each query definition. Solution: In the Power Query M code, you replace references to the Excel file with DataSourceExcel. Does this meet the goal? A. Yes B. No

B. No Instead modify the source step of the queries to use DataSourceExcel as the file path. Note: Parameterising a Data Source could be used in many different use cases. From connecting to different data sources defined in Query Parameters to load different combinations of columns.

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: From Power Query Editor, you import the table and then add a filter step to the query. Does this meet the goal? A. Yes B. No

B. No The filter is applied after the data is imported. Instead add a WHERE clause to the SQL statement.

Which of the following filters are not available in Power BI reports? A. Drillthrough B. Page type C. Report level D. Page level

B. Page type

You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent during the past year. More than 10 million records are returned.You build a report analyzing the internal networks of employees based on whom they send emails to.You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size.What should you do? A. Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent. B. Remove the Subject and Body columns during the import. C. From Model view, set the Subject and Body columns to Hidden.

B. Remove the Subject and Body columns during the import. A&C don't reduce the size of the model

You have an Azure SQL database that contains sales transactions. The database is updated frequently. You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update. How should you configure the data connection? A. Add a SQL statement. B. Set Data Connectivity mode to DirectQuery. C. Set the Command timeout in minutes setting. D. Set Data Connectivity mode to Import.

B. Set Data Connectivity mode to DirectQuery With Power BI Desktop, when you connect to your data source, it's always possible to import a copy of the data into the Power BI Desktop. For some data sources, an alternative approach is available: connect directly to the data source using DirectQuery.DirectQuery: No data is imported or copied into Power BI Desktop. For relational sources, the selected tables and columns appear in the Fields list. For multi- dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, so you"™re always viewing current data. Incorrect Answers: D: Import: The selected tables and columns are imported into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop uses the imported data. To see underlying data changes since the initial import or the most recent refresh, you must refresh the data, which imports the full dataset again.

What Power BI feature can give an in-depth analysis of the distribution of data? A. The Next Level of Hierarchy feature can give in-depth analysis because it will allow you to drill down for all subcategories and is not used to analyze the distribution. B. The Analyze feature allows a user to understand why the distribution looks the way that it does. C. Only time series analysis can provide in-depth analysis on the data.

B. The Analyze feature allows a user to understand why the distribution looks the way that it does.

What visual should be used to display outliers? A. The line chart is best-suited to display outliers. B. The scatter chart is best-suited to display outliers. C. The clustered column chart is best-suited to display outliers.

B. The scatter chart is best-suited to display outliers.

Where are time series charts located? A. The filter pane is where all filters on visuals and pages are located. B. Time series charts can be imported from AppSource. C. The fields pane is where all charts are located.

B. Time series charts can be imported from AppSource.

What is the benefit to using a report tooltip? A. To give users additional information about a report visual, such as the author and date/time it was created. B. To provide additional detail that is specific to the context of the data that is being hovered over. C. To give users the ability to export data from the visual.

B. To provide additional detail that is specific to the context of the data that is being hovered over.

How do you use an inactive relationship in a single measure? A. Change the active relationship in the Modeling tab. B. Use the USERELATIONSHIP function. C. Delete one relationship.

B. Use the USERELATIONSHIP function This function will change which relationship is used in a single measure.

Can you use bookmarks to create a slide show in Power BI? A. No, you cannot, because bookmarks are not dynamic. B. Yes, you can, by adding buttons as navigation to go between saved bookmarks. C. No, you will require a specific visual to achieve this task.

B. Yes, you can, by adding buttons as navigation to go between saved bookmarks.

Binning data

Binning is similar to grouping, but it is used for grouping continuous fields, such as numbers and dates.

Where do you create a Power BI Paginated Report?

By using the Power BI Report Builder. This is a feature of Power BI Premium. (not created in Power BI Desktop)

You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in shared capacity of powerbi.com.The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The dataset is scheduled to refresh twice a day at 08:00 and 17:00.The report is a single page that contains 15 AppSource visuals and 10 default visuals.Users say that the report is slow to load the visuals when they access and interact with the report.You need to recommend a solution to improve the performance of the report.What should you recommend? A. Change any DAX measures to use iterator functions. B. Replace the default visuals with AppSource visuals. C. Change the imported dataset to DirectQuery. D. Remove unused columns from tables in the data model.

C. Change the imported dataset to DirectQuery. There are a few benefits to using DirectQuery:✑ DirectQuery lets you build visualizations over very large datasets, where it would otherwise be unfeasible to first import all the data with pre-aggregation.✑ Underlying data changes can require a refresh of data. For some reports, the need to display current data can require large data transfers, making reimporting data unfeasible. By contrast, DirectQuery reports always use current data.The 1-GB dataset limitation doesn't apply to DirectQuery.

You are developing a sales report that will have multiple pages. Each page will answer a different business question. You plan to have a menu page that will show all the business questions. You need to ensure that users can click each business question and be directed to the page where the question is answered. The solution must ensure that the menu page will work when deployed to any workspace. What should you include on the menu page? A. Create a text box for each business question and insert a link. B. Create a button for each business question and set the action type to Bookmark. C. Create a Power Apps visual that contains a drop-down list. The drop-down list will contain the business questions.

C. Create a Power Apps visual that contains a drop-down list. The drop-down list will contain the business questions. Power BI enables data insights and better decision-making, while Power Apps enables everyone to build and use apps that connect to business data. Using the Power Apps visual, you can pass context-aware data to a canvas app, which updates in real time as you make changes to your report. Now, your app users can derive business insights and take actions from right within their Power BI reports and dashboards.

You create a dashboard by using the Microsoft Power BI Service. The dashboard contains a card visual that shows total sales from the current year.You grant users access to the dashboard by using the Viewer role on the workspace.A user wants to receive daily notifications of the number shown on the card visual. You need to automate the notifications.What should you do? A. Create a data alert. B. Share the dashboard to the user. C. Create a subscription. D. Tag the user in a comment.

C. Create a subscription. You can subscribe yourself and your colleagues to the report pages, dashboards, and paginated reports that matter most to you. Power BI e-mail subscriptions allow you to: ✑ Decide how often you want to receive the emails: daily, weekly, hourly, monthly, or once a day after the initial data refresh. ✑ Choose the time you want to receive the email, if you choose daily, weekly, hourly, or monthly. Note: Email subscriptions don't support most custom visuals. The one exception is those custom visuals that have been certified.Email subscriptions don't support R-powered custom visuals at this time.

You have a Microsoft SharePoint Online site that contain several document libraries.One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the manufacturing reports have the same data structure. You need to use Power BI Desktop to load only the manufacturing reports to a table for analysis. What should you do? A. Get data from a SharePoint Online folder, enter the site URL, and then select Combine & Load. B. Get data from a SharePoint Online list and enter the site URL. Edit the query and filter by the path to the manufacturing reports library. C. Get data from a SharePoint Online folder and enter the site URL. Edit the query and filter by the path to the manufacturing reports library. D. Get data from a SharePoint Online list, enter the site URL, and then select Combine & Load.

C. Get data from a SharePoint Online folder and enter the site URL. Edit the query and filter by the path to the manufacturing reports library.

You have a data model that contains many complex DAX expressions. The expressions contain frequent references to the RELATED and RELATEDTABLE functions. You need to recommend a solution to minimize the use of the RELATED and RELATEDTABLE functions. What should you recommend? A. Split the model into multiple models. B. Hide unused columns in the model. C. Merge tables by using Power Query. D. Transpose.

C. Merge tables by using Power Query. Combining data means connecting to two or more data sources, shaping them as needed, then consolidating them into a useful query.When you have one or more columns that you"™d like to add to another query, you merge the queries.Note: The RELATEDTABLE function is a shortcut for CALCULATETABLE function with no logical expression.CALCULATETABLE evaluates a table expression in a modified filter context and returns A table of values.

Power BI paginated reports are created by using which tool? A. Power BI Desktop B. Power BI service C. Power BI Report Builder

C. Power BI Report Builder`

You have four sales regions. Each region has multiple sales managers.You implement row-level security (RLS) in a data model. You assign the relevant distribution lists to each role. You have sales reports that enable analysis by region. The sales managers can view the sales records of their region. The sales managers are prevented from viewing records from other regions. A sales manager changes to a different region. You need to ensure that the sales manager can see the correct sales data. What should you do? A. Change the Microsoft Power BI license type of the sales manager. B. From Microsoft Power BI Desktop, edit the Row-Level Security setting for the reports. C. Request that the sales manager be added to the correct Azure Active Directory group. D. Manage the permissions of the underlying dataset.

C. Request that the sales manager be added to the correct Azure Active Directory group. Using AD Security Groups, you no longer need to maintain a long list of users.All that you will need to do is to put in the AD Security group with the required permissions and Power BI will do the REST! This means a small and simple security file with the permissions and AD Security group. Note: Configure role mappings -Once published to Power BI, you must map members to dataset roles.Members can be user accounts or security groups. Whenever possible, we recommend you map security groups to dataset roles. It involves managing security group memberships in Azure Active Directory. Possibly, it delegates the task to your network administrators.

Which of the following selections are not features of the Q&A visual? A. Adding new synonyms to fields through Q&A tooling. B. Converting a Q&A answer into a visual inside your report. C. Searching for help topics about Power BI.

C. Searching for help topics about Power BI.

You have a report that contains a bar chart and a column chart. The bar chart shows customer count by customer segment. The column chart shows sales by month. You need to ensure that when a segment is selected in the bar chart, you see which portion of the total sales for the month belongs to the customer segment. How should the visual interactions be set on the column chart when the bar chart is selected? A. no impact B. highlight C. filter

C. filter Filters remove all but the data you want to focus on. Highlighting isn't filtering. It doesn't remove data, but instead highlights a subset of the visible data; the data that isn't highlighted remains visible but dimmed.

You import the tables.Which relationship should you use to link the tables? A. many-to-many between Customer and Transaction B. one-to-many from Transaction to Customer C. one-to-many from Customer to Transaction D. one-to-one between Customer and Transaction

C. one-to-many from Customer to Transaction Each customer can have many transactions.

DAX: CALCULATE()

CALCULATE evaluates an expression in a modified filter context.

Calculated Tables

Calculated tables are defined by using a DAX formula which returns a table. It is important to understand that calculated tables increase the size of the data model because they materialize and store values. They are recomputed whenever formula dependencies are refreshed, as will be the case in this data model when new (future) date values are loaded into tables. Unlike Power Query-sourced tables, calculated tables cannot be used to load data from external data sources. They can only transform data based on what has already been loaded into the data model.

How are table relationships related to performance?

Check that you've established the correct relationships Check that relationship cardinality properties are right. For example, a one-side column that contains unique values might be incorrectly configured as a many-side column.

Combo charts

Combination of a column chart and a line chart that can have one or two Y axes. The combination of the two charts into one lets you: 1. Compare multiple measures with different value ranges. 2. Illustrate the correlation between two measures in one visual. 3. Identify whether one measure meets the target that is defined by another measure. 4. Conserve space on your report page.

Buttons

Create a more interactive experience for the report users. With the addition of buttons that have assigned actions, your report behaves similar to an app, where users can hover, select, and interact more with the content.

How to help users interpret a paginated report?

Creating good headers and footers Documenting why this report was created. Adding a report implementation date and time. Localize data formats to the appropriate target user. Consider how the user wants to see the report (sent via email printable format, in a web browser) Focusing on only getting pertinent data

You have a prospective customer list that contains 1,500 rows of data. The list contains the following fields: ✑ First name ✑ Last name ✑ Email address ✑ State/Region ✑ Phone number You import the list into Power Query Editor. You need to ensure that the list contains records for each State/Region to which you want to target a marketing campaign. Which two actions should you perform? Each correct answer presents part of the solution. A. Open the Advanced Editor. B. Select Column quality. C. Enable Column profiling based on entire dataset. D. Select Column distribution. E. Select Column profile.

D&E

You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each compliant occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy. What should you do? A. Create a column by example that starts with 2018-12-31. B. Apply the Parse function from the Date transformations options to the Logged column. C. Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date. D. Apply a transform to extract the first 11 characters of the Logged column.

D. Apply a transform to extract the first 11 characters of the Logged column. With Power Query you can Split Date and Time into Separate Columns by using a transform.

You open a query in Power Query Editor.You need to identify the percentage of empty values in each column as quickly as possible.Which Data Preview option should you select? A. Show whitespace B. Column profile C. Column distribution D. Column quality

D. Column quality In this section, we can easily see valid, Error and Empty percentage of data values associated with the Selected table.

You have sales data in a star schema that contains four tables named Sales, Customer, Date, and Product. The Sales table contains purchase and ship dates.Most often, you will use the purchase date to analyze the data, but you will analyze the data by both dates independently and together.You need to design an imported dataset to support the analysis. The solution must minimize the model size and the number of queries against the data source.Which data modeling design should you use? A. Use the Auto Date/Time functionality in Microsoft Power BI and do NOT import the Date table. B. Duplicate the Date query in Power Query and use active relationships between both Date tables. C. On the Date table, use a reference query in Power Query and create active relationships between Sales and both Date tables in the modeling view. D. Create an active relationship between Sales and Date for the purchase date and an inactive relationship for the ship date.

D. Create an active relationship between Sales and Date for the purchase date and an inactive relationship for the ship date. Only one relationship can be active. Note: If you query two or more tables at the same time, when the data is loaded, Power BI Desktop attempts to find and create relationships for you. The relationship options Cardinality, Cross filter direction, and Make this relationship active are automatically set.

In the Date table, the date_id column has a format of yyyymmdd and the month column has a format of yyyymm.The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. Reporting Reqs: Month must be shown as such Feb 2020 You need to create a calculated column to display the month based on the reporting requirements. Which DAX expression should you use? A. FORMAT('Date'[date], "MMM YYYY") B. FORMAT('Date' [date], "M YY") C. FORMAT('Date'[date_id], "MMM") & "" & FORMAT('Date'[year], "#") D. FORMAT('Date' [date_id], "MMM YYYY")

D. FORMAT('Date' [date_id], "MMM YYYY")

What feature in Power BI service can you use to troubleshoot the flow of data from its source to its destination? A. Usage Metrics report B. Query Caching C. Quick Insights D. Lineage view

D. Lineage view

You have the following three versions of an Azure SQL database: ✑ Test✑ Production✑ Development You have a dataset that uses the development database as a data source. You need to configure the dataset so that you can easily change the data source between the development, test, and production database servers from powerbi.com. Which should you do? A. Create a JSON file that contains the database server names. Import the JSON file to the dataset. B. Create a parameter and update the queries to use the parameter. C. Create a query for each database server and hide the development tables. D. Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function.

D. Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function With privacy level settings, you can specify an isolation level that defines the degree that one data source must be isolated from other data sources.An Organizational data source limits the visibility of a data source to a trusted group of people. An Organizational data source is isolated from all Public data sources, but is visible to other Organizational data sources.

You have a large dataset that contains more than 1 million rows. The table has a datetime column named Date. You need to reduce the size of the data model. What should you do? A. Round the hour of the Date column to startOfHour. B. Change the data type of the Date column to Text. C. Trim the Date column. D. Split the Date column into two columns, one that contains only the time and another that contains only the date.

D. Split the Date column into two columns, one that contains only the time and another that contains only the date. We have to separate date & time tables. Also, we don"™t need to put the time into the date table, because the time is repeated every day.Split your DateTime column into a separate date & time columns in fact table, so that you can join the date to the date table & the time to the time table. The time need to be converted to the nearest round minute or second so that every time in your data corresponds to a row in your time table.

You use an R visual to produce a map of 500,000 customers. You include the values of CustomerID, Latitude, and Longitude in the fields sent to the visual. Each customer ID is unique. In powerbi.com, when users load the visual, they only see some of the customers. What is the cause of the issue? A. The visual was built by using a different version of R. B. The data comes from a Microsoft SQL Server source. C. The data is deduplicated. D. Too many records were sent to the visual.

D. Too many records were sent to the visual 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 have a table that contains sales data and approximately 1,000 rows.You need to identify outliers in the table.Which type of visualization should you use? A. donut chart B. pie chart C. area chart D. scatter plot

D. scatter plot Outliers are those data points that lie outside the overall pattern of distribution & the easiest way to detect outliers is though graphs. Box plots, Scatter plots can help detect them easily.

Statistical Functions in Power BI

DAX Functions can be used for quick stats based on your data you can access the quick functions by right-clicking the values field in the Visualizations pane To avoid performance issues, it's better to create the stat measure yourself using DAX functions

Power BI Dashboards

Dashboards allow report consumers to create a single artifact of directed data that is personalized just for them. Dashboards can be comprised of pinned visuals that are taken from different reports. Where a Power BI report uses data from a single dataset, a Power BI dashboard can contain visuals from different datasets. Power BI dashboards is a feature that is only included in Power BI service. You can also view dashboards on mobile devices, though you can't build them there.

Where is data that comes from a streaming model stored?

Data is NOT stored in a Power BI data model; instead, it is stored in a temporary cache. Consequently, you cannot model the data with this type of dataset. The only way to visualize the data from a streaming data source is to create a tile directly on a dashboard and use a custom streaming data source. These tiles are optimized for displaying the data quickly and, because no database exists to pull the data from, these types of tiles have low latency and are best suited for data that doesn't need additional transformations, such as temperature or humidity

Cardinality

Describes the uniqueness of the values in a column. Cardinality is also used in the context of the relationships between two tables, where it describes the direction of the relationship.

How to create a report optimized for mobile

Design a mobile layout view, where you can drag and drop certain visuals onto a phone emulator canvas. Use visuals and slicers that are suitable for use on small, mobile screens. To publish a mobile-optimized version of your report, you can publish the main report as you did previously. The web and mobile versions are published at the same time.

Report Accessibility

Designing a report that offers an accessible experience will benefit all report users because it ensures that your report has an effective design and uses consistent formatting and color scheme or theme. Generally, when you are using Power BI with a screen reader, we recommend that you turn scan mode or browse mode off.

Development Environments

Development - The location in which dashboard developers or data modelers can build new content with other developers. This stage is first in the deployment pipeline. Test - Where a small group of users and user acceptance testers can see and review new reports, provide feedback, and test the reports with larger datasets for bugs and data inconsistencies before it goes into production. Production - Where an expansive user audience can use tested reports that are reliable and accurate. This stage is the final one of the deployment pipeline.

How can you enable/disable the auto/date time feature?

Go to File > Options and settings > Options, and then select either the Global or Current File page. On either page, select Data Load and then, in the Time Intelligence section, select or clear the check box as required.

Dashboard theme (Power BI Service)

Go to a dashboard, select the ellipsis (...), and then select Dashboard theme. Options available to choose from or custom where you can create your own. You can upload your own JSON theme or download the current theme too

How should you distribute the reports to the board? Grant access by: A. Sharing Individual Reports B. Using a Workspace Membership C. Using an app Grant access to: A. A dynamic distribution list B. A mail enabled security group C. Individual user emails

Grant access by: B. Using a workspace membership 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. Grant access to: B. A mail-enabled security group 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.

How to navigate to mobile view? (Power BI Desktop)

In Power BI Desktop, select View, then select Mobile Layout After you have published to Power BI service, you can view your visuals on a mobile device.

What information for each visual does the performance analyzer log contain?

Information for each visual shows how much time it took across three categories: 1. DAX query - The time it took for the visual to send the query, along with the time it took Analysis Services to return the results. 2. Visual display - The time it took for the visual to render on the screen, including the time required to retrieve web images or geocoding. 3. Other - The time it took the visual to prepare queries, wait for other visuals to complete, or perform other background processing tasks. If this category displays a long duration, the only real way to reduce this duration is to optimize DAX queries for other visuals, or reduce the number of visuals in the report.

How are columns related to performance?

It is best practice to not import columns of data that you do not need. To avoid deleting columns in Power Query Editor, you should try to deal with them at the source when loading data into Power BI Desktop. If it is impossible to remove redundant columns from the source query or the data has already been imported in its raw state, you can use Power Query Editor to examine each column. For example, suppose that you have an ID column with thousands of unique rows. You know that you won't use this particular column in a relationship, so it will not be used in a report. Therefore, you should consider this column as unnecessary and admit that it is wasting space in your data model. When you remove an unnecessary column, you will reduce the size of the data model which, in turn, results in a smaller file size and faster refresh time. Also, because the dataset contains only relevant data, the overall report performance will be improved.

how does cardinality relate to performance?

Lower cardinality leads to more optimized performance, so you might need to reduce the number of high cardinally columns in your dataset. Reducing the data that is loaded into your model will improve the relationship cardinality of the report.

What does the dataset card tell you in lineage view?

Name & when the dataset was last refreshed, and you can refresh the dataset by selecting the arrow icon on the lower-left corner of the card Additionally, if a dataset or dataflow belongs to a different workspace (in this case, the Tailwind workspace), it will be indicated on the card By double-clicking on any card, you can view the metadata, such as the sensitivity, by whom it was configured, the last refresh date, and the names and count of tables within this dataset You can also view the impact of this dataset across workspaces. By selecting the overlapping window icon on the lower-right corner of a dataset card, you can determine the impact analysis of the dataset. Impact analysis Window

When can Power BI Paginated Reports be rendered in the Power BI Service?

Paginated reports can only be rendered in the Power BI service when they are stored in a workspace on dedicated capacity, and when that capacity has the paginated reports workload enabled.

You need to grant access to the business unit analysts. What should you configure? Permissions required in powerbi.com: A. Access permissions to an app B. Member role to the workspace C. Viewer role to the workspace Permissions for the profit and loss dataset: A. Build B. Delete C. Reshare

Permissions required in powerbi.com: C. Viewer role in the workspace (Can view an item, Can create a report in another workspace based on a dataset in this workspace, Can copy a report) Permissions for the profit and loss dataset: A. 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.

How does getting data for a Power BI paginated report differ from using Power BI Desktop?

Power BI paginated reports do not use Power Query when connecting to data sources. Getting data in a Power BI paginated report does not involve data cleaning steps. In fact, data is not stored in a Power BI paginated report dataset. When data is refreshed on the report, it is retrieved in an unaltered form from the data source, according to the query that was used to retrieve. Can be collected from multiple sources (Excel, Oracle, SQL Server, a dataset in Power BI Service etc.) --> data collected from different sources CANNOT be merged into a single data model

What does the report / dashboard cards tell you in lineage view?

Selecting a card will bring up a window in which you can view the metadata about the report or dashboard. In this window, you can also go directly to the report or dashboard. You can also enable or disable whether you want to include this report or dashboard within the app you can select to analyze the report in Microsoft Excel, delete a report, create Quick Insights, save a copy directly to your local drive, and more.

You are modeling data in a table named SalesDetail by using Microsoft Power BI. You need to provide end users with access to the summary statistics about the SalesDetail data. The users require insights on the completeness of the data and the value distributions. Which three actions should you perform in sequence? A. Specify the following query then close and apply -Table.Distinct(#"SalesDetail") B. Create a visual for the query table C. Create a parameter that uses a query for the suggested values D. Create a query that uses Common Data Service as a data source E. Specify the following query then close and apply -Table.Profile(#"SalesDetail") F. Create a Blank Query as a Data Source

Step 1: Create a blank query as a data source Create a parameter that use a query for suggested values. Step 2: Specify the following query then close and apply -Table.Profile(#"SalesDetail") Note: The Table.Profile() function takes a value of type table and returns a table that displays, for each column in the original table, the minimum, maximum, average, standard deviation, count of values, count of null values and count of distinct values. Step 3: Create a visual for the query table

You are building a dataset from a JSON file that contains an array of documents.You need to import attributes as columns from all the documents in the JSON file. The solution must ensure that date attributes can be used as date hierarchies inMicrosoft Power BI reports. 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 and Place: A. Expand Columns B. Expand the records C. Add columns that use data type conversions D. Set the data types E. Convert the list to a table

Step 1: Expand the records. First Open Power BI desktop and navigate to Power Query, import the JSON file, then load the data, click on the record to expand it and to see the record and list. Step 2: Add columns that use data type conversions. Step 3: Convert the list to a table

Set the navigation destination with conditional formatting

This saves space in your report Rather than using multiple nav buttons, you can use one to navigate to different pages based on the users selection To use conditional formatting to set the navigation, start by creating a single-column table that contains the names of the navigation destinations. In the table, ensure that the entered values match your report page names. When you load the table, add it to the report page as a single-select slicer. Next, add a page navigation button. In the Actions section, ensure that None is set as the Destination, right-click the destination, and then select Conditional formatting. On the Destination window, select the name of the column that you created. Notice that, based on the user's selection, the button can navigate to different pages. Configure the conditional formatting to complete your navigation design.

Where do you edit interactions?

Under the format tab The Edit interactions button turns grey to show that it's enabled, and Filter, Highlight, and/or None icons are added to the other visualizations on the report page. When you hover over an icon, a grey box displays over the related visual. The icon in bold is the one that is being applied

How to introduce a variable in DAX?

Use "VAR" Example: Sales YoY Growth = VAR SalesPriorYear = CALCULATE ( [Sales], PARALLELPERIOD ( 'Date'[Date], -12, MONTH ) ) VAR SalesVariance = DIVIDE ( ( [Sales] - SalesPriorYear ), SalesPriorYear ) RETURN SalesVariance

How to conduct a time series analysis in Power BI

Use a visualization type that is suitable for displaying trends and changes over time, such as a line chart, area chart, or scatter chart. You can also import a time series custom visual into Power BI Desktop from Microsoft AppSource. 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

Quick Insights

Uses machine learning algorithms to go over your entire dataset and produce insights (results) for you quickly. This feature is a great way to build dashboards when you don't know where to start.

How to create and configure an app

When you have added your content to the app workspace, you can create the app. 1. Go to your workspace, and on the ribbon, select + New 2. In this ribbon, you can choose to create a new report, dataset, streaming dataset, or dataflow, to name a few. Selecting any one of these options will generate a window where you can enter the name of the app and select the source of the report 3. You can also configure your app and turn on the option to include the report or dashboard in the app when you publish. If you don't want to include a report or dashboard in the app then turn this option off 4. Once ready to publish your app w/ its collection of reports, dashboards, and datasets, return to the workspace and click Create App --> an App Name and Description are required, can also configure Navigation and Permissions tabs here 6. Publish App (can go back and edit later then you just need to hit the Update app button later)

How to use Play Axis visual (for time series)

With the visual selected, select the field (e.g. Quarter) that you want to use as the slicer in the Play Axis animation 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.

Can you modify the fields that show up in a slicer?

Yes -- you can do this in the Fields pane, so select the fields you want to include or drag them into the slicer visualization

Can you edit the vocab of the Q&A visual?

Yes, you can add synonyms etc. and teach Power BI what you mean e.g. user types in country, but there is no result, you only have region in your dataset so you need to add country to the thesaurus by going to the settings icon and opening the Q&A setup window then select the Teach Q&A option Enter your question again, and then select the Submit button. In the Define the terms Q&A didn't understand section that displays, enter your alternative term or synonym. In this case, you will enter region. If this result is correct, select Save.

What are the Query Reduction options?

You can access the query reduction settings by selecting **File > Options and settings > Options, then scrolling down and selecting the Query reduction option. 1. Reduce number of queries sent by - By default, every visual interacts with every other visual. Selecting this check box disables that default interaction. You can then optionally choose which visuals interact with each other by using the Edit interactions feature. 2. Slicers - By default, the Instantly apply slicer changes option is selected. To force the report users to manually apply slicer changes, select the Add an apply button to each slicer to apply changes when you're ready option. 3. Filters - By default, the Instantly apply basic filter changes option is selected. To force the report users to manually apply filter changes, select one of the alternative options: 3a. Add an apply button to all basic filters to apply changes when you're ready 3b. Add a single apply button to the filter pane to apply changes at once (preview)

Use the Q&A visual to find the top N

You can ask questions like "What are my top 10 products by sales?" Power BI will automatically display those results for you.

Manage Aggregations

You can open the Manage Aggregations window from any view in Power BI Desktop. In the Fields pane, right-click the table and then select Manage aggregations. For each aggregation column, you can select an option from the Summarization drop-down list and make changes to the selected detail table and column. When you are finished managing the aggregations, select Apply All.

Quick Measures

You can select the calculation that you want and the fields to run the calculation against. For instance, you can select a calculation and the column that you want to operate over. Power BI creates the DAX measure for you and displays the DAX.

How to create a histogram in Power BI

You start by selecting the clustered column chart icon on the Visualization pane. Next, create a new grouping for the x-axis. You will learn more about grouping and binning later in this module, but they are useful in this context also. To create the group, in the Fields pane, right-click the data field that you want to analyze and then select New Group. In this case, you use the OrderQty field. In the Groups window that displays, set up the bin group as follows: Rename the group as Order Bins (Buckets). Set the Group type option to Bin and the Bin Type option to Number of bins. Enter 5 as the Bin count, 1 as the Min value, and 44 as the Max value. Next, populate the visual as follows: Drag and drop the OrderQty field from the Fields pane into the Value field on the Visualizations pane. Drag and drop the Order Bins (Buckets) group from the Fields pane into the Axis field on the Visualizations pane.

Slicer Visualization

a standalone chart that can be used to filter the other visuals on the page

Filters

allow users to interact with the visuals at the report, page, and visual level. As a report designer, you can customize the Filters pane in Power BI Desktop as follows: Add and remove fields to filter on. Change the filter state. Format and customize the Filters pane so that it seems part of your report. Define whether the Filters pane is open or collapsed by default when a consumer opens the report. Hide the entire Filters pane or specific filters that you don't want report consumers to see. Control and bookmark the visibility, open, and collapsed state of the Filters pane. Lock filters that you don't want consumers to edit.

Q&A Visualization

allows you to ask natural language questions and get answers in the form of a visual. This ability to ask questions is valuable to consumers and to you, the report author. This visualization type can help you create visuals in the report, and it can also be used as a tool for consumers to get answers quickly. The Q&A visualization consists of the following four core components: 1. The question box, where users enter their question and are shown suggestions to help them complete the question. 2. A pre-populated list of suggested questions. 3. An icon that users can select to convert the Q&A visual into a standard visual. 4. An icon that users can select to open Q&A tooling, which allows designers to configure the underlying natural language engine. When entering natural language queries with Power BI Q&A, you can specify the visual type in your query.

Cross Report Drillthrough

allows you to contextually jump from one report to another report in the same Power BI service workspace or app. This feature allows you to connect two or more reports that have related content. You can also pass filter context along with that cross-report connection. To enable Cross-report drillthrough, you first need to validate the data models for the source and target reports. Though the schemas in each report don't have to be the same, both data models must contain the fields that you want to pass. Also, the names of those fields, and the names of the tables that they belong to, must be identical. The strings must match and are case-sensitive. If they are not identical, you must update the field name or table name in the underlying model.

Scheduled Refresh

allows you to define the frequency and time slots to refresh a particular dataset 1. Go to the Datasets + dataflows page. 2. Hover over the dataset 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 dataset is on shared capacity, or 48 time slots on Power BI Premium. 6. When you have finished configuring the scheduled refresh, select Apply.

What is the auto date/time feature?

allows you to work with time intelligence when filtering, grouping, and drilling down through calendar time periods. We recommend that you keep the Auto date/time option enabled only when you work with calendar time periods and when you have simplistic model requirements in relation to time.

Tradeoffs between Import and DirectQuery: which is generally best? And what are the reasons for using DirectQuery?

best practice to import data into Power BI Desktop, but your organization might need to use the DirectQuery data connectivity mode because of one of the following reasons (benefits of DirectQuery): 1. It is suitable in cases where data changes frequently and near real-time reporting is required. 2. It can handle large data without the need to pre-aggregate. 3. It applies data sovereignty restrictions to comply with legal requirements. 4. It can be used with a multidimensional data source that contains measures such as SAP Business Warehouse (BW).

DirectQuery and Performance

depends heavily on the performance of the underlying data source. Slow query response times will lead to a negative user experience and, in the worst-case scenarios, queries might time out. Also, the number of users who are opening the reports at any one time will impact the load that is placed on the data source. For example, if your report has 20 visuals in it and 10 people are using the report, 200 queries or more will exist on the data source because each visual will issue one or more queries. also impacted by network latency & performance of data source's server & other workloads on that server

Funnel Visualization

displays a linear process that has sequential connected stages, where items flow sequentially from one stage to the next. Funnel charts are most often seen in business or sales contexts. For example, they are useful for representing a workflow, such as moving from a sales lead to a prospect, through to a proposal and sale. To calculate a potential outcome (revenue, sales, deals, and so on) by stages. To calculate and track conversion and retention rates. To reveal bottlenecks in a linear process.

Scatter Chart

effective when you are comparing large numbers of data points without regard to time. The scatter chart has two value axes to show: one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis. The chart displays points at the intersection of an X and Y numerical value, combining these values into single data points. These data points might be distributed evenly or unevenly across the horizontal axis, depending on the data. You can set the number of data points, up to a maximum of 10,000. You might want to use a scatter chart instead of a line chart because it allows you to change the scale of th

Accessibility Standards under the Web Content Accessibility Guidelines (WCAG)

ensure that your Power BI experiences are accessible to as many people as possible. When you build accessible reports or dashboards, that content is accessible for anyone who views them by using Power BI Mobile Key Principles: Perceivable - Information and user interface components must be presentable to users in ways that they can perceive. Operable - User interface components and navigation must be operable. Understandable - Information and the operation of the user interface must be understandable

TOTALYTD()

evaluates an expression—in this case the sum of the Sales column—over a given date column. The date column must belong to a date table marked as a date table. The function can also take a third optional argument representing the last date of a year. The absence of this date means that December 31 is the last date of the year. For Adventure Works, June in the last month of their year, and so "6-30" is used.

ISINSCOPE() Sales % Country = IF( ISINSCOPE(Region[Region]), DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region[Region] ) ) )

function used to test whether the region column is in a hierarchy of levels. When true, the DIVIDE() function is evaluted

How to add sensitivity labels?

go to any workspace and choose an object to secure (e.g. Sales Data Report -- under the ellipsis select settings then apply a Sensitivity Label)

Key Influencers Visual

helps you understand the factors that are affecting a specific metric. It analyzes your data for you, ranks the factors that matter, and then displays those factors as key influencers. The visual also helps you to contrast the relative importance of these factors, meaning that you can build your visuals while understanding what factors impact those visuals and why the visuals appear the way that they do.

Impact analysis Window in the Dataset Card in the lineage view shows you what?

how many workspaces, reports, and dashboards that this dataset is a part of and how many views that this dataset has gathered detail about which specific reports and dashboards that this dataset is part of. Additionally, you can select Notify contacts, which allows you to notify dataset owners (or any other user) of changes in the dataset. Impact analysis is useful because it allows you to pinpoint datasets that aren't being used or looked at.

What is metadata?

information about other data. Power BI metadata contains information on your data model, such as the name, data type and format of each of the columns, the schema of the database, the report design, when the file was last modified, the data refresh rates, and much more. File size & data refresh rates are also considered metadata

Time series analysis

involves analyzing a series of data in time order to identify meaningful information and trends and make predictions. The result of time series analysis is the best data that you can use for forecasting activities. Time series analysis often involves the use of visuals such as Gantt charts, project planning, and stock movement datasets.

What form can streaming dataset tiles be in?

line charts, stacked bar charts, cards, and gauges

Performance Optimization

making changes to the current state of the data model so that it runs more efficiently minimizing the size of the data model and making the most efficient use of the data in the model, which includes: Ensuring that the correct data types are used. Deleting unnecessary columns and rows. Avoiding repeated values. Replacing numeric columns with measures. Reducing cardinalities. Analyzing model metadata. Summarizing data where possible.

How to apply the AI Insights feature

open Power Query Editor (Home --> Transform Data) and select the Add Column tab. Three AI Insights options will be available for you to choose from: Text Analytics, Vision, and Azure Machine Learning Premium capacity is required to use the Text Analytics and Vision options.

How to check refresh history

open the dataset's settings page and then select Refresh history.

Maps

provide default map coordinates (a process called geocoding), so you can create maps. Together, they use algorithms to identify the correct location; however, sometimes, it's a best guess. A basic map (bubble map) is used to associate categorical and quantitative information with spatial locations. This type of map visual displays precise geographical locations of data points on a map, as illustrated in the following image. A fill map uses shading, tinting, or patterns to display how a value differs in proportion across a geographical region. Similarly, shape maps use colors to display relative comparisons of geographical regions. You can also use an ArcGIS map to display graphical information in a more interactive way.

Analyze feature in Power BI

provides you with additional analysis that is generated by Power BI for a selected data point To use the Analyze feature, right-click a data point on the visual and then hover over the Analyze option to display two further options: 1. Explain the increase 2. Find where the distribution is different. The options that are available will depend on the data point that you selected. If you find this analysis useful, you can add the new visual to your report so that other users can view it. Select the plus (+) icon in the upper-right corner of the visual to add it to your report.

Dynamic Reports

reports in which the data can be changed by a developer according to user specifications. Dynamic reports are valuable because a single report can be used for multiple purposes. If you use dynamic reports, you'll have fewer individual reports to create, which will save organizational time and resources Creating dynamic reports allows you to give users more power over the data that is displayed in your reports; they can change the data source and filter the data by themselves.

How to apply clustering to a scatter chart

select More options (...) in the upper-right corner of the visual and then select Automatically find clusters On the Clusters window that displays, you can edit the default name, field, and description, if required. However, for this example, you want to change the number of clusters. The following image shows that the Number of clusters box is blank by default, which means that Power BI automatically finds the number of clusters that it determines as making the most sense with your data. If you want to find clusters by using more than two measures, you can use a table visual instead of a scatter chart, add all the fields you want to use, and then run the clustering algorithm by using the same process.

Pie chart, donut chart, treemaps

show you the relationship of parts to the whole by dividing the data into segments. From a data analysis perspective, these charts are not useful because interpreting the data that they present can be difficult. However, these charts are often used for aesthetic reasons due to the colorful segments that they display. These charts are best suited for illustrating percentages, such as the top five sales by product or country, or any other available categories. The pie chart is a solid circle, whereas the donut chart has a center that is blank and allows space for a label or icon. Treemap visualization displays data as a set of nested rectangles. Each level of the hierarchy is represented by a colored rectangle (branch) containing smaller rectangles (leaves). The space inside each rectangle is allocated based on the value that is being measured. The rectangles are arranged in size from top left (largest) to bottom right (smallest).

Waterfall Visualization

shows a running total as values are added or subtracted, which is useful in displaying a series of positive and negative changes. The chart consists of color-coded columns, so you can quickly identify increases and decreases. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns. Waterfall charts can be used to: Visualize changes over time or across different categories. Audit the major changes that contribute to the total value. Plot your organization's annual profit by showing various sources of revenue to help determine the total profit (or loss). Illustrate the beginning and ending headcount for your organization in a year. Visualize how much money you earn and spend each month and the running balance for your account.

What is report navigation?

the way in which your report users move from one page in your report to the next, move from one visual to another, and return to where they started. The design of your report navigation is important because, if users can't easily find their way around in your reports, they will become frustrated and will have a negative experience. You can use a range of buttons and bookmarks when designing your report navigation, and you can further enhance this navigation experience with the use of conditional formatting.

Boost performance with query caching (premium)

use the local caching services of Power BI to process query results. Instead of relying on the dataset to calculate queries, which when overloaded can reduce performance, you can use cloud resources on your Premium capacities on Power BI service to load your report and, thereby, ensure constant performance maintains results on a user and report basis only available for Power BI Premium or Power BI Embedded.

What happens when you pin a report element to a dashboard?

you create a direct connection between the dashboard and the report that the snapshot came from note that you can pin entire pages or you can pin an individual tile

Differences between Azure Analysis Services cubes and SQL Server

1. Analysis Services cubes have calculations already in the cube 2. If you don't need an entire table, you can query the data directly. Instead of using Transact-SQL (T-SQL) to query the data, like you would in SQL Server, you can use multi-dimensional expressions (MDX) or data analysis expressions (DAX).

How to get data from Azure Analysis Services?

1. Authenticate to the server. 2. Pick the cube you want to use. 3. Select which tables you need. Get data feature in Power BI Desktop. When you select Analysis Services, you are prompted for the server address and the database name with two options: Import and Connect live.

A simple table structure will be able to:

1. Be simple to navigate because of column and table properties that are specific and user-friendly. 2. Have merged or appended tables to simplify the tables within your data structure. 3. Have good-quality relationships between tables that make sense.

Common Flow of Activity in Power BI

1. Bring data into Power BI Desktop, and create a report. 2. Publish to the Power BI Service, where you can create new visualizations or build dashboards. 3 Share dashboards with others, especially people who are on the go. 4. View and interact with shared dashboards and reports in Power BI Mobile apps.

How to create a custom column in Power BI?

1. Create the column in the source query when you get the data, for instance, by adding the calculation to a view in a relational database. 2. Create the custom column in Power Query. 3. Create a calculated column by using DAX in Power BI.

How can you change the source file?

1. Data source settings -- this is in PowerQuery, then select file and click change source, update the file path or use browse to locate file, select OK , then Close 2. Query settings 3. Advanced Editor

How to connect to a sql database?

1. Get data 2. SQL Server 3. Type in server name (and database) 4. Log in with username and password (use windows account / same as Azure AD, database credentials, or microsoft account credentials) 5. Select data to import in the navigator -- chose to load or transform data

Data Analyst

(Prepare, Model, Visualize, Analyze, Manage) enables businesses to maximize the value of their data assets through visualization and reporting tools such as Microsoft Power BI. Data analysts are responsible for profiling, cleaning, and transforming data. Their responsibilities also include designing and building scalable and effective data models, and enabling and implementing the advanced analytics capabilities into reports for analysis. A data analyst works with the pertinent stakeholders to identify appropriate and necessary data and reporting requirements, and then they are tasked with turning raw data into relevant and meaningful insights. A data analyst is also responsible for the management of Power BI assets, including reports, dashboards, workspaces, and the underlying datasets that are used in the reports. They are tasked with implementing and configuring proper security procedures, in conjunction with stakeholder requirements, to ensure the safekeeping of all Power BI assets and their data. Data analysts work with data engineers to determine and locate appropriate data sources that meet stakeholder requirements. Additionally, data analysts work with the data engineer and database administrator to ensure that the analyst has proper access to the needed data sources. The data analyst also works with the data engineer to identify new processes or improve existing processes for collecting data for analysis.

Many-to-one (*:1) or one-to-many (1: *) Cardinality

- Describes a relationship in which you have many instances of a value in one column that are related to only one unique corresponding instance in another column. - Describes the directionality between fact and dimension tables. - Is the most common type of directionality and is the Power BI default when you are automatically creating relationships. An example of a one-to-many relationship would be between the CountryName and Territory tables, where you can have many territories that are associated with one unique country.

For what transformations are native queries not possible?

1. Adding an index column 2. Merging and appending columns of different tables with two different sources 3. Changing the data type of a column 4. Running complex DAX functions A good guideline to remember is that if you can translate a transformation into a Select SQL statement, which includes operators and clauses such as GROUP BY, SORT BY, WHERE, UNION ALL, and JOIN, you can use query folding.

How to create a data table using DAX

1. Go to Table tab on ribbon --> new Table; then enter the DAX formula with Calendar() or CalendarAuto() 2. This creates a singular column of dates that you can use. By selecting New Column you can enter additional DAX equations to retrieve specifics from the Date column (e.g. get the year, month, day, week number)

How to create your own hierarchies?

1. Go to fields pane and right click the col you want the hierarchy for 2. select new hierarchy 3. drag and drop the subcategory column into the new hierarchy you've created, this col will be added as a sublevel on the hierarchy

Building Blocks in Power BI

1. Visualizations -- visual representation of data, like a chart, or color-coded map 2. Datasets -- collection of data that PBI uses to create its visualizations 3. Reports -- collection of visualizations that appear together on one or more pages. Can create in PBI desktop or PBI service 4. Dashboards -- A single page from a report; a collection of visuals from a single page / canvas 5. Tiles -- a single visualization (the rectangular box that holds the visual). When creating a report you can change the size and position of the tiles but when viewing or consuming you can't move them but can interact with it

If you have two queries that have different data, but you want to concatenate the results into one query with all the combined rows, which operation should you perform? A. Append B. Merge C. Combine Column

A. Append Will take two tables and combine it into one query. The combined query will have more rows while keeping the same number of columns.

What is Azure Analysis Services?

Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and creates calculations on the data. The calculations are built using data analysis expressions (DAX). Azure Analysis Services is similar to the data modeling and storage technology in Power BI. If you want to directly query the Azure Analysis Services model, you can use DAX or MDX.

What is a risk of having null values in a numeric column? A. DAX expressions that SUM data will be incorrect B. DAX expressions that MAX data will be incorrect C. DAX expressions that AVERAGE data will be incorrect

C. DAX expressions that AVERAGE data will be incorrect AVERAGE takes the total and divides by the number of non-null values. If NULL is synonymous with zero in the data, the average will be higher than the accurate average.

DAX for date tables

CALENDARAUTO() or CALENDAR() to build your common date table. CALENDAR() function returns a contiguous range of dates based on a start and end date that are entered as arguments in the function. CALENDARAUTO() function returns a contiguous, complete range of dates that are automatically determined from your dataset. The starting date is chosen as the earliest date that exists in your dataset, and the ending date is the latest date that exists in your dataset plus data that has been populated to the fiscal month that you can choose to include as an argument in the CALENDARAUTO() function

Hierarchies

Can help you find detail in dimensional tables Form through natural segments in the data (e.g. Date can be segmented to year, quarter, month, day) Allow you to drill down into specifics of data

How to update data in Power BI service?

Can refresh on the dataset directly Can schedule a refresh under the settings tab

What is DAX?

Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

Benefits of a good data model

Data exploration is faster. Aggregations are simpler to build. Reports are more accurate. Writing reports takes less time. Reports are easier to maintain in the future.

Difference between fact and dimension tables

Fact tables are usually much larger than dimension tables because numerous events occur in fact tables, such as individual sales. Dimension tables are typically smaller because you are limited to the number of items that you can filter and group on. For instance, a year contains only so many months, and the United States is comprised of only a certain number of states. Dimension tables are used to filter fact tables

How to mark your table as the official date table?

Find the table in the Fields pane. Right click the table and select Mark as date table By marking your table as a date table, Power BI performs validations to ensure that the data contains zero null values, is unique, and contains continuous date values over a period.

Configure table and column properties in the Model view -- What is under the General tab? Formatting? Advanced?

General: Edit the name and description of the column. Add synonyms that can be used to identify the column when you are using the Q&A feature. Add a column into a folder to further organize the table structure. Hide or show the column. Formatting: Change the data type Format the date Advanced: Sort by a specific column Assign a specific category to the data Summarize the data Determine if the column or table contains null values

Descriptive Analytics

Help answer questions about what has happened based on historical data. Descriptive analytics techniques summarize large datasets to describe outcomes to stakeholders. By developing key performance indicators (KPIs), these strategies can help track the success or failure of key objectives. Metrics such as return on investment (ROI) are used in many industries, and specialized metrics are developed to track performance in specific industries. An example of descriptive analytics is generating reports to provide a view of an organization's sales and financial data.

Source Data (for date table)

Identify company holidays Separate calendar and fiscal year Identify weekends versus weekdays If you have a table as such, bring it into your data model and don't use any other methods that are outlined in this section. We recommend that you use a source date table because it is likely shared with other tools that you might be using in addition to Power BI. If you do not have a source data table, you can use other ways to build a common date table.

Both Cross-Filter / Bi-Directional Cross Filtering

One table in a relationship can be used to filter the other. For instance, a dimension table can be filtered through the fact table, and the fact tables can be filtered through the dimension table. You might have lower performance when using bi-directional cross-filtering with many-to-many relationships. A word of caution regarding bi-directional cross-filtering: You should not enable bi-directional cross-filtering relationships unless you fully understand the ramifications of doing so. Enabling it can lead to ambiguity, over-sampling, unexpected results, and potential performance degradation.

Which file options are the best way to keep Power BI information in-sync

OneDrive or SharePoint Teams sites (because it's in the cloud -- this is good if you update data regularly, if it's not updated regularly, using from local files can be fine)

Single Cross-Filter Direction

Only one table in a relationship can be used to filter the data. For instance, Table 1 can be filtered by Table 2, but Table 2 cannot be filtered by Table 1. Only one table in a relationship can be used to filter the data. For instance, Table 1 can be filtered by Table 2, but Table 2 cannot be filtered by Table 1. Follow the direction of the arrow on the relationship between your tables to know which direction the filter will flow. You typically want these arrows to point to your fact table.

What is Power BI?

Power BI is a collection of services, apps, and connectors that lets you connect to your data, wherever it happens to reside, filter it if necessary, and then bring it into Power BI to create compelling visualizations that you can share with others.

Skills Measured

Prepare the Data (20-25%) Model the Data (20-30%) Visualize the Data (20-25%) Analyze the Data (10-15%) Deploy and Maintain Deliverables (10-15%)

Calculated Columns

Required to operate row by row

Data Type Errors

Sometimes, when you import data into Power BI, the columns appear blank. This situation happens because of an error in interpreting the data type in Power BI. The resolution to this error is unique to the data source. For instance, if you are importing data from SQL Server and see blank columns, you could try to convert to the correct data type in the query. Example: Instead of using this query: SELECT CustomerPostalCode FROM Sales.Customers Use this query: SELECT CAST(CustomerPostalCode as varchar(10)) FROM Sales.Customers

Where can you learn about the underlying data structures in Power BI?

The Model Tab on PBI Desktop On the Model tab, you can edit specific column and table properties by selecting a table or columns, and you can transform the data by using the Transform Data button, which takes you to Power Query Editor. Additionally, you can manage, create, edit, and delete relationships between different tables by using Manage Relationships, which is located on the ribbon.

Flattening the hierarchy

The process of viewing multiple child levels based on a top-level parent creating multiple columns in a table to show the hierarchical path of the parent to the child in the same record. You will use PATH(), a simple DAX function that returns a text version of the managerial path for each employee, and PATHITEM() to separate this path into each level of managerial hierarchy

DAX Formatting: Describe each piece of this statement Total Price = 'Sales OrderDetails'[Quantity] * 'Sales OrderDetails'[Unit Price]

The value on the left side of the equal sign is the column name. The text on the right side of the equal sign is the DAX expression. This simple DAX expression takes the quantity value and multiplies it with the unit price value for each individual row. It will produce one value for each record in the table. If you drag the new column from the Fields list to the visual, you will see the new values.

Could not find file

Usually, this error is caused by the file moving locations or the permissions to the file changing. If the cause is the former, you need to find the file and change the source settings. 1. Open Power Query by selecting the Transform Data button in Power BI. 2. Highlight the query that is creating the error. 3. On the left, under Query Settings, select the gear icon next to Source. 4. Change the file location to the new location.

Business Analyst

While some similarities exist between a data analyst and business analyst, the key differentiator between the two roles is what they do with data. A business analyst is closer to the business and is a specialist in interpreting the data that comes from the visualization. Often, the roles of data analyst and business analyst could be the responsibility of a single person.

Does power bi automatically enter values of the date type as a hierarchy?

Yes, but only if the table has not been marked as a date table

Import

- Most popular way to use data in Power BI is to import it into a PBI dataset - Importing the data means that the data is stored in the Power BI file and gets published along with the Power BI reports. - Import creates a local Power BI copy of your datasets from your data source. - You can use all Power BI service features with this storage mode, including Q&A and Quick Insights. - Data refreshes must be done manually. - Import mode is the default for creating new Power BI reports.

One-to-one (1:1) Cardinality

- Describes a relationship in which only one instance of a value is common between two tables. - Requires unique values in both tables. - Is not recommended because this relationship stores redundant information and suggests that the model is not designed correctly. It is better practice to combine the tables. An example of a one-to-one relationship would be if you had products and product IDs in two different tables. Creating a one-to-one relationship is redundant and these two tables should be combined.

Many-to-many (.) Cardinality

- Describes a relationship where many values are in common between two tables. - Does not require unique values in either table in a relationship. - Is not recommended; a lack of unique values introduces ambiguity and your users might not know which column of values is referring to what. For instance, the following figure shows a many-to-many relationship between the Sales and Order tables on the OrderDate column because multiple sales can have multiple orders associated with them. Ambiguity is introduced because both tables can have the same order date.

How to get data from a NoSQL database?

1. Get data feature in Power BI Desktop. However, this time you will select the More... option to locate and connect to the type of database that you use. 2. In this example, you will select the Azure category, select Azure Cosmos DB, and then select Connect. 3. On the Preview Connector window, select Continue and then enter your database credentials. In this example, on the Azure Cosmos DB window, you can enter the database details. 4. You can specify the Azure Cosmos DB account endpoint URL that you want to get the data from (you can get the URL from the Keys blade of your Azure portal). Alternatively, you can enter the database name, collection name or use the navigator to select the database and collection to identify the data source. If you are connecting to an endpoint for the first time, as you are in this example, make sure that you enter your account key. You can find this key in the Primary Key box in the Read-only Keys blade of your Azure portal.

How to get data from online services?

1. Get data feature in Power BI Desktop. Then, select the option that you need from the Online Services category. 2. In this example, you select SharePoint Online List. 3. After you have selected Connect, you'll be asked for your SharePoint URL. You do not need to enter your full URL file path; you only need to load your site URL because, when you are connected, you can select the specific list that you want to load. 4. Click OK 5. Sign in with your (Microsoft) account and select connect 6. After Power BI has made the connection with SharePoint, the Navigator window appears, as it does when you connect to other data sources. The window displays the tables and entities within your SharePoint site. Select the list that you want to load into Power BI Desktop. Similar to when you import from other data sources, you have the option to automatically load your data into Power BI model or launch the Power Query Editor to transform your data before loading it.

How to access Query Diagnostics?

1. Go to Tools in the Home ribbon. When you are ready to begin transforming your data or making other edits in Power Query Editor, 2. select Start Diagnostics on the Session Diagnostics tab. 3. When you are finished, make sure that you select Stop Diagnostics. Selecting Diagnose Step shows you the length of time that it takes to run that step

What are the three types of storage modes in PBI?

1. Import 2. DirectQuery 3. Dual (Composit)

Cardinality Types

1. Many-to-one (*:1) or one-to-many (1: *) 2. One-to-one (1:1) 3. Many-to-many (.)

Query Folding Benefits

1. More efficiency in data refreshes and incremental refreshes. When you import data tables by using query folding, Power BI is better able to allocate resources and refresh the data faster because Power BI does not have to run through each transformation locally. 2. Automatic compatibility with DirectQuery and Dual storage modes. All DirectQuery and Dual storage mode data sources must have the back-end server processing abilities to create a direct connection, which means that query folding is an automatic capability that you can use. If all transformations can be reduced to a single Select statement, then query folding can occur.

How to change the data source settings? (Transform Data)

1. On the Home tab, select Transform data, and then select the Data source settings option. 2. From the list of data sources that displays, select the data source that you want to update. 3. Then, you can right-click that data source to view the available update options or you can use the update option buttons on the lower left of the window. 4. Select the update option that you need, change the settings as required, and then apply your changes. After you create a data source connection and load data into Power BI Desktop, you can return and change your connection settings at any time

Common data import errors result from

1. Power BI imports from numerous data sources. 2. Each data source might have dozens (and sometimes hundreds) of different error messages. 3. Other components can cause errors, such as hard drives, networks, software services, and operating systems. 4. Data can often not comply with any specific schema.

How to fix performance issues? / Imporve performance in Power BI?

1. Query Folding 2. Query Diagnostics 3. Process as much data as possible in the original data source 4. Use Native SQL Queries -- make sure you aren't pulling data from stored procedures or Common Table Expressions when using DirectQuery for SQL databases 5. Separate data and time if bound together

How to import data by writing a SQL query?

1. SQL Server database window, enter your server and database names 2. Select the arrow next to Advanced options to expand this section and view your options 3. In the SQL statement box, write your query statement, and then select OK. In this example, you will use the Select SQL statement to load the ID, NAME and SALESAMOUNT columns from the SALES table.

How to change the data source settings? (Power Query)

1. Select the table 2. Select the Data source settings option on the Home ribbon. Alternatively, you can go to the Query Settings panel on the right side of the screen and select the settings icon next to Source (or double Select Source). In the window that displays, update the server and database details, and then select OK. 3. After you've made changes click Close and Apply to apply the changes to your data source settings

NoSQL Database

A NoSQL database (also referred to as non-SQL, not only SQL or non-relational) is a flexible type of database that does not use tables to store data. e.g. Cosmos DB

Power BI Apps

An app is a collection of preset, ready-made visuals and reports that are shared with an entire organization. 1. Go to Power BI Service 2. Select Get Data (lower left corner) --> can use data from different SaaS providers e.g. Salesforce, Facebook, Google Analytics Apps get you up and running quickly, with data and dashboards that your organization has created for you. For example, when you use the GitHub app, Power BI connects to your GitHub account (after you provide your credentials) and then populates a predefined collection of visuals and dashboards in Power BI.

Creating a calculated column by pulling the data from the data source: SQL Example

CREATE VIEW OrdersWithTotalPrice AS SELECT unitprice, qty, unitprice * qty as TotalPrice FROM sales.salesorders Using SQL language is an efficient way of creating a column because it would make the data source do the calculations for you. In Power BI, the calculated column would appear like any other column.

Fact Tables

Contain observational or event data values: sales orders, product counts, prices, transactional dates and times, and quantities Fact tables can contain several repeated values. For example, one product can appear multiple times in multiple rows, for different customers on different dates. These values can be aggregated to create visuals. For instance, a visual of the total sales orders is an aggregation of all sales orders in the fact table. With fact tables, it is common to see columns that are filled with numbers and dates. The numbers can be units of measurement, such as sale amount, or they can be keys, such as a customer ID. The dates represent time that is being recorded, like order date or shipped date.

Connect to data in a relational database

E.g. SQL Server Need to put in Server name and optionally database name Also need to select the data connectivity mode

What is included when an app is created in Power BI Service?

Ex: Get Data --> Service --> GitHub In addition to the app dashboard, the report that was generated (as part of the GitHub app) and used to create the dashboard is available, as is the dataset (the collection of data pulled from GitHub) that was created during data import and used to create the GitHub report. (Dashboard, report, and dataset)

When do you need to refresh the dataset to see the new calculated columns?

If using DAX you DO NOT need to refresh the dataset to see the new column (however, DAX is generally not the best option because it doesn't compress as well as the other methods) In Power Query and when using source data you DO need to refresh the dataset

Data Connectivity Options

Import (used most often) DirectQuery

Star Schemas

In a star schema, each table within your dataset is defined as a dimension or a fact table, as shown in the following visual the more time you spend creating these connections and design, the easier it will be to create and maintain reports.

Query Folding

Increases performance of PBI reports Process by which the transformations and edits that you make in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you are actively making transformations. The reason for implementing this process is to ensure that these transformations can take place in the original data source server and do not overwhelm Power BI computing resources.

Where should you be writing your SQL Queries?

It is a best practice to avoid doing this directly in Power BI. Instead, consider writing a query like this in a view. A view is an object in a relational database, similar to a table. Views have rows and columns, and can contain almost every operator in the SQL language. If Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Power Query. Query folding will be explained later, but in short, Power Query will optimize data retrieval according to how the data is being used later.

How to import a JSON file in Power BI

JSON type records must be extracted and normalized before you can report on them, so you need to transform the data before loading it into Power BI Desktop. After connecting to a DB account the navigator window opens showing the DBs in that account. Select the table you want to import -- the preview pane only shows "Record" items b/c all record in the document are represented as a Record type in PBI Select EDIT to open records in Power Query In Power Query select the Expander button to the right side of the Column1 header which will display the context menu w/ a list of fields Select the field you want to load into PBI desktop clear the Use Original Column name as prefix checkbox then select OK Click Close & Apply Data now looks like a table w/ rows and cols. Data from Cosmos DB can now be related to data from other sources

Load Data vs Transform Data in Power BI

Load - Automatically load your data into a Power BI model in its current state. Transform Data - Open your data in Microsoft Power Query, where you can perform actions such as deleting unnecessary rows or columns, grouping your data, removing errors, and many other data quality tasks.

Flat file location

Local OneDrive for Business OneDrive Personal SharePoint - Team Sites: Saving your Power BI Desktop files to SharePoint Team Sites is similar to saving to OneDrive for Business. The main difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.

What language does Power Query use behind the scenes?

M

How to use a specific column from a table to mark as the date?

Right-click the table, select Mark as date table, and then select Date table settings. The following window will appear, where you can choose which column should be marked as Date. Selecting Mark as date table will remove autogenerated hierarchies from the Date field in the table that you marked as a date table. For other date fields, the auto hierarchy will still be present until you establish a relationship between that field and the date table or until you turn off the Auto Date/Time feature. You can manually add a hierarchy to your common date table by right-clicking the year, month, week, or day columns in the Fields pane and then selecting New hierarchy.

What is Query Folding Dependent on?

View Native Query Option If the View Native Query option is not available (not displayed in bold type under Applied Steps), that query folding is not possible for this step, and you will have to work backward in the Applied Steps area until you reach the step in which View Native Query is available (displays in bold type). This process will reveal the native query that is used to transform the dataset.

Iterator Functions

X Functions (e.g. SUMX, COUNTX, MINX) they allow you to create measures that are aware of the data in individual rows and calculate totals based on the totals in the row An X function will perform better and use less disk space than a calculated column

How to build a visual with date tables?

You will need to establish a relationship between this new common date table and the Sales and Orders tables. As a result, you will be able to build visuals by using the new date table. To complete this task, go to Model tab > Manage Relationships, where you can create relationships between the common date table and the Orders and Sales tables by using the OrderDate column

Query Diagnostics

allows you to determine what bottlenecks (if any) exist while loading and transforming your data, refreshing your data in Power Query, running SQL statements in Query Editor, and so on.

Cognitive Analytics

attempt to draw inferences from existing data and patterns, derive conclusions based on existing knowledge bases, and then add these findings back into the knowledge base for future inferences, a self-learning feedback loop. Cognitive analytics help you learn what might happen if circumstances change and determine how you might handle these situations. Inferences aren't structured queries based on a rules database; rather, they're unstructured hypotheses that are gathered from several sources and expressed with varying degrees of confidence. Effective cognitive analytics depend on machine learning algorithms, and will use several natural language processing concepts to make sense of previously untapped data sources, such as call center conversation logs and product reviews.

Dimension Tables

contain the details about the data in fact tables: products, locations, employees, and order types. These tables are connected to the fact table through key columns. Dimension tables are used to filter and group the data in fact tables. The dimension tables, by contrast, contain unique values, for instance, one row for each product in the Products table and one row for each customer in the Customer table.

Left Outer Join

displays all the rows from first table and only the matched rows from second table

We couldn't find any data formatted as a table

error while importing data from Microsoft Excel. Fortunately, this error is self-explanatory. Power BI expects to find data formatted as a table from Excel. The error event tells you the resolution. Perform the following steps to resolve the issue: 1. Open your Excel workbook, and highlight the data that you want to import. 2. Press the Ctrl-T keyboard shortcut. The first row will likely be your column headers. 3. Verify that the column headers reflect how you want to name your columns. Then, try to import data from Excel again. This time, it should work.

Predictive Analytics

help answer questions about what will happen in the future. Predictive analytics techniques use historical data to identify trends and determine if they're likely to recur. Predictive analytical tools provide valuable insight into what might happen in the future. Techniques include a variety of statistical and machine learning techniques such as neural networks, decision trees, and regression.

Diagnostic Analytics

help answer questions about why events happened. Diagnostic analytics techniques supplement basic descriptive analytics, and they use the findings from descriptive analytics to discover the cause of these events. Then, performance indicators are further investigated to discover why these events improved or became worse. Generally, this process occurs in three steps: 1. Identify anomalies in the data. These anomalies might be unexpected changes in a metric or a particular market. 2. Collect data that's related to these anomalies. 3. Use statistical techniques to discover relationships and trends that explain these anomalies.

Dual (Composite mode(

identify some data to be directly imported and other data that must be queried. Any table that is brought in to your report is a product of both Import and DirectQuery modes. Using the Dual mode allows Power BI to choose the most efficient form of data retrieval.

Database Administrator

implements and manages the operational aspects of cloud-native and hybrid data platform solutions that are built on Microsoft Azure data services and Microsoft SQL Server. A data administrator is responsible for the overall availability and consistent performance and optimizations of the database solutions. They work with stakeholders to identify and implement the policies, tools, and processes for data backup and recovery plans. The role of a database administrator is different from the role of a data engineer. A database administrator monitors and manages the overall health of a database and the hardware that it resides on, whereas a data engineer is involved in the process of data wrangling, in other words, ingesting, transforming, validating, and cleaning data to meet business needs and requirements. The database administrator is also responsible for managing the overall security of the data, granting and restricting user access and privileges to the data as determined by business needs and requirements.

What is Connect Live?

new option in Azure Analysis Services helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI. Azure Analysis Services can have a fast refresh schedule , which means that when data is refreshed in the service, Power BI reports will immediately be updated, without the need to initiate a Power BI refresh schedule

Cardinality & Cross Filtering for one to one relationships

only option that is available is bi-directional cross-filtering. Data can be filtered on either side of this relationship and result in one distinct, unambiguous value. For instance, you can filter on one Product ID and be returned a single Product, and you can filter on a Product and be returned a single Product ID.

Data Scientist

perform advanced analytics to extract value from data. Their work can vary from descriptive analytics to predictive analytics. Descriptive analytics evaluate data through a process known as exploratory data analysis (EDA). Predictive analytics are used in machine learning to apply modeling techniques that can detect anomalies or patterns. These analytics are important parts of forecast models. Descriptive and predictive analytics are only partial aspects of data scientists' work. Some data scientists might work in the realm of deep learning, performing iterative experiments to solve a complex data problem by using customized algorithms. A data scientist looks at data to determine the questions that need answers and will often devise a hypothesis or an experiment and then turn to the data analyst to assist with the data visualization and reporting.

Data Engineer

provision and set up data platform technologies that are on-premises and in the cloud. They manage and secure the flow of structured and unstructured data from multiple sources. The data platforms that they use can include relational databases, nonrelational databases, data streams, and file stores. Data engineers also ensure that data services securely and seamlessly integrate across data services. Data engineers collaborate with business stakeholders to identify and meet data requirements. They design and implement solutions. While some alignment might exist in the tasks and responsibilities of a data engineer and a database administrator, a data engineer's scope of work goes well beyond looking after a database and the server where it's hosted and likely doesn't include the overall operational data management. A data engineer adds tremendous value to business intelligence and data science projects. When the data engineer brings data together, often described as data wrangling, projects move faster because data scientists can focus on their own areas of work.

PowerQuery

query engine used by Power BI and Excel Helps you clean the data, such as renaming columns, replacing values, removing errors, and combining query results

DirectQuery

storage mode, which allows you to query the data in the data source directly and not import a copy into Power BI. DirectQuery is useful because it ensures you are always viewing the most recent version of the data. Useful when you don't want to save local copies of your data b/c data won't be cacehd Creates a direct connection to the data source Ensures security requirements are satisfied Mode is suited for when you have large datasets to pull from, instead of slowing down performance you can use DirectQuery to connect to the source

Flat file

type of file that has only one data table and every row of data is in the same structure. The file does not contain hierarchies. Likely, you're familiar with the most common types of flat files, which are comma-separated values (.csv) files, delimited text (.txt) files, and fixed width files. Another type of file would be the output files from different applications, like Microsoft Excel workbooks (.xlsx).