DA-100 Analyzing Data with Microsoft Power BI
Why dimension tables are typically smaller than fact tables.
because you are limited to the number of items that you can filter and group on.
What is the ultimate goal of a visualization?
to solve business problems
How often does the data preparation and modeling process repeat?
multiple times; it is iterative
What types of transformations can you make in Power Query?
delete unnecessary rows or column group data remove errors many other data quality tasks...
In Power Query, what does the query name determine
the model table name
How do you get to the Properties pane within the Model view?
by Ctrl+clicking or Shift+clicking items on the Model page.
What are the main data platform types?
relational databases nonrelational databases data streams file stores
What are two important steps when preparing to append queries
remove extraneous columns rename columns that will be appended to one another
What is an easy and popular way to quickly create visuals in Power BI?
apps
In a paginated report, once you have created a data source, what do you do next?
create a data set
What models are created in predictive analytics?
forecast models
What the the query diagnostics feature allow you to do?
determine what bottlenecks (if any) exist while: - loading and transforming your data - refreshing your data in Power Query - running statements in Query Editor and so on
How do you rename a column in Power Query
double click the column header > rename the column > click Enter
Can you change the size or arrangement of tiles when viewing them in a report or dashboard online?
no
What is a common reason for poor report performance?
too many visuals on the same page.
What is a visualization?
A visual representation of data
Write a DAX formula to create a calculated column for the numeric value of the year and month.
(YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])
What is a waterfall chart?
(also known as a bridge chart) 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.
For what transformations is query folding NOT possible?
- Adding an index column - Merging and appending columns of different tables with two different sources - Changing the data type of a column - Running complex DAX functions
What are two ways to create new content when getting data from the Power BI service?
- Files - bring in your reports, workbooks, or data from Excel, Power BI Desktop or CSV files - Databases - User Power BI Desktop to connect to data in Azure SQL Database and more
After you have prepared queries to be appended, how do you append them?
- Home tab - select the Append Queries drop-down list - select either Append Queries as New or Append Queries - in the dialog box, select the tables to append and click OK
What are the three different types of storage modes?
- Import - DirectQuery - Dual (Composite)
What are two ways to discover content when getting data from the Power BI service?
- My Organization - discover apps published by other people in your organization - Services -choose apps from online service that you use
In the PQE, what issues might you need to address in the column headers?
- one or more columns have the wrong headers - a header has a spelling error - the header naming convention is not consistent or user-friendly
How can Power BI apps help you manage assets?
- they are a valuable distribution method for your content - allow easier management for large audiences - allow for custom navigation experiences, and link to other assets within your organization to complement your reports
What types of data that are being generated each day in an organization?
- transactional data in a traditional database - telemetry data from services that you use - signals that you get from different areas like social media
What can you do in the Manage relationships window?
- you can create, edit, and delete relationships between tables and also autodetect relationships that already exist. - configure relationships between tables - configure table and column properties to ensure organization in your table structure.
By default, how many rows of data does the PQE examine?
1000
How can you read the M code behind the steps in the PQE?
Advanced Query Editor
In the Table view, where can you view table statistics?
At the bottom-left corner, in the status bar
With paginated reports, can data be collected from multiple sources?
Data can be collected from multiple data sources, including Microsoft Excel, Oracle, SQL Server, and many more.
This table type contains observational or event data values: sales orders, product counts, prices, transactional dates and times, and quantities.
Fact tables
In a data model diagram, what is the indication for the filter direction? The active relationship?
Filter direction is represented by the arrow head. And, a solid line represents an active relationship; a dashed line represents an inactive relationship.
What are the two most common way to display statistics about your datasets?
Histograms and bell curves
Does data classification enforce security policies?
No, because data protection does.
What can you do in the Navigator window?
Select a table or entity to preview its contents, to ensure that the correct data is loaded into the Power BI model
What does the Column quality feature show you?`
The percentages of data that is: valid in error empty
What is the most effective way to keep your Excel file and your dataset, reports, and dashboards in Power BI in-sync?
Using a cloud option such as OneDrive or SharePoint Team Sites.
In the PQE, when you merge queries, you are combining the data from multiple table into one based on what
a column that is common between the tables (or queries)
Give examples of common issues after importing data that require cleaning and preparation.
a column that should contain descriptions contains codes columns have errors columns have unexpected null values unexpected duplicate IDs in columns data in a column is not atomic (e.g. a single column contains the street, city, state, and zip code)
The combo chart visualization is what?
a combination of a column chart and a line chart that can have one or two Y axes.
How do you convert a slicer from a list to a dropdown?
at the top-right of the slicer, click the down-arrow, and then select Dropdown.
How do you remove a date hierarchy from a date field?
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
What does the SELECT statement allow you to do?
choose the specific fields that you want to pull from your database.
When file data is first loaded in the PQE, a data source might have a first row that contains what?
column names
What is built in to Power BI to allow you get data from a variety of sources?
connectors
What does filtering data before you bring it into Power BI do?
lets you focus on the data that matters to you
On what do effective cognitive analytics depend?
machine learning algorithms
In the PQE, what are two ways to combine tables?
merge append
How many lines of M code are typically written for each PQE step?
one or two
What is data modeling a critical factor of?
performance
Give examples of the types of data sets that are often used in time series analysis?
project planning, and stock movement datasets.
What happens when you mark the Date field in the table you Mark as date table?
removes autogenerated hierarchies
you should aim for __________ when designing your data models.
simplicity
When can query folding occur
when all transformations can be reduced to a single Select statement.
Where is most of a data scientist's time spent?
wrangling data feature engineering
Is it possible to edit or delete steps in the Query Editor?
yes
What do you do 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.
What is the ideal percentage of valid data?
100%
In what two places can you change the data type of a column?
A) In the PQE - two ways: 1) select the column > Transform tab > select Data Type 2) select the data type icon next to the column header and then select the correct data type from the list B) In the PBI Desktop Report view by using the column tools - NOTE: It is best to change the data type in the Power Query Editor before you load the data
How do you reset the contents of the Fields pane?
At the top-right corner of the Fields pane, collapse and then expand open the pane.
Which of the following options is not one of the four components in the Q&A visualization? The question box, where users enter their question and are shown suggestions to help them complete their question. A pre-populated list of suggested questions Automatic creation of a custom tooltip An icon that users can select to convert the Q&A visual into a standard visual. An icon that users can select to open Q&A tooling, which allows designers to configure the underlying natural language engine.
Automatic creation of a custom tooltip
What is the default behavior for many visuals? If you wish to change this behavior, for example, for mobile reporting, where do you go?
By default, many visuals in Power BI are responsive, which means that they change dynamically to display the maximum amount of data and insight, regardless of the screen size. As a visual changes size, Power BI gives priority to the data and makes small changes, such as removing padding or repositioning the legend, so that the data remains visible. When it comes to configuring visuals for mobile reports, Power BI does all the hard work for you. However, if you want to turn off this default responsiveness, you can do so in the General section of the visual's format settings.
In M code, how can later steps refer to earlier steps?
By referencing the previous step name within the new step
The combo chart lets you what?
Compare multiple measures with different value ranges. Illustrate the correlation between two measures in one visual. Identify whether one measure meets the target that is defined by another measure. Conserve space on your report page.
one of the most important tasks that a data analyst can perform in Microsoft Power BI.
Creating a great data model
When you add a bookmark, what elements are saved with it?
Current page Filters Slicers, including slicer type (for example, drop-down or list) and slicer state Visual selection state (such as cross-highlight filters) Sort order Drill location Visibility of an object (by using the Selection pane) Focus or Spotlight modes of any visible object
What are Excel and CSV file objects imported to create in Power BI?
Excel tables and CSV data are imported to create model tables, while an Excel workbook data model is transposed to create a Power BI model. In all cases, file data is imported into a model.
Describe fact and dimension tables. Give examples of each
Fact tables contain information about events such as sales orders, shipping dates, resellers, and suppliers. Dimension tables store details about business entities, such as products or time, and are connected back to fact tables through a relationship.
Give examples of the range of complexity of visuals.
From a single number that represents something significant to a gradient-colored map showing voter sentiment about a certain social issue or concern
Give examples of the range of complexity of datasets.
From a single table from a Microsoft Excel workbook to a combination of many different sources, which you can filter and combine to provide a unique collection of data for use in Power BI
How are pie, donut and treemap charts useful?
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.
What are the steps to connect to an Azure Analysis Services database?
Get data > select Analysis Services > enter a server address and database name > select either 'Import' or 'Connect live' >
How do you apply a theme to a dashboard?
Go to a dashboard, select the ellipsis (...) at the top of the dashboard, and then select Dashboard theme. This selection will open a window, where you can choose from a variety of themes, including Light (the default theme), Dark, Color-blind friendly, and Custom, where you can create your own theme. You can also upload your own JSON theme or download the current theme. For instance, if you select Custom, you can add your own background image, or you can change the background color, tile color, the opacity, or even the font color, as shown in the following figure.
How do you add a column using Power Query
Go to the PQE > Add Column tab > click Custom Column > create column in the dialog box
How do you view the objects associated with a service app?
Go to workspaces > select the workspace (e.g. GitHub) objects are displayed in the canvas area
What is the difference between grouping and binning?
Grouping is used for categories of data. Binning is similar to grouping, but it is used for grouping continuous fields, such as numbers and dates.
What are the five table in the PQE ribbon?
Home Tranform Add Column View Help
How do you change your connection settings from Power BI Desktop?
Home > Transform data > Data source settings > select the data source that you want to update > [optional] right-click a data source to view the available update options or you can use the update option buttons on the lower left of the window > select the update option that yhou need > change the settings > apply your changes
What are the five tabs in the PQE?
Home, Transform, Add Column, View, and Help
Why might you seek to address performance issues during development?
If you address performance issues during development, you will have a robust Power BI data model that will return better reporting performance and a more positive user experience. Ultimately, you will also be able to maintain optimized performance. As your organization grows, the size of its data grows, and its data model becomes more complex. By optimizing your data model early, you can mitigate the negative impact that this growth might have on the performance of your data model.
In a paginated report, how do you create a data set?
In PBI Report Builder: right-click Datasets in the Report View window and select Add Dataset. Ensure that the correct data source is selected. This action will run the query against the correct data source. 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.
What is one way to avoid creating calculated columns using DAX
In addition, one way to avoid using a calculated column is to use one of the X functions, such as SUMX, COUNTX, MINX, and so on.
When creating paginated report, how do you create and configure a data source?
In the report in PBI Report Builder: Right-click the Data Sources folder and select Add Data Source. On the General tab, name the data source. After naming the data source, choose the correct connection string by selecting the Build button. After you have selected Build, the Connection Properties screen appears. The properties on this screen will be unique for each data source. The following figure is an example of what you might see in the screen. The figure shows the properties of a SQL Server connection that you, the report author, will enter: Server name Database name A button for testing the connection Select OK to continue You can also enter username and password information on the Connection Properties screen, or you can leave it on the default setting and use your Windows credentials. Select OK again.
What is the Matrix visualization?
It looks similar to the table visualization; however, it allows you to select one or more elements (rows, columns, values) to cross-highlight other visuals on the report page
What is the size limitation of a dashboard?
It must fit on a single page
In diagnostic analytics, what is further investigated to discover why events improved or worsened outcomes?
KPIs
What is used to to help track the success or failure of key objectives?
Key Performance Indicators (KPI)
What language is used in the PQE?
M
Does PQ change the data in the original source?
No. The PQE only makes changes to a particular view of your data. So, you can feel confident about changes that are being made to your original data source.
Give an example of replacing null values in a column.
Occasionally, you might find that your data sources contain null values. For example, a freight amount on a sales order might have a null value if it's synonymous with zero. If the value stays null, the averages will not calculate correctly. One solution would be to change the nulls to zero, which will produce the more accurate freight average. In this instance, using the same steps that you followed previously will help you replace the null values with zero.
In the PQE, where can you see a list of your steps?
On the right side of the screen, in the Query Settings pane, along with query's properties.
Where is the Q&A located?
PBI service go to your dashboard in Power BI service. Along the top ribbon is the Ask a question about your data search box. After selecting this box, you will be routed to the Q&A page
When creating paginated reports, what should you pay attention to regarding layout?
Pay attention to the height and width of the report page. Verify that the report is not running off the page when the report renders for the user.
What tool makes data analysis accessible and simplifies the data analysis process?
Power BI
Which feature lets you explore your data in your own words by allowing you to ask natural language questions and then providing you with answers to those questions.
Q&A
What Power Query technique takes advantage of good performance at the data source?
Query Folding
In the Query Editor, where can you can rename steps, delete steps, or reorder the steps as you see fit? .
Query Settings pane
One way to increase the likelihood that the user will read all column names in a data model.
Remove unneeded columns to provide a more manageable number
What are some of the performance optimization techniques?
Review the performance of measures, relationships, and visuals. Use variables to improve performance and troubleshooting. Improve performance by reducing cardinality levels. Optimize DirectQuery models with table level storage. Create and manage aggregations.
In PQ, how do you values from a related table?
Right click the double-arrow drop-down in the column header > Select the columns you want > Optionally, deselect the "Use Original Column Name as Prefix" checkbox Click OK
How do you delete a relationship (an inactive one, for example)?
Right-click the inactive relationship between the Salesperson (Performance) and Sales tables, and then select Delete. When prompted to confirm the deletion, click Delete.
What is connecting to an Azure Analysis Service database similar to?
SQL Server
What are the two types of external-hosted models?
SQL Server Analysis Services Azure Analysis Services
In a paginated report, how to you add a visual to the design surface?
Select the Insert tab from the ribbon at the top to see your options for adding a visual. Then, depending on the visual added, from the Report data window, drag fields from the dataset to the visual on the design surface.
In PQ, how do you replace values in a column?
Select the column header > Right-click and select "Replace Values..." > Enter the value to replace > Enter the value to replace with > Click OK
How do you use the Cross-report drill through feature?
Select the source report in the Power BI service, and then select a visual that uses the Drill through field in the way that you specified when you set up the target page. Right-click a data point on the visual, select Drill through, and then select the drillthrough target. Notice that the Cross-report drillthrough targets are formatted as Page name [Report name].
Name examples of the range of software applications that organizations use to support their operations.
SharePoint OneDrive Dynamics 365 Google Analytics more...
What are the steps to get to format a visual?
Start by selecting the visualization on the canvas, and then select the Format button (paint roller icon) to display the Format pane.
What does SQL stand for?
Structured Query Language
What happens to data after it has been analyzed?
The data is then crafted into a story through reports for analysis to support the critical decision-making process.
In the Performance Analyzer log, information is recorded about what categories of tasks?
The log information for each visual shows how much time it took (duration) to complete the following categories of tasks: 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. Visual display - The time it took for the visual to render on the screen, including the time required to retrieve web images or geocoding. 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.
What visual should be used to display outliers? The line chart is best-suited to display outliers. The scatter chart is best-suited to display outliers. The clustered column chart is best-suited to display outliers.
The scatter chart is best-suited to display outliers.
What is the difference between Distinct value count and Unique value count?
The total number of different values found in a given column versus The total number of values that only appear once in a given column.
What are the characteristics of tiles used to display streaming data?
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.
Is phone view customizable for each person who uses the dashboard?
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.
When creating a bi-directional relationship, what does checking the Apply Security Filter in Both Directions checkbox do?
This setting will ensure that bi-directional filtering is applied when row-level security is being enforced. You will configure a security role in the next exercise.
Describe how to create a many-to-many relationship.
To create this relationship, go to Manage Relationships > New. In the resulting window, create a relationship between the Customer ID column in CustomerTable and AccountTable. The cardinality is set to many-to-many, and the filter type is in both directions. Immediately, you will be warned that you should only use this type of relationship if it is expected that neither column will have unique values because you might get unexpected values. Because you want to filter in both directions, choose bi-directional cross-filtering.
How do you create a key influencers visual? Give an example.
To establish the key influencers, first add the Key influencers visual to your report by selecting the Key influencers icon on the Visualization pane. Then, populate the visual with the metrics that you want to measure. In this case, you're interested in the logged tickets, so in the Analyze field well, you will add the Sales field, and in the Explain by field well, you will add the City and Product Name fields. The visual updates according to the fields that you added and then shows the influence that those fields have on your data. You can now use the What influences... drop-down list to see what caused the data to decrease or increase. The previous image shows that the sales for California are likely to be USD 130,700.00 higher than sales in other cities.
How can you add more data points to the Tooltip displayed? What should you be aware of?
To expand on the data points that are displayed in the default tooltip, you can drag a field (value) from the Fields panel into the Tooltips bucket. However, you should not add many more fields to the tooltips because adding too many fields can introduce performance issues and slow down your visuals.
How might you organize fields in a folder in Model view?
To organize columns into a display folder, in the Fields pane, first select the Background Color Format column. While pressing the Ctrl key, select the Font Color Format. In the Properties pane, in the Display Folder box, enter Formatting. In the Fields pane, notice that the two columns are now inside a folder.
What is the benefit of using a report tooltip?
To provide additional detail that is specific to the context of the data that is being hovered over.
Describe a scenario where you need to connect to an Azure Analysis Services database.
To resume the scenario, Tailwind Traders uses Azure Analysis Services to store financial projection data. You've been asked to compare this data with actual sales data in a different database.
How do you turn off the auto date/time feature?
To turn off auto/date time, click the File ribbon tab to open the backstage view. At the left, select Options and Settings, and then select Options. In the Options window, at the left, in the Current File group, select Data Load. In the Time Intelligence section, uncheck Auto Date/Time. Click OK.
What is UPN an abbreviation for?
User Principal Name
How can Power BI be used by businesses?
User can quickly gain insights into their data by using visuals and metrics directly from their desktop and then publish those insights to dashboards so that others can find needed information
What does the Autodetect feature do?
When you load your data into Power BI, the Autodetect feature will help you establish relationships between columns that are named similarly.
Once you have created a common date table using M, and have converted the list to a table, what do you do next?
Your first task is to change the column type by selecting the icon next to the name of the column and, in the resulting drop-down menu, selecting the Date type.
nine times out of ten, poor performance is a direct result of what?
a bad data model, bad Data Analysis Expressions (DAX), or the mix of the two.
Give examples of visualizations
a chart a color-coded map a card a line-chart a bar chart a pie chart
What is a dataset?
a collection of data that Power BI uses to create visualizations
What is a Power BI app?
a collection of preset, ready-made visuals and reports that are shared within an organization
What is available to improve the process of creating reports with screen readers?
a context menu is available. The menu allows you to move fields in the well up or down in the Fields list. The menu also allows you to move fields to other wells, such as Legend, Value, or others.
What does the CALENDAR() function return?
a contiguous range of dates based on a start and end date that are entered as arguments in the function.
In Model view, how can you tell a relationship is inactive?
a dashed relationship exists
What is a table visual?
a grid that contains related data in a logical series of rows and columns. it 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.
To conduct a time series analysis in Power BI, which visualization types are suitable for displaying trends and changes over time?
a line chart, area chart, or scatter chart. You can also import a time series custom visual into Power BI Desktop from Microsoft AppSource.
What does the funnel visualization display?
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.
What does creating a dataset based on Excel workbooks or CSV files result in the automatic creation of?
a model
What does the Column profile give you?
a more in-depth look into the statistics within a selected column.
What does the poor performance of a report lead to?
a negative user experience.
heuristic
a simple thinking strategy that often allows us to make judgments and solve problems efficiently; usually speedier but also more error-prone than algorithms enabling a person to discover or learn something for themselves.
What do dashboards allow report consumers to create?
a single artifact of directed data that is personalized just for them.
What is SQL
a standardized programming language used to manage relational databases and perform various data management operations
Each time you shape data in Power Query, what is created?
a step in the Power Query process
What do you do 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.
What are you doing when you merge queries?
adding columns from one table or query to another table or query
What happens each time a PBI query connects to it's data source?
all query steps in the PQE are applied
Effectively, what is a model developed in Power BI Desktop?
an Analysis Services tabular model
How do prescriptive analytics work?
analyze past decisions and events, and estimate the likelihood of different outcomes
What does time series analysis involve?
analyzing a series of data in time order to identify meaningful information and trends and make predictions.
When building dashboards, what should you consider to ensure that the same theme is applied to your dashboards to create a cohesive picture.
applying a theme
What do the results of report performance analysis identify?
areas for improvement and highlight items that you need to optimize.
What can DAX variables be useful for?
ariables can be useful for simplifying the formula logic, and more efficient when an expression needs to be evaluated multiple times within the formula (which will be the case for the YoY growth logic).
In the PQE, how is the ability to combine queries powerful?
because it allows you to append or merge different tables or queries together.
What does the AI Insights feature allow you to do?
connect to a collection of pretrained machine learning models that you can apply to your data to enhance your data preparation efforts.
With paginated reports, once you have created the basic report, what do you do next?
create and configure a data source
What feature helps the dashboard owner raise security awareness to viewers of a dashboard so that they know what level of security should be considered when viewing or sharing a dashboard.
data classification
This table type is used to filter and group the data in fact tables.
dimension
What do data analysts need to do to ensure that the right people have access and that you are not leaking data past the correct stakeholders?
ensure that items are secure
What is modeling data about?
establishing and maintaining relationships so that you can effectively visualize the data in the form that your business requires
How do you 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.
How do you select a report theme?
from the View tab. Select the Expand button to view all available themes and related options. Select any theme and it will automatically apply across the whole report.
In the Visualizations pane, how can you get the visual type.
hover the cursor over each icon
In M code, what is the statement in the last step?
in
How do you open the Power Query Editor?
in PBI Desktop, select the Transform data option on the Home tab
How do you rename a query?
in the Query Settings pane > in the Name box > replace the text > press ENTER
In a scatter chart, where do you put your fields of interest?
in the X Axis and Y Axis sections, respectively. In this case, the Orders Shipped field is on the x-axis, and the Qty Orders field is on the y-axis.
What does the following measure do? IF( ISINSCOPE(Region[Region]) || ISINSCOPE(Region[Country]), DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS( Region[Region], Region[Country] ) ) ) ) HINT: the measure name is Sales % Group
it calculates sales as a percentage of group it only returns a value when a region or country is in scope.
Give an example of a parent-child hierarchy.
manager -> employee
In the PQE, where can you see a list of available queries (tables)?
on the left side, in the Queries pane
How many relationships can be active between tables at one time?
one
What does query folding do?
optimizes data retrieval according to how the data is being used later.
On a paginated report, what do you create if you want a user to be able to affect which data is retrieved from the data source?
parameters
In the PQE, what can you do in the Add Column tab.
perform additional tasks associated with adding a column, formatting column data, and adding custom columns
What data analytics category helps answer questions about what will happen in the future?
predictive analytics
When a query in the PQE has column names in the first table row, what do you need to do?
promote the first table row into column headers
Name some things you can do in Power Query Editor.
rename columns or tables change text to numbers remove rows set the first row as a header much more...
What does the process of identifying outlier involve?
segmenting your data into two groups: one group is the outlier data and the other group is not.
In the PQE, after you have completed all steps to clean and transform your data, what do you do to close the PQE and apply your changes to your data model?
select Close & Apply
What can you do if you need to revert the changes that you make?
select the Revert to default option at the bottom of each section in the Format pane.
To test a paginated report in PBI Report Builder, what do you do?
select the Run button from the Home tab in the ribbon
What can you do in the visual Data colors section?
set the colors that you want to use for the data values in the visual. You can use different colors for different fields, but always try to be consistent when it comes to selecting those colors. It is best to use the same color scheme throughout the report.
When you import data from multiple sources into Power BI Desktop, why might you want to change some of these names?
so that they are in a consistent format, easier to work with, and more meaningful to a user.
Distinguish the terms dataset and model
sometimes the terms datasets and models are used interchangeably. Generally, from a Power BI service perspective it's referred to as a dataset, and from a development perspective it's referred to as a model. In the context of MS's documentation they mean much the same thing.
What are the two varieties of bar and column charts?
stacked or clustered format. The stacked format will stack the information items on top of each other.
What are inference not?
structured queries based on a rules database
What will your organization likely have regarding report design?
style guidelines you'll have to adhere to particular color scheme and font.
What does the data model view show?
tables and their relationships
What do diagnostic analytics techniques use to discover the cause of events?
the findings of descriptive analytics
What are tiles? Where can they be sourced from?
the individual report elements, or snapshots, of your data that are then pinned to a dashboard. Tiles can be sourced from a multitude of places including reports, datasets, other dashboards, Microsoft Excel, SQL Server Reporting Services, and more.
What does a well-built dashboard capture?
the main, most important highlights of the story that you are trying to tell. they are comprised of a canvas of interactive tiles, or report elements, that tell a data story.
What does a Power BI dataset leverage (except for streaming datasets)?
the mature modeling technologies of Analysis Services
What should you keep in mind about the number of visuals on a report page?
the more visuals you use in your report, the more that they impact the performance of your report.
What do the The pie chart, donut chart, and Treemap visualizations show you?
the relationship of parts to the whole by dividing the data into segments.
What is the name of all of the buttons at the top of the PQE
the ribbon
What is a bubble map?
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.
How do cognitive services make sense of previously untapped data sources?
using several natural language processing concepts
What do cognitive analytics help you learn?
what might happen if circumstances change and determine how you might handle these situations
If you pin a live page to dashboard, when are any changes you make to the report shown on the dashboard?
when the page is refreshed 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.
What questions do prescriptive analytics help answer?
which actions should be taken to achieve a goal or target.
Can you use Data Analysis Expressions (DAX) measures and conditional formatting to create dynamic alt text?
yes
If you make changes to a visual in a report, are the changes reflected on the dashboard
yes
If you pin a live page to dashboard, will all your slicers and filters still work and will visuals have the same data as in the report?
yes
Where do you go to visualize streaming data? How is this done?
you need to create a new tile directly on an existing or new dashboard. To complete this task, go to and open a dashboard and then select Add Tile. The following window will appear, where you can select Custom Streaming Datasets under Real-Time Data. Select Next, which will redirect you to the following window where you can choose an existing streaming dataset, or get new streaming datasets, as shown in the following image. After you have selected the new dataset, select Next, enter the details for your streaming dataset, and then add a new streaming dataset tile. Streaming dataset tiles can be in the form of line charts, stacked bar charts, cards, and gauges and are formatted similarly to any other kind of tile.
When you publish a report, what does Power BI Desktop packages and upload to the Power BI service?
your report and data, including all your visualizations, queries, and custom measures
Describe the self-learning feedback loop of cognitive analytics
1) draw inferences from existing data and patterns 2) derive conclusions based on existing knowledge bases 3) add these findings back into the knowledge base for future inferences
In the PQE ribbon, what tab is used to add a column, format column data, and add custom columns
Add Column
In PBI-D, where can you see the data set you are connected to?
At the bottom-right corner, in the status bar.
Why should you use calculated columns sparingly?
Calculated columns are materialized in the .pbix Power BI file extension, meaning that each time you add a calculated column, you are increasing the size of the overall file. Having too many calculated columns will slow performance and will cause you to reach the maximum Power BI data size sooner.
What functions does Power BI have that help you conduct statistical analysis?
Data Analysis Expressions (DAX) functions visuals such as - histograms - bell curves - advanced analytics visuals and statistical programming languages such as Python and R.
When creating paginated report, what should report authors ask themselves to test the appropriate delivery format and ensure that the report is rendering correctly in that format?
Does the user want the report sent to them in an email message? Does the user want the report in a printable format? Does the user read the report in a web browser?
Name and describe the two different types of row-level security (RLS) roles.
Dynamic - Dynamic roles filter by the report user, while static roles apply the same filters for all users assigned to the role. Static - Static roles apply the same filters for all users assigned to the role
What are the two options for hierarchy drill down and up?
Expand all down one level in the hierarchy button was selected, the hierarchy was expanded down by one level. If you were to select the button again, the visual would update to display the next lower level in the hierarchy, and so on. Another navigation option is the Go to the next level in the hierarchy button. When you select either hierarchy option, select the Drill up button to move back up the hierarchy.
When you apply a filter to a column, what is the name of the new step in the Applied Steps pane?
Filtered Rows
How can you tell which direction a filter will flow? In what direction do you typically want your cross-filter arrows to point?
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.
How do you sync a slicer across pages?
Go to the Overview page, and then select the Year slicer. On the View ribbon tab, from inside the Show Panes group, click Sync Slicers. In the Sync Slicers pane (at the left of the Visualizations pane), in the second column (which represents syncing), check the checkboxes for the Overview and My Performance pages.
From the Power BI service, how do you update the dataset for an app?
Go to the workspace > under the dataset object, select the schedule update icon for the data set to update > use the menu the Datasets tab is selected on the Settings page that appears > In the right pane, select the arrow next to 'Schedule refresh' to expand that section > the 'Settings' dialog box appears on the canvas, letting you set the update settings that meet your needs
What colors should you avoid putting together?
Green and red Green and brown Blue and purple Green and blue Light green and yellow Blue and grey Green and grey Green and black
How do you 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.
What is the default storage mode for creating new reports?
Import
What are the three model modes?
Import DirectQuery Composite
Can data classification be turned on or off? Is there a default classification type?
It can be turned on and off in accordance with your organization's business needs. All dashboards are defaulted to a certain classification type
In the Power Query Editor, how do you select columns to keep from a sorted list of all columns?
Manage Columns group, click the Choose Columns icon. In the Choose Columns window, to uncheck all columns, uncheck the (Select All Columns) item. To include columns, check the columns. Click OK
Once a tile is pinned to a dashboard, can the filter context be changed?
No
Where can you edit specific column and table properties?
On the Model tab
In the PQE, how do you pivot columns
On the Transform tab > select Transform > Pivot Columns
How can have PBI Desktop choose a visual for you?
PBI Desktop automatically selects a visualization for you, depending on the data type of the fields that you selected.
What happens in PQ each time the query connects to the data source?
PQ automatically applies your steps.
Why are the DAX 'X' functions called iterators?
These functions are called iterator functions because, though they are used in measures, they iterate over the individual rows to do their calculations.
How do you access page settings?
To access the page settings, select the white space on your report canvas to open the Format pane. You can then configure the following settings to suit your needs: Page information, Page alignment, Page size, Wallpaper, Page background, and Filter pane.
Which tab in the PQE is used to turn on or off the display of certain panes or windows, and to display the advanced editor?
View
What questions does descriptive analytics help answer?
What has happened based on historical data?
In the PQE, a merge is similar to what in SQL?
a JOIN clause
What is a query parameter?
a placeholder for a value that will be passed in at query execution time.
What can dashboards contain?
a single page from a report or a collection of visuals
In the labs, you will connect to the SQL Server database by using localhost. Why isn't this a recommended practice, however, when creating your own solutions.
because gateway data sources cannot resolve localhost.
How do you pin a visual to a dashboard?
by hovering over the visual. In the visual header, select the Pin Visual icon, as shown in the following image. After you have selected the icon, a window will appear, where you can choose to pin this visual to a new or existing dashboard. 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.
How can many of the tasks in the PQE tabs also be found?
by right-clicking on a column or other object in the center pane of the PQE
How can you manually add a date hiearachy?
by right-clicking the year, month, week, or day columns in the Fields pane and then selecting New hierarchy.
How do you access the query reduction settings?
by selecting **File > Options and settings > Options, then scrolling down and selecting the Query reduction option.
What can you do in the visual Data labels section?
change fonts, size, and colors for all labels in the visual. Try to use solid colors so the labels are clearly visible. For example, if the background is white, use a black or dark grey color to display your labels.
What are good qualities of titles?
clear, concise, descriptive titles for your visuals and report pages. Avoid using acronyms or jargon that new users or users who are external to your organization will not understand.
In the PQE, how can you see the result of all steps previous to and including the selected one
click on the step in the Queries Setting pane, under Applied Steps
In a visual, how do you expand all down one level in a hierarchy?
click the forked-double arrow icon
What does the Home tab in the PQE contain?
common query tasks, including the first step in any query, which is Get Data.
In a paginated report, consider the scenario where you you want the report user to chose sales data from between a begin date and an end date. What do you need to create?
create two parameters and then modify the dataset query to include those parameters in the WHERE clause of the query.
What does the auto date/time feature do? When should you use it?
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. The Auto date/time option 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.
What is an indicator for a low level of cardinality?
distinct count is low
In the PQE, when might you need to remove some of top rows?
if they are blank if they contain data that you do not need in your reports
This aspect of data modeling requires constant effort.
keeping it simple, comprehensive, and accurate
Can you model the data coming a streaming dataset?
no
Given an employee, manager, Path, and Level columns, how do you create a hierarchy?
on the Fields pane, as you did previously. Right-click Level 1, because this is the first hierarchy level, and then select New Hierarchy. Then, drag and drop Level 2 and Level 3 into this hierarchy, etc. You have now successfully flattened a hierarchy so that you can view individual levels.
How do you apply the changes you made in the Query Editor to the data model in Power BI Desktop? .
on the Power Query Editor File menu, select Close & Apply
Once data has been loaded into Power BI Desktop, how can you update the file connection?
one of three places: - Data source settings - Query settings - Advanced editor
The multi-row card visualization displays what?
one or more data points, with one data point for each row.
column(s) that identify each unique, non-null data row.
primary keys
What can you do with the steps in the PQE
reorder delete modify
How do you use drill through?
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.
Paginated reports are ideal for creating what?
sales invoices, receipts, purchase orders, and tabular data.
What can you do in the Advanced Editor?
see the code that Power Query Editor is creating with each step, and create your own shaping code
How do you change the number of rows that the PQE examines?
select the profiling status in the status bar and select Column profiling based on entire data set
What feature helps you determine data anomalies
the Column Distribution feature
telemetry
the process of sending and receiving data wirelessly
When using time intelligence, what feature should you turn off? What should you do instead?
turn off the auto date/time to get rid of the auto-generated hierarchy. instead, create a date table
In the PQE, what can you do in the View tab?
turn on or off the display of certain panes or windows, and to display the advanced editor.
What are characteristics of simple table structure in your data model?
- Be simple to navigate because of column and table properties that are specific and user-friendly. - Have merged or appended tables to simplify the tables within your data structure. - Have good-quality relationships between tables that make sense.
When would you consider a dashboard versus a report?
- 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.
When importing data into Power BI, what factors might results in import errors
- Power BI imports from numerous data sources. - Each data source might have dozens (and sometimes hundreds) of different error messages. - Other components can cause errors, such as hard drives, networks, software services, and operating systems. - Data can often not comply with any specific schema.
What can you do in the Advanced tab of the Properties pane of the Model view?
- Sort by a specific column. - Assign a specific category to the data. - Summarize the data. - Determine if the column or table contains null values.
Describe the role of the Data Engineer.
- provision and set up data platform technologies on-premises and in the cloud - manage and secure the flow of structured and unstructured data from multiple sources - ensure that data services securely and seamlessly integrate across data services also, they use on-premise and cloud data service and tools to: - ingest - egress - transform - validate - clean ...data from multiple sources collaborate with business stakeholders to identify and meet data requirements design and implement solutions does not include DBA responsibilities such as looking after a database and the server where it's hosted, and overall operational data management adds value by "wrangling" data works closely with the data analyst in: - ensuring access to the data - optimizing data models - providing connectivity to and maintenance of a modern data warehouse or data lake
What is the file extension for a Power BI Desktop file?
.pbix
What are two ways to manage, create, edit, and delete relationships between tables?
1) in the Model tab, drag and drop, click and edit 2) in the Home ribbon, go to Relationships > click Manage relationships
What is the 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
What is a dashboard? A canvas of report elements that can be built in Power BI Desktop A canvas of report elements that can be built in Power BI service Dashboards can be built by using visuals that are developed with an underlying data source. The canvas in which you can view the data model of a report
A canvas of report elements that can be built in Power BI service
In PBI Report Builder, what is the difference between a data source and a data set? Where does the data for a data set always reside?
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 does CALCULATE do?
A method of creating a DAX measure that will override certain portions of the context that are being used to express the correct result.
Why does the performance optimization process involve minimizing the size of the data model and making the most efficient use of the data in the model?
A smaller sized data model uses less resources (memory) and achieves faster data refresh, calculations, and rendering of visuals in reports.
What do active relationships do? When might you have inactive relationships in a data model? How can you use inactive relationships between tables?
Active relationships will propagate filters. It's possible to mark a relationship as inactive so filters don't propagate. Inactive relationships can exist when there are multiple relationship paths between tables. In which case, model calculations can use special functions to activate them.
Once you have created a common date table using M, converted the list to a table, changed the column data type to a date, what do you do next?
Add columns you can add columns for year, months, weeks, and days. Go to Add Column, select the drop-down menu under Date, and then select Year, as shown in the following figure.
How do you add a Decomposition tree visual. What are AI splits?
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, as illustrated in the following image. The visual updates according to the fields that you added and displays the analysis summary result. In this case, the value of sales is USD 13,499,680.00. 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. At the top of the list of dimensions that you added are two additional options that are marked with lightbulb icons. These options are referred to as AI splits, and they'll automatically find high and low values 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. The following image illustrates the result of selecting the High value AI split.
What accessibility features do you need to configure?
Alt text Tab order Titles and labels Markers Themes
What are some data analysis techniques?
Analysis techniques include descriptive and inferential analyses and are chosen based on the research objectives, questions, or hypotheses and the types of research instruments used.
Which data analytics task is the important step of understanding and interpreting the information that is displayed on the report?
Analyze
How do you add a bookmark?
Before adding a bookmark, go to the View tab in the ribbon and select Selection. The displayed Selection pane includes a list of all items on your page, along with an eye icon that indicates the items that are currently visible. You can rename the items in the list by double-clicking them so you clearly know which one is which. Now you are ready to add the bookmark. On the View tab, select Bookmarks. On the Bookmarks panel that displays, select Add. It's good practice to rename the new bookmark so its purpose is clear; this notion is especially true if you plan on adding multiple bookmarks. To rename a bookmark, double-click the bookmark and enter the new name. In this example, you want to change the bookmark name from Bookmark 1 to Variance Chart because the Variance chart is the main focus of the page, as shown in the following image. In the Selection pane, the Variance chart displays and the other visuals are hidden.
Which features make your report more compelling, interactive, and simpler for users to navigate?
Bookmarks, Buttons, and Selections features in PBI-D
What does marking a table as the date table do?
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.
How can you analyze performance of each of your report elements?
By using performance analyzer
What does clustering allow?
Clustering allows you to identify a segment (cluster) of data that is similar to each other but dissimilar to the rest of the data. The process of clustering is different to that of grouping, which you accomplished previously. The Power BI clustering feature allows you to quickly 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. For example, you might want to look for patterns in your sales data, such as the behavior of customers overall. You can segment the customers into clusters according to their similarities, such as age or location.
After creating a common date table using M code in Power Query, what do you need to do? How do you do that?
Convert the list to a table. Go to the Transform tab on the ribbon and select Convert > To Table.
Performance analyzer highlights potential issues but does not tell you what needs to be done to improve them. You might want to conduct further investigation into why this measure takes so long to process. What can you use to investigate your queries in more detail?
DAX Studio
What tool can you use to analyze your queries in more detail?
DAX Studio, which is a free, open-source tool that is provided by another service.
Where can you configure and set data alerts? Data alerts can be set only in Power BI service on specific visuals such as KPI cards, gauges, and cards. Data alerts can be set in both Power BI service and Power BI Desktop on any kind of visual. Data alerts can be set in Power BI service on any kind of visual. Data alerts can be set only in Power BI Desktop on specific kinds of visuals such as KPI cards and gauges.
Data alerts can be set only in Power BI service on specific visuals such as KPI cards, gauges, and cards.
A good data model offers the following benefits:
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.
What are the basic building blocks in Power BI?
Datasets Reports Visualizations Dashboards Tiles
Which visual automatically aggregates your data and lets you drill down into your dimensions so that you can view your data across multiple dimensions?
Decomposition tree you can use it for improvised exploration and conducting root cause analysis.
Describe a 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.
What are some region specific best practices for report writing?
English speakers read top-down, left-to-right. Putting important information, like totals, at the top of the report will highlight that information for English speakers. Europeans read dates differently than users from the US. Localize data formats to the appropriate target user.
What things can be done to minimize the size of the data model and make the most efficient use of the data in the model?
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.
What is the name of the process by which descriptive analytics evaluate data?
Exploratory Data Analysis (EDA)
Give an example of how data granularity might have an impact when you are building relationships between tables in Power BI.
For example, consider that you are building reports for the Sales team at Tailwind Traders. You have been asked to build a matrix of total sales and budget over time by using the Calendar, Sales, and Budget tables. You notice that the lowest level of time-based detail that the Sales table goes into is by day, for instance 5/1/2020, 6/7/2020, and 6/18/2020. The Budget table only goes to the monthly level, for instance, the budget data is 5/2020 and 6/2020.These tables have different granularities that need to be reconciled before you can build a relationship between tables. A relationship between Budget and Calendar is missing. Therefore, you need to create this relationship before you can build your visual. Notice that if you transform the Year and Month columns in the Budget table, you can match the format of the Date column in the Calendar table. Then, you can establish a relationship between the two columns. To complete this task, you will concatenate the Year and Month columns and then change the format.
Give an example of how you might use DAX Studio to improve performance on a query that is slow.
For example, select Copy Query to copy the calculation formula onto the clipboard, then paste it into Dax Studio. You can then review the calculation step in more detail. In this example, you are trying to count the total number of products with order quantities greater than or equal to five. Count Customers = CALCULATE ( DISTINCTCOUNT ( Order[ProductID] ), FILTER ( Order, Order[OrderQty] >= 5 ) ) After analyzing the query, you can use your own knowledge and experience to identify where the performance issues are. You can also try using different DAX functions to see if they improve performance. In the following example, the FILTER function was replaced with the KEEPFILTER function. When the test was run again in Performance analyzer, the duration was shorter as a result of the KEEPFILTER function. Count Customers = CALCULATE ( DISTINCTCOUNT ( Order[ProductID] ), KEEPFILTERS (Order[OrderQty] >= 5 ) ) In this case, you can replace the FILTER function with the KEEPFILTER function to significantly reduce the evaluation duration time for this query. When you make this change, to check whether the duration time has improved or not, clear the data cache and then rerun the Performance analyzer process.
It's good practice to change uncommon or unhelpful query names to names that are more obvious or that the user is more familiar with. Give an example of a table and a view.
For instance, if you import a product fact table into Power BI Desktop and the query name displays as FactProductTable, you might want to change it to a more user-friendly name, such as Products. Similarly, if you import a view, the view might have a name that contains a prefix of v, such as vProduct. People might find this name unclear and confusing, so you might want to remove the prefix.
From a user's perspective, what is poor performance characterized by?
From a report user's perspective, poor performance is characterized by report pages that take longer to load and visuals taking more time to update. This poor performance results in a negative user experience.
Give a range of extensibility of Power BI
From quick insights from an Excel workbook to an enterprise-grade data model with real-time analytics and custom development.
Why would define a higher level of granularity be advantageous?
Generally, the fewer the records that you are working with, the faster your reports and visuals will function. This approach translates to a faster refresh rate for the entire dataset, which might mean that you can refresh more frequently.
How do you create a connection to a NoSQL database?
Get data > More... > select the 'Azure' category > select 'Azure Cosmos DB' > select 'Connect' > on the 'Preview Connector' window, select 'Continue' > enter you database credentials (Azure Cosmos DB account endpoint URL. You can get the URL from the Keys blade of your Azure portal) or enter the database name, collection name, or use the navigator to select the database and collection to identify the data source. > [optional] if you are connecting to an endpoint for the first time, 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 do you enable the cross-report drill through feature in PBI-D?
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. The Cross-report drillthrough feature can also be enabled/disabled in the Report settings in Power BI service.
In PQ, how do you create custom column
Go to the Add Column tab > click Custom Column > enter a new column name > enter the desired formula > click OK
How do you create an alert on a dashboard tile?
Hover the cursor over the tile, click the ellipsis, and then select Manage Alerts. In the Manage Alerts pane (located at the right), click Add Alert Rule. In the Threshold box, replace the value with the value you want. This configuration will ensure you're notified whenever the tile updates to a value above that threshold value. At the bottom of the pane, click Save and Close.
As a best practice, which storage mode should you use when connecting to data?
Import
How do you conduct time series analysis in Power BI?
In Power BI, you can use visuals to view how your data is progressing over time, which in turn allows you to make observations, such as if any major events disrupted your data.
In the Model view, where can you set a field to summarize values? What is the default behavior for numeric columns?
In the Advanced group (you may need to scroll down to locate it), in the Summarize By dropdown list, select Average. By default, numeric columns will summarize by summing values together. This default behavior is not suitable for a column like Unit Price, which represents a rate. Setting the default summarization to average will produce a useful and accurate result.
In the Date view, how would sort a Month column by the MonthKey column?
In the Fields pane, ensure that the Month field is selected (when selected, it will have a dark gray background). On the Column Tools contextual ribbon, from inside the Sort group, click Sort by Column, and then select MonthKey.
How do you view all table fields in Report view?
In the Fields pane, right-click an empty area, and then select Expand All.
How would you create a quick measure to calculate profit?
In the Fields pane, right-click the Sales table, and then select New Quick Measure. In the Quick Measures window, in the Calculation dropdown list, from inside the Mathematical Operations group, select Subtraction. In the Fields pane, expand the Sales table. Drag the Sales field into the Base Value box. Drag the Cost field into the Value to Subtract box.
Where can you view how many distinct and unique items were in each column in the data?
In the PQE, the Column distribution option on the View tab
In the Model view, how might you use built-in categorizations for a field?
In the Properties pane, expand the Advanced section, and then in the Data Category dropdown list, select the category.
In the Model view, where can you format field setting such as the thousands separator or percentage format?
In the Properties pane, from inside the Formatting section,
How do you add a description to a field in the Model view?
In the Properties pane, in the Description box, enter: Based on standard cost Descriptions can be applied to table, columns, hierarchies, or measures.
Other than using Manage relationships, how can you create a relationship?
In the model diagram, you can drag and drop columns to create a new relationship.
What influences customer purchases?
Internal Influences Internal influences are the factors that affect customer purchases that come from the customers themselves. Customer wants, needs, desires, and preferences are internal influences that drive purchasing decisions. Hunger, health reasons, boredom, or a desire to travel are all internal influences. Customers make buying decisions based on their gender, age, location, education level, and family, among many other factors. Less obvious influences that may be affecting your customers can include mood, social status, time of day, and cognitive functioning. What are the internal influences that impact your customers? Identifying these internal influences will enable you to reach customers who are already looking to use a product or service like the one you offer. External Influences External influences are the things that influence buying habits outside of individual wants and needs. Billboards, Facebook ads, celebrities, customer testimonials, and current trends are all external factors that influence customer buying decisions. Signage and branding are also external influences that drive buying decisions. Does your business's brand align with your target customer? For example, a cutesy brand with doodles is more fitting for a preschool than a financial services firm. Use external influences to encourage customers to make a purchase—identify what influences your target customers and use these external forces to get more sales.
What does crafting reports that tell a story do?
It helps business leaders take action on the data.
For what reasons might your organization need to use the DirectQuery data connectivity mode?
It is suitable in cases where data changes frequently and near real-time reporting is required. It can handle large data without the need to pre-aggregate. It applies data sovereignty restrictions to comply with legal requirements. It can be used with a multidimensional data source that contains measures such as SAP Business Warehouse (BW).
What is the result of the aggregation process in data modeling?
It reduces the table sizes in the data model, allowing you to focus on important data and helping to improve the query performance.
What does a calculated column produce?
It will produce one value for each record in the table.
What accessibility features are built in to PBI-D, so you don't need to configure them?
Keyboard navigation Screen-reader compatibility High contrast colors view Focus mode Show data table
What function retrieves the last date in the current filter context? Write a formula to get the total warehouse inventory count for the last date in the current filter context?
LASTDATE Last Inventory Count = CALCULATE ( SUM ( 'Warehouse'[Inventory Count] ), LASTDATE ( 'Date'[Date] )) This approach will stop the SUM from crossing all dates. Instead, you will only use the SUM function on the last date of the time period, thus effectively creating a semi-additive measure.
What is a A Treemap is ideal to visualize?
Large amounts of hierarchical data when a bar chart can't effectively handle the large number of values. Proportions between each part and the whole. The distribution pattern of the measure across each level of categories in the hierarchy. Attributes, by using size and color coding. Spot patterns, outliers, most-important contributors, and exceptions.
What are the software components of Power BI?
MS Windows desktop application called Power BI Desktop an online SaaS service call the Power BI Service mobile Power BI apps that are available on any device, with native mobile BI apps for Windows, iOS, and Android
What method of modeling data allows for the integration with external data sources?
Models can be developed by importing data from data flows, which can then be integrated with external data sources.
How do you create a Python visual?
No prerequisites exist for creating a Python visual, so you can start right away in Power BI Desktop by selecting the Python visual icon in the Visualizations pane. Select Enable on the window that displays, and then you'll then see a placeholder Python visual image on the report canvas, with the Python script editor underneath. You can continue creating a Python visual in the same way as you did when creating the R visual. In summary, you would select the fields, write the Python script, and then select Run from the Python script editor title bar.
With paginated reports, after the data has been collected, can the different data sources be merged into a single data model?
No. Each source must be used for a different purpose. For instance, data from an Excel source can be used for a chart, while data from SQL Server can be used for a table on a single report. Paginated reports have an expression language that can be used to look up data in different datasets, but it is nothing like Power Query.
In the Data view, how would you add a calculated column to a new Date table containing a single column [Date]? NOTE: The fiscal year begins in July.
On the Table Tools contextual ribbon, from inside the Calculations group, click New Column. In the formula bar, type the following, and then press Enter: DAXCopy Year = "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1) A calculated column is created by first entering the column name, followed by the equals symbol (=), followed by a DAX formula that returns a single-value result. The column name cannot already exist in the table. The formula uses the date's year value but adds one to the year value when the month is after June. This is how fiscal years at Adventure Works are calculated. Verify that the new column was added.
Which of the following filters are not available in Power BI reports? Drillthrough Page type Report level Page level
Page type
Paginated reports allow report developers to create what?
Paginated reports allow report developers to create Power BI artifacts that have tightly controlled rendering requirements.
With what PBI Desktop tool can you isolate queries to validate query plans?
Performance analyzer
The performance in Power Query depends on what?
Performance at the data source level.
Which animation custom visual works like a dynamic slicer and is a compelling way to display time trends and patterns in your data without user interaction?
Play Axis hat is available for scatter chart and line chart visualizations within Power BI Desktop
Power BI Desktop, what can you do in live connection mode?
Power BI Desktop can no longer be used to develop the data model; in live connection mode, it's only a report authoring tool. It is possible, however, to create measures, but they are measures that are only available within the report. You won't add any report-scoped measures in this lab.
What are capacity are PBI paginated reports a feature of?
Power BI Premium
Power BI paginated reports are created by using which tool? Power BI Desktop Power BI service Power BI Report Builder
Power BI Report Builder
How do Map visualizations work?
Power BI integrates with Bing Maps to 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.
With the Q&A, what does Power BI do with questions that are asked?
Power BI records all questions that are asked, and you can use this information to set up the Q&A feature to be more effective. When the Q&A feature answers so many questions, you'll have fewer people turning to you for those answers.
Where is the only place you can build dashboards?
Power BI service.
How do you check to see if query folding is occuring?
Power Query Editor --> Query Settings pane ("Applied Steps") --> Right-click the last applied step --> If the "View Native Query" option is available (displays in bold type), then click it. --> The native query that is used to transform the query is revealed
Which tool allow you to ask natural-language questions about the data?
Q&A feature
The Q&A visual consists of what three main elements?
Question box - In this element, the user can enter their question about the data. An example of a question could be: What was the average sales amount by category? Entering this question will trigger Power BI's natural-language analysis engine to parse and determine the appropriate data to display. Pre-populated suggestion tiles - This element contains tiles with pre-populated suggestions for questions that the user can consider asking. When the user selects one of these tiles, they will be shown analysis. For example, if you select the top product category named by march sales tile, you would get the following visual that is converted from the Q&A visual. Pin visual icon - This icon is located in the upper right of the visual, as shown in the following image. Selecting the pin visual icon will allow you to pin the visual onto a new or existing dashboard.
When using DAX variables, the measure expression must be output after what keyword?
RETURN
What is a common descriptive metric used in many industries?
Return On Investment (ROI) a ratio between net profit (over a period) and cost of investment (resulting from an investment of some resources at a point in time). A high ROI means the investment's gains compare favourably to its cost. As a performance measure, ROI is used to evaluate the efficiency of an investment or to compare the efficiencies of several different investments. In economic terms, it is one way of relating profits to capital invested.
What is a role playing dimension? Give an example
Role-playing dimensions have multiple valid relationships with fact tables, meaning that the same dimension can be used to filter multiple columns or tables of data. As a result, you can filter data differently depending on what information you need to retrieve Calendar is the dimension table, while Sales and Order are fact tables. The dimension table has two relationships: one with Sales and one with Order. This example is of a role-playing dimension because the Calendar table can be used to group data in both Sales and Order. If you wanted to build a visual in which the Calendar table references the Order and the Sales tables, the Calendar table would act as a role-playing dimension.
Power BI paginated reports is an evolved technology that was built from which original tool? SQL Server Analysis Services SQL Server Reporting Services Microsoft SharePoint
SQL Server Reporting Services
What are Power BI paginated reports descendants of?
SQL Server Reporting Services (SSRS), which was first introduced in 2004. Power BI paginated reports and SSRS have a lot in common. If you're looking for information on paginated reports and can't find it, searching the internet and Microsoft documentation on SSRS is an excellent idea because you'll find numerous blog posts, videos, and documentation available to you.
Write a measure to calculate the percentage of sales in all regions.
Sales % All Region = DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region) ) )
Which of the following selections are not features of the Q&A visual? Adding new synonyms to fields through Q&A tooling. Converting a Q&A answer into a visual inside your report. Searching for help topics about Power BI.
Searching for help topics about Power BI.
How might you concatenate columns in the PQE?
Select Transform Data on the ribbon. On Applied Steps, on the right pane, right-click the last step and then select Insert Step After. Under Add Column on the Home ribbon, select Custom Column. Enter the following equation, which will concatenate the Year and Month columns, and then add a dash in between the column names. Column = Table.AddColumn(#"Renamed Columns", "Custom", each [Year] & "-" &[Month]) Change the data type to Date and then rename the column. Your Budget table should resemble the following figure.
How do you create an R visual?
Select the R visual icon in the Visualizations pane and then select Enable on the window that displays. You'll then see a placeholder R visual image on the report canvas, with the R script editor underneath. Next, in the Field panel, select the fields that you want to use in your script. They will display in the Values section in the Visualizations pane. You'll use the data in these fields to create a plot. As you select or remove fields, supporting code in the R script editor is automatically generated or removed. Based on your selections, the R script editor generates the following binding code: - The editor created a dataset dataframe with the fields that you added. - The default aggregation is: do not summarize. - Similar to table visuals, fields are grouped and duplicate rows appear only once. When you have selected the fields, you're ready to write an R script that results in plotting to the R default device. When the script is complete, select Run from the R script editor title bar. Power BI Desktop identifies the plot and presents it on the canvas.
In what ways can you configure a slicer control?
Select the slicer and then, in the Format pane, expand the Selection controls section to view the following options: Single select - This option is Off by default. It ensures that only one item can be selected at a time. Multi-select with CTRL - This option is On by default. It allows you to select multiple items by pressing the Ctrl key. 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.
How does performance using DirectQuery depend heavily the performance of the underlying data source and the number of users connecting at a time?
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.
In the Report view, in the Fields pane, how are spatial fields adorned? What will fields adorned with the sigma symbol (Ʃ) do?
Spatial fields in the Region and Reseller table are adorned with a spatial icon Fields adorned with the sigma symbol (Ʃ) will summarize, by default
telemetry
Telemetry is the in situ collection of measurements or other data at remote points and their automatic transmission to receiving equipment (telecommunication) for monitoring.[1] The word is derived from the Greek roots tele, "remote", and metron, "measure". Systems that need external instructions and data to operate require the counterpart of telemetry, telecommand.[2] Although the term commonly refers to wireless data transfer mechanisms (e.g., using radio, ultrasonic, or infrared systems), it also encompasses data transferred over other media such as a telephone or computer network, optical link or other wired communications like power line carriers. Many modern telemetry systems take advantage of the low cost and ubiquity of GSM networks by using SMS to receive and transmit telemetry data. A telemeter is a physical device used in telemetry. It consists of a sensor, a transmission path, and a display, recording, or control device. Electronic devices are widely used in telemetry and can be wireless or hard-wired, analog or digital. Other technologies are also possible, such as mechanical, hydraulic and optical.[3] Telemetry may be commutated to allow the transmission of multiple data streams in a fixed frame.
What Power BI feature can give an in-depth analysis of the distribution of data? 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. The Analyze feature allows a user to understand why the distribution looks the way that it does. Only time series analysis can provide in-depth analysis on the data.
The Analyze feature allows a user to understand why the distribution looks the way that it does.
In what section of the PQE are steps that you apply captured?
The Applied Steps section of the Query Settings pane on the right side
When creating a calculated column, which of the three methods compresses the data? Which does not?
The DAX calculated column does not compress as well as the other methods. The other column types do get compressed, which makes the .pbix file smaller and the performance usually faster.
When building a paginated report, how does the field appear? How does the field label (or header) appear?
The field appears in square brackets. The label or header appears without brackets. You can rename or apply formatting to the headers, such as bolding or underlining the text.
What are the differences between calculated columns and measures?
The fundamental difference between a calculated column and a measure is that a calculated column creates a value for each row in a table. For this reason, the 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. For example, if you drag a Total Sales measure onto the report, Power BI calculates 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.
If you are creating a report with category on rows, and sales on values, and all values are the same, what might be the issue?
The issue is that the table is based on fields from different tables. The expectation is that each product category displays the sales for that category. However, because there isn't a model relationship between these tables, the Sales table is not filtered. You will now add a relationship to propagate filters between the tables.
f a DAX query is displaying a high duration value, but the duration of measures and visuals are displaying low values (in other words they have a short duration time), what might be reason for the poor performance?
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
How do you create bins?
The process of binning allows you to group your numerical and time field data into "bins" of equal size. This approach allows you to visualize and identify trends in your data in more meaningful ways. Binning allows you to right-size the data that Power BI Desktop displays. In this example, you want to create bins (groups) for the Order Qty field. Start in the Fields pane by right-clicking the Order Qty field that you want to create the bins for, and then select New Group. On the Groups window that displays, set the Bin size to the size that you want, adjust other settings as required, and then select OK. When you have set up the bin group, you'll see a new field in the Fields pane with (bins) appended to its name. You can then drag that field onto the canvas to use the bin size in a visual.
What do descriptive analytics techniques do with large data sets?
They summarizes them to describe outcomes to stakeholders.
When should you create your own date table in PBI Desktop?
This design approach for a date table is suitable when you don't have a date table in your data source. If you have access to a data warehouse, it would be appropriate to load date data from its date dimension table rather than "redefining" date logic in your data model.
What is the query analyzer useful for?
This tool is useful when you want to analyze performance on the Power Query side for tasks such as loading datasets, running data refreshes, or running other transformative tasks.
Where are time series charts located? The filter pane is where all filters on visuals and pages are located. Time series charts can be imported from AppSource. The fields pane is where all charts are located.
Time series charts can be imported from AppSource.
How do you create a parameter in a paginated report?
To add a parameter, right-click Parameters and select Add Parameter. On the General tab, name the parameter, select the data type, and then choose the prompt that the user will see. On the Available Values tab, enter options that the user can choose from. The Default Values tab has the initial value of the parameter when the report loads, but it can be changed by the user. You can also get parameter values from a query. For more information, see the Microsoft documentation on parameters. 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. 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.
Describe a scenario where you might be able to import data into Power BI.
To continue with the scenario, you are building Power BI reports for the Sales department at Tailwind Traders, where importing the data is not an ideal method. The first task you need to accomplish is to create your datasets in Power BI so you can build visuals and other report elements. The Sales department has many different datasets of varying sizes. For security reasons, you are not allowed to import local copies of the data into your reports, so directly importing data is no longer an option. Therefore, you need to create a direct connection to the Sales department's data source. The following section describes how you can ensure that these business requirements are satisfied when you are importing data into Power BI.
How do you create a histogram that displays the order quantity (OrderQty field) by order size buckets?
To create a group for the x-axis, 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. 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. The visual now shows that the data is grouped into buckets on the x-axis, with the order quantities of that variable on the y-axis. You have now produced a histogram that displays the order quantity (OrderQty field) by order size buckets
In a bar chart, how do you create a group?
To create the group, use Ctrl + click to select the data points on the visual that you want to group. In this case, it's states with sales greater than 500,000 dollars. Right-click one of those selected data points and then select the Group data option. When the group is created, notice that the visual updates to take into account the new group. The following image shows that the other states, which are the states with lower sales (less than 500,000 dollars) have been grouped together and are highlighted in a different shade. The new group field displays in the Legend bucket for the visual and is listed in the Fields pane. When you create a group, you can change the way that the data is displayed in the visual. For example, you might want to switch the values in each axis. You can also use the group in any of the other visuals in your report. To do so, drag the group field from the Fields pane and then drop it into the visual in which you want to use it.
What are the various ways you can access the Q&A feature?
To get access to the Q&A feature, you need to add the Q&A visual to your report. You can double-click anywhere on the canvas, and the visual should appear. Alternatively, you can select the Q&A icon on the Visualizations pane. The Q&A feature is also available as a button, which is a useful option if you want to save space on your report canvas. When the Q&A visual or button is added to your report, you can reposition and resize it. You can also customize the formatting in the same way that you would for any other type of visual or button. You can start asking questions immediately by selecting one of the suggested questions or by entering a question into the question box. As you type, Power BI will automatically display suggestions to help you complete your question.
How do you get started creating a paginated report?
To retrieve data, open Power BI Report Builder. From the Getting Started screen, select New Report. You can choose whether to create a report with a table on it, a chart, or a blank report. These choices create a default visual on your a new report, which can be changed at any time. Next, go to the Report Data window, which is typically on the left side of the tool, though it can be moved around.
Given employee, manager, and Path columns, how would create columns for each hierarchy level?
To view all levels of the hierarchy separately, you can create columns in the same way that you did previously, by entering the following equations. You will use the PATHITEM function to retrieve the value that resides in the corresponding level of your hierarchy. Level 1 = PATHITEM(Employee[Path],1) Level 2 = PATHITEM(Employee[Path],2) Level 3 = PATHITEM(Employee[Path],3) After you have finished, notice that you now have each level of hierarchy within your table.
How would you get the top 10 products using a filter type?
Top N is a filtering option that is available on the Filters pane. Select the field that you want to analyze on your report page (in this example, it's the Product Name field). In the Filters pane, expand the Filter type list and select Top N. In the Show items settings, select Top and 10. Then, select Cost of Sales as the value that you want to filter the field by. The visual updates accordingly
What formula would you write if you want to see the total sales of the month next to the total sales of the prior month? How does the formula work?
Total Sales Previous Month = CALCULATE ( sum('Sales OrderDetails'[Total Price]) , PREVIOUSMONTH(Dates[Date]) ) This measure uses the CALCULATE function, indicating that you're overriding the context to evaluate this expression the way that you want to. You're summing Total Price, as you've been doing throughout this module. For the second argument, you're using PREVIOUSMONTH for the override, which tells Power BI that, no matter what month is the default, the system should override it to be the previous month.
Can Power Query-source tables be used to load data from external data sources? Can calculated 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.
Why is it important to simplify your data model?
Users will enjoy using your data model considerably more. For example, suppose you've imported dozens of tables from many data sources and now the visual appears disorderly. In this case, you need to ensure that, before you begin working on building reports, your data model and table structure are simplified.
What should you consider about the number of visuals on a report page?
Using more visuals might have the opposite effect to what you are trying to achieve. More visuals might make your report look too busy, causing users to feel overwhelmed because they don't know where to focus their attention. Also, visuals are key factors in the performance of your report; they contribute to performance issues. The fewer visuals you use, the better the performance will be. It's best to limit the number of visuals that you use on a page. Examine each visual and ask yourself if it's necessary. If a visual does not add value to the audience, you should not use it in your report.
How do you view the Column Distribution feature?
View ribbon > under Data Preview > choose options: Column Distribution, Column Quality, and Column Profile
How do you navigate to the Advanced Query Editor?
View ribbon > Advanced Editor
What is the name of the tab in PBI Desktop where you create the report?
Visualizations
To what objects can you add objects? Why might you choose to add a comment to a specific visual?
When you add a comment to a specific visual rather than the report as a whole, the context of the comment is clearer and acts like a personal bookmark.
When designing navigation buttons, what can you chose for Back navigation?
When you are on that destination page, to return to the Navigation page, you can use one of the following Back button navigation options: Select the Back button from the main Buttons menu, and then reposition the button to where you want it to sit on the page. OR Select the Blank button from the main Buttons menu, reposition and customize the button as required, and then select Back as the action typ
What ways can you setup and monitor a Q&A visual?
When you have added the Q&A visual to your report, you can set up the underlying Q&A feature so that it improves in answering questions about your data. Basically, you will teach the Q&A feature to better understand people. This setup can be useful from the outset so that you can get the visual ready for active use. However, setup does not stop at that point; you can proactively monitor and review the questions that are coming through from users, and then you can address misunderstandings or common typos. You can also manage the key terms that are associated with your data, so you can add a library of synonyms that might be entered by different users across the organization when they are asking questions about the data. You can constantly calibrate the Q&A feature so that it provides better answers to your organization's questions.
What is the best practice for retrieving the data you need from a relational database using SQL? Why
Write a view instead of coding directly in Power BI. If you Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Power Query.
How do you chose a particular column in a date table to be the date column?
You can also choose specific columns in your table to mark as the date, which can be useful when you have many columns within your table. 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.
What does conditional formatting need to be based on?
You can apply conditional formatting to any text or data field, but the formatting needs to be based on a field that has numeric, color name or hex code, or web URL values.
How do you use a combination of DAX and a scatter chart to identify outliers?
You can use DAX to create a measure that will identify the outliers in your data, such as in the following formula: 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. You then need to add a scatter chart visual, as you did in the previous section, because this the best visualization option for displaying outliers. When you've added the scatter chart, populate it with the fields that are associated with your DAX formula and outlier measure.
What is the best way to identify outliers in Power BI?
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.
With which file types do you have a higher likelihood of Power BI Desktop detecting the wrong data type?
You have a higher chance of getting data type errors when you are dealing with flat files, such as comma-separated values (.CSV) files and Excel workbooks (.XLSX), because data was entered manually into the worksheets and mistakes were made. Conversely, in databases, the data types are predefined when tables or views are created.
How do you create a graphical tooltip?
You would begin by creating a new page in the report. Open the new page and then open the Format pane. Expand the Page Size section and then select Tooltip from the Type list. In the Page information section, turn the Tooltip slider to On so that Power BI registers this page as a tooltip page Tooltips have limited canvas space, so to ensure that your visuals appear in the tooltip, on the View tab, set the Page view option to Actual size. Next, add one or more visuals to the tooltip page, in the same way that you would on any other report page. Now, you need to specify the fields for which you want the tooltip to display. Select the tooltip page and then select the Values tab in the Visualizations pane. Drag the fields from the Fields pane into the Tooltip bucket. In this example, you will drag the SalesAmount field into the Tooltip bucket. Return to the report page and apply the tooltip to one or more visuals on that page. Select a visual and then, in the Format pane, scroll down to the Tooltip section. Turn the tooltip option On and then select your tooltip page from the Page list. When you hover over the visual, the tooltip will display.
What does a Mixed model design produce?
a composite model
Except for streaming datasets, Power BI datasets represent what?
a data model
Success with Power BI begins with ____________________.
a great data model
What type of report should you create if you want a footer on every sales receipt that you create or if you want a certain customer's name to always appear in green font on a report or operational reports with tables of details and optional headers and footers or you expect to print the report on paper or when you want an e-receipt, a purchase order, or an invoice. or you need customized sort orders, clickable-headers, and URLs in results, which allows for simple integration with custom applications or display all of your data in a single report element, such as a table. If you have 25,000 records, and you want the reports to print over 100 pages, you can do that or you want every third record to be printed with a light pink background?
a paginated report
What do paginated reports give?
a pixel-perfect view of the data. Pixel perfect means that you have total control of how the report renders.
What is cardinality?
a term that is used to describe the uniqueness of the values in a column It is also used in the context of the relationships between two tables, where it describes the direction of the relationship.
What is a flat file?
a type of file that has only one data table and every row of data is in the same structure A data file with no structured hierarchy
What does each transformation you create in the Power Query Editor interface result in?
additional step logic
In the PQE, when should you remove unnecessary columns?
as early as possible
The Q&A visualization allows you to what?
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.
What should you do if you find that is NOT the visuals that are causing performance issues?
assess the DAX query results that are displayed in the Performance analyzer pane and investigate those results further. For example, you might need to look elsewhere in your data model, such as the relationships and columns.
Where can you verify the number of columns and rows in Power Query?
at the bottom-left, in the status bar
What are visuals?
attractive charts and graphics that you can use to revitalize your data. allow you to share data insights more effectively and increase comprehension, retention, and appeal. a fundamental part of your report because they help your report audience connect and interact with the information to make informed decisions quickly. provide users with quicker, easier access to the information that they need to make their business decisions. allow you to present the important information and insights that you discovered in the data in a compelling and insightful way. The report consumers rely on these visualizations as a gateway to the underlying data.
What is the key to unlocking business data?
being able to tell a story with it
For one-to-one relationships, the only option that is available is what?
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.
How do you create a calculated column?
by selecting the ellipsis (...) button on the table in the Fields list and then selecting New column. A new DAX formula appears in the formula bar underneath the ribbon at the top.
How is The data that you load into Power BI Desktop is compressed and stored to the disk?
by the VertiPaq storage engine
What are three ways to show the top 10 selling products?
by using a Q&A visual, using a Top N filter, or writing a DAX formula.
If you are extracting data from a relational database by using SQL, how might you limit the columns that you extract?
by using a column list in the SELECT statement
How can you apply filtering?
by using the Filters pane or by adding and using a slicer. Slicers and filters are similar; both let you filter out the unnecessary data
How do you remove duplicates from columns to only keep unique names in a selected column? Give an example.
by using the Remove Duplicates feature in Power Query. In this example, notice that the Category Name column contains duplicates for each category. As a result, you want to create a table with unique categories and use it in your data model. You can achieve this action by selecting a column, right-clicking on the header of the column, and then selecting the Remove Duplicates option.
How do you get to the Power Query Editor to transform data?
by using the Transform Data button
How can you ensure that changes to visuals in reports are shown live in your dashboard?
by using the pinning live reports to a dashboard feature from Power BI
What does this formula do? Sales YoY Growth = VAR SalesPriorYear = CALCULATE( SUM(Sales[Sales]), PARALLELPERIOD( 'Date'[Date], -12, MONTH ) ) RETURN DIVIDE( (SUM(Sales[Sales]) - SalesPriorYear), SalesPriorYear )
calculates the % change in sales from the same period 12 months ago.
Give an example of untapped data source to which natural language processing concepts can be applied to make sense of the data?
call center conversation logs product reviews
How can you access the DAX functions that you can use to get quick statistics based on your data?
can access these quick functions by right-clicking the Values field in the Visualizations pane, as illustrated in the following image.
What is a common pitfall when creating relationships? Give an example.
circular relationships. For example, you are developing reports for the Sales team and are examining the relationships between tables. In a poorly designed data model, Table 1 has a many-to-one relationship with a column in Table 2, but Table 2 has a one-to-many relationship with Table 3 that has its own relationship with Table 1. This web of relationships is difficult to manage and becomes a daunting task to build visuals because it is no longer clear what relationships exist. Therefore, it is important that you are able to identify circular relationships so that your data is usable.
What should you do before you start the Performance analyzer?
cleared the caches and opened the Power BI Desktop file on the blank page, go to the View tab and select the Performance analyzer option.-
How do you see a visual in expanded form?
click Focus mode.
What does the management of content help to foster?
collaboration between teams and individuals
What is the most common type of flat file?
comma-separated values (.csv) file delimited text (.txt) file fixed width file MS Excel workbook (.xlsx)
What is the only way to visualize data coming from a streaming data source
create a tile directly on a dashboard and use a custom streaming data source.
In the PQE, what changes might you make to columns?
data type format default summarization
How should you avoid deleting columns in the PQE?
deal with them at the source when loading data into PBI Desktop.
Before you start creating aggregations in your data model, what should you do?
decide on the grain (level) on which you want to create them
Typically, a smaller data model is comprised of what?
fewer tables and fewer columns in each table that the user can see.
What are comments useful for?
for personal comments or for starting a conversation about a report item with your colleagues. For example, users can comment on pages or visuals that they are experiencing issues with, or they could give you suggestions for changes or improvements.
How do you manage relationships in the data model?
go to Manage Relationships on the ribbon, where the Manage relationships window will appear.
What is a radial gauge chart?
has a circular arc 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).
What can determining data anomalies help you with
identifying what the normal distribution of your data looks like and whether specific data points exist that you need to investigate further.
What is a good guideline for knowing when query folding is possible.
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.
Give an example of a 1:1 relationship.
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.
When connections to multiple data sources include Azure Analysis Services, what alternative to importing do you have?
import all other data that you want (from Excel, SQL Server, and so on) into the Azure Analysis Services model and then use a live connection. Using this approach, the data modeling and DAX measures are all performed in one place, and it's a much simpler and easier way to maintain your solution.
What is the most popular way to use data in Power BI
import it
Where do you go to access query diagnostics?
in Power Query Editor, go to Tools in the Home ribbon. When you are ready to begin transforming your data or making other edits in Power Query Editor, select Start Diagnostics on the Session Diagnostics tab. When you are finished, make sure that you select Stop Diagnostics.
Metadata
information about other data.
What does connecting to a SQL Server Analysis Services model involve?
installing the on-premises data gateway, whether it's on-premises or VM-=hosted infrastructure-as-a-service (IaaS).
What data does predictive analytics use, and how does it use it?
it uses historical data to identify trends and determine if they are likely to recur.
Where can data alerts be used? On what objects?
only available on Power BI service and they are available on such report elements as KPI visuals, gauges, and cards.
When you interact with a dashboard or report, what may happen?
other visuals on the page may be filtered by the selection you make
What are your options for R and Python visualizations?
out-of-the-box visualization option for both R and Python that you can access on the Visualizations pane, and the process for creating these visuals is almost the same. You can also import a custom R or Python visual from Microsoft AppSource.
What does the final 'in' statement do?
returns the result set of the specified step name
What do shared data sets allow you to do?
reuse data that you have prepared and modeled
How do you use the Analyzer feature?
right-click a data point on the visual and then hover over the Analyze option to display two further options: Explain the increase and Find where the distribution is different. The options that are available will depend on the data point that you selected. a window displays with a new visual, as illustrated in the following image. 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.
How do you create a quick measure
right-click or select the ellipsis (...) button next to any item in the Fields pane and then select New quick measure from the menu that appears. The Quick measures screen will appear. In the window, 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. This approach can be a helpful way to learn the DAX syntax.
In Performance Analyzer, how do you review the task in order of duration, longest to shortest?
right-click the Sort icon next to the Duration (ms) column header, and then select Total time in Descending order.
In the PQE, what are two ways to rename columns?
right-click the header > select Rename > edit the name > press Enter double-click the column header > overwrite the name with the correct name
How do you save your work as a .pbix file in Power BI Desktop?
select File > Save (or File > Save As), as shown in the following image
How do you publish a paginated report?
select File > Save as and then select Power BI Service. Your report will now appear in Power BI service.
How do you add alt text to an object?
select that object and, in the Visualizations pane, open the Format pane. Expand the General section, scroll to the bottom of the options, and then enter a description in the Alt Text box.
What can you do in the visual Border section?
set a border around the visual to isolate the visual from other elements on the canvas, which helps make it easier for the user to read and understand the data. You can change the border color and radius to be consistent with your color scheme
What can you do in the visual Background section?
set any color or image as the background for the visual. If you plan to use an image as a background, try to select an image that won't have lines or shapes that would make it difficult for the user to read the data. It is best to keep a white background so the presented data can be clearly seen
What factors affect how a DAX formula is calculated and displayed?
visual elements, slicers, filters, etc.
In databases, when are data types defined?
when tables or views are created
What are some key guidelines for a well-designed report?
- 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. - 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. - 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.
What can you do in the General tab of the Properties pane of the Model view?
- 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.
What measures can you take to tune a report if you identify that visuals are the bottleneck that has caused poor performance?
- Reduce the number of visuals on the report page because fewer visuals means better performance. If a visual is not necessary and doesn't add value to the user, you should remove it. Rather than using multiple visuals on the page, consider other ways to provide additional details, such as drillthrough pages and report page tooltips. - Reduce the number of fields in each visual. The upper limit for visuals is 100 fields, so a visual with more than 100 fields will be slow to load (and will look cluttered and confusing). Identify fields that are not valuable to the visual and then remove them.
Describe the importance and types of inventory analysis
- days sales of inventory, optimize inventory in stores; inventory analysis can smooth out the biggest challenge for most growing businesses — cash flow; Managing inventory is all about efficiency — you don't want too much or not enough of any item; so you're not losing money on wasted items or missing out on sales due to stockouts; identify trends and acquire the right mix of products based on customer demand; Reduce stockouts and project delays; Low on cash and run out of an in-demand SKU? Finding short-term financing is a pain and cuts into your margins. Capital all tied up in inventory that is sitting rotting in storage? It's incredibly painful to write it off as a loss. Inventory analysis can show you when the lean times in your business are and when the rush is, so you can prepare with safety stock or stop buying inventory you're not selling; enables faster response times and efficiently filled orders, giving you the ability to earn loyal customers; lost inventory, theft, and leakage, which reduces costs and enables you to introduce costs controls; better pricing from suppliers and vendors Always Better Control (ABC) analysis Vital Essential Desirable (VED) control High Medium Low cost (HML) analysis Scarce Difficult Easy (SDE) analysis Gross Margin Return On Invested Inventory (GMROI) Available to Promise Inventory Turnover Rate Stockout Rate Customer Service Level - probability of no having a stockout or probability of not losing a sale
What is the responsibility of the data analyst in managing Power BI assets?
- oversee the sharing and distribution of items, such as reports and dashboards - ensuring the security of Power BI assets
What types of change might occur when acting on the data?
- reallocating resources within the business to accommodate a need - identifying a failing campaign and knowing when to change course - adjusting...prices, inventory, purchasing, assortment
What are aspects of building a data culture?
- telling the data story - where the story is told - who the story is told to - making sure people can find the story and have appropriate access - making sure data stories are part of regular interactions
A retail business should be able to use their data in such a way that impacts the business, including what?
- tracking inventory - identifying customer purchase habits - detecting user trends and patterns - recommending purchases - determining price optimizations - identifying and stopping fraud - identifying daily/monthly sales patterns - performing trend analysis - performing period over period analysis
What is the data analyst's role in analyzing the data?
- understand the analytical capabilities of Power BI - use those capabilities to find insights - identify patterns and trends - predict outcomes - communicate those insights in a way that everyone can understand
What are the five dataset types?
1) Connecting to an existing data model that isn't hosted in a Power BI capacity 2) Uploading a Power BI Desktop file that contains a model 3) Uploading an Excel workbook (containing one or more Excel tables and/or a workbook data model), or uploading a CSV (comma-separated values) file 4) Using the Power BI service to create a push dataset 5) Using the Power BI service to create a streaming or hybrid streaming dataset
Three ways 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.
What are two ways you can enable/disable this Auto date/time option?
1) globally so that it applies to all of your Power BI Desktop files, or 2) you can enable/disable the option for the current file so that it applies to an individual file only.
What is the diagnostic analytics process?
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
What are the key responsibilities of the data analyst?
A data analyst knows how to organize information and distill it into something relevant and comprehensible. A data analyst knows how to gather the right data and what to do with it, in other words, making sense of the data in your data overload.
What should you be aware of about bi-directional filtering.
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.
What does the AI splits feature do? 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. 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. AI splits only display the difference between highest and lowest value of the measure that is being analyzed.
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 would you use a Q&A visual to get the top 10 products?
Add the Q&A visualization to your report, and then reposition the visual and customize its formatting, as required. Now, you can use the visual to get answers. In this case, you want to know what the top 10 selling products are, so you enter a question such as, "What are my top 10 products by sales?" Power BI will automatically display those results for you.
Where can you see the code that Power Query Editor is creating with each step, and create your own shaping code?
Advanced Editor
Where do alert notifications appear, and where can they be delivered?
Alert notifications appear on the dashboard tile, and can be delivered by email, and push notifications to mobile apps including the Apple Watch.
What are selections used for? What are they used along side of?
Allow you to determine what items in the report are visible and what items are hidden. They are used alongside bookmarks and buttons.
What does the CALENDARAUTO() function return?
Alternatively, the 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.
regression analysis
An analytic technique where a series of input variables are examined in relation to their corresponding output results in order to develop a mathematical or statistical relationship. measures the impact of a set of variables on another variable A method of predicting sales based on finding a relationship between past sales and one or more independent variables, such as population or income
How might you combine summarized fact table with drill-through reporting.
An effective technique to reduce the model size is to set the Storage Mode property for larger fact-type tables to DirectQuery. This design approach can work well in conjunction with techniques that are used to summarize your data. For example, the summarized sales data could be used to achieve high performance "summary" reporting. A drill-through page could be created to display granular sales for specific (and narrow) filter context, displaying all in-context sales orders. The drill-through page would include visuals based on a DirectQuery table to retrieve the sales order data (sales order details).
What is an outlier?
An outlier is a type of anomaly in your data, something that you didn't expect or that surprised you, based on historical averages or results.
What are the differences between connecting to a SQL database and an Azure Analysis Services database?
Analysis Services cubes have calculations already in the cube, which will be discussed in more detail later. 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). You don't need to use the Get Data button in Power BI Desktop.
decision trees
Analysis procedure which classifies observations into distinct groups based upon the values of predictor/input variables Graph of decisions and their possible consequences, used to create a plan to reach a goal Diagrams where answers to yes or no questions lead decision makers to address additional questions until they reach the end of the tree.
What is the range of who Power BI can serve?
Any where from a personal report and visualization tool, to the analytics and decision engine behind group projects, divisions, and entire corporations
Who can see comments?
Anyone with the right permissions
How do data analysts build business trust in its data?
As a practice, the data analysis process will capture data from trusted sources and shape it into something that is consumable, meaningful, and easily understood to help with the decision-making process. Data analysis enables businesses to fully understand their data through data-driven processes and decisions, allowing them to be confident in their decisions.
What standard database practice can help to optimize a DirectQuery data connection?
Avoid the use of complex calculated columns because the calculation expression will be embedded into the source queries. It is more efficient to push the expression back to the source because it avoids the push down. You could also consider adding surrogate key columns to dimension-type tables. Review the indexes and verify that the current indexing is correct. If you need to create new indexes, ensure that they are appropriate.
How does the Azure Analysis Services refresh work?
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. This process can improve the timeliness of the data in your report.
What is Azure Analysis Services?
Azure Analysis Services is an Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and creates calculations on the data.
What technology is Azure Analysis Services similar to?
Azure Analysis Services is similar to the data modeling and storage technology in Power BI.
Which AI integrations in Power BI can take your analysis to next level?
Azure machine learning Cognitive services built-in AI visuals
Why would you likely import Azure Analysis Services data directly into Power BI?
Because you want to get data to other data in your organization.
What are bookmarks and how can they be used?
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.
What are the main role in the data analytics process?
Business Analyst Data Analyst Data Engineer Data Scientist Database Administrator
Give an example of several layers and types of data analytics that a retail company might perform.
By enabling reporting and data visualizations, a retail business uses descriptive analytics to look at patterns of purchases from previous years to determine what products might be popular next year. The company might also look at supporting data to understand why a particular product was popular and if that trend is continuing, which will help them determine whether to continue stocking that product. A business might determine that a certain product was popular over a specific timeframe. Then, they can use this analysis to determine whether certain marketing efforts or online social activities contributed to the sales increase.
How might you analyze a scatter chart?
By identifying outliers, you can run further analysis and break them down by group, which can help to improve things like logistics, decrease costs, and increase customer satisfaction.
What Data Analysis Expression (DAX) functions can be used to build your common date table?
CALENDARAUTO() or CALENDAR()
Give an example of data source view that adds a calculated column.
CREATE VIEW OrdersWithTotalPrice AS SELECT unitprice, qty, unitprice * qty as TotalPrice FROM sales.salesorders
What can you do in the Format tab of the Properties pane of the Model view?
Change the data type. Format the data
What does the decomposition tree not enable you to do? Conduct root cause analysis to understand a measure better. Conduct what-if analysis with built-in parameters. Automatically analyze selected dimensions to find where a measure is highest or lowest
Conduct what-if analysis with built-in parameters.
Give an example of query folding
Consider a scenario where you've renamed a few columns in the Sales data and merged a city and state column together in the "city state" format. Meanwhile, the query folding feature tracks those changes in native queries. Then, when you load your data, the transformations take place independently in the original source, this ensures that performance is optimized in Power BI.
Describe a scenario where you would want to define a higher level of data granularity.
Consider a scenario where your company manages 1,000 refrigerated semi-trucks. Every few minutes, each truck uses a Microsoft Azure IoT application to record its current temperature. This temperature is important to your organization because, if the refrigeration were to malfunction, it could spoil the entire load, costing thousands of dollars. With so many trucks and so many sensors, extensive data is generated every day. Your report users don't want to sift through numerous records to find the ones that they are particularly interested in. How can you change the granularity of the data to make the dataset more usable? In this scenario, you might want to import the data by using a daily average for each truck. That approach would reduce the records in the database to one record for each truck for each day. If you decide that the approach was acceptable enough for tracking costs and errors, then you could use that data granularity. Alternatively, you could select the last recorded temperature, or you could only import records that are above or below a normal range of temperatures. Any of these methods will reduce the total records that you import, while still bringing in data that is comprehensive and valuable.
Describe a scenario where you create a *:* relationship. What is the default filtering?
Consider the scenario where you are tasked with building a visual that examines budgets for customers and accounts. You can have multiple customers on the same account and multiple accounts with the same customer, so you know that you need to create a many-to-many relationship. To create this relationship, go to Manage Relationships > New. In the resulting window, create a relationship between the Customer ID column in CustomerTable and AccountTable. The cardinality is set to many-to-many, and the filter type is in both directions. Immediately, you will be warned that you should only use this type of relationship if it is expected that neither column will have unique values because you might get unexpected values. Because you want to filter in both directions, choose bi-directional cross-filtering. Select OK. You have now successfully created a many-to-many relationship.
How would you create a measure that always calculates the total sales for 2015, regardless of which year is selected in any other visual in Power BI?
Create a measure that looks like the following sample: Total Sales for 2015 = CALCULATE(SUM('Sales OrderDetails'[Total Price]), YEAR('Sales OrderDetails'[orderdate]) = 2015) Notice how the measure is named Total Sales for 2015. When you use the CALCULATE function to override the context, it is helpful to name the measure in a way that describes exactly how you are overriding it. In this example, CALCULATE is aggregating the Total Price column, just as you did in the previous measure. However, instead of operating over the entire dataset while using whatever the filter context tells it to do, you are overriding the filter context for the year 2015. No matter what year is selected, you will always get the total for 2015; all other filters still apply. If you were to filter by any other criteria, such as region, employee, or product, the filter context would still be applied to the measure. It's only the year filter that does not apply to that measure.
What is a useful way to learn M?
Create the steps in the PQE using the user interface. Then, see what corresponding M code was written.
From how many datasets and reports can a dashboard be built?
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.
Who can help data scientist speed up the experimentation process?
Data engineers can use their skills to wrangle data
Describe a 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.
What does the 1:* relationship describe?
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
What are the basic elements of creating a mobile-optimized version of a report?
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.
How might you modify settings to optimize them for mobile use?
Determine whether you want to allow report readers to select only one item or multiple items. Decide on the orientation of the slicer, whether it should be vertical, horizontal, or responsive (responsive slicers must be horizontal). If you make the slicer responsive, as you change its size and shape, it shows more or fewer options. If you make the slicer small enough, it becomes a filter icon on the report page.
Consider the scenario where you are analyzing data for a shipping warehouse. You notice that the number of orders increased above average for a specific product category. You first want to identify the product category. Then, you want to ask what questions about the outlier?
Did above average shipments happen that day? Did this anomaly occur in a specific warehouse? Did a single event cause the increase in orders for that specific category? Did this event occur on other days in the last month, quarter, year, or prior year?
This table type contains the details about the data in fact tables: products, locations, employees, and order types.
Dimension tables
Which method connects directly to data in its source repository from within Power BI Desktop. It is an alternative to importing data into Power BI Desktop.
DirectQuery
Which storage mode poses a risk to the quality of your model's performance. To optimize performance in this situation, you need to have control over, or access to, the source database.
DirectQuery
What are two ways to get data for a new report?
Discover content - two ways: - My Organization - discover apps published by other people in your organization - Services -choose apps from online service that you use Create content - two ways: - Files - bring in your reports, workbooks, or data from Excel, Power BI Desktop or CSV files - Databases - User Power BI Desktop to connect to data in Azure SQL Database and more
Give examples of the types of connectors that are built into Power BI.
Excel SQL Server Azure Oracle Facebook Salesforce MailChimp
What is the difference between a slicer and a filter in the filter pane?
Fields added to the Filters pane can achieve the same result as a slicer. One difference is they don't take up space on the report page. Another difference is that they can be configured for more advanced filtering requirements.
What are the sections in the Filters pane?
Filters on this visual - Filters that apply to the selected visual and nothing else. This section only displays if you have a visual selected. Filters on this page - Filters that apply to the whole page that you currently have open. Filters on all pages - Filters that apply to all the pages in your report. Drillthrough - Filters that apply to a single entity in a report.
When working with *:* relationships, what are your choices for filter directions? Why are these relationships complicated?
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. For this reason, many-to-many relationships and/or bi-directional relationships are complicated. Unless you are certain what your data looks like when aggregated, these types of open-ended relationships with multiple filtering directions can introduce multiple paths through the data.
Describe how to resolve the "We couldn't find any data formatted as a table" error when importing data from 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: Open your Excel workbook, and highlight the data that you want to import. Press the Ctrl-T keyboard shortcut. The first row will likely be your column headers. 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.
What are the steps to connect to data in a relational database using Power BI Desktop?
Get data > select the applicable relational database type (e.g. SQL Server, Analysis Services, etc.) > enter your database server name and database name > select either 'Import' or 'DirectQuery' > select other advanced options > sign-in using one of the three options: 1) Windows - use your Windows account (Azure AD credentials) 2) Database - use your database credentials. If the database administrator gave you a unique sign-in to the database. 3) Microsoft account - User your Microsoft account credentials. This option is often used for Azure services > enter your UN and PWD > click 'Connect' > [optional] select the a table or entity to preview its contents > select the check box(es) of the table(s) that you want to bring in to Power BI Desktop > select either 'Load' or 'Transform Data'
How do you connect to a relational database in Power BI using a SQL command
Get data > select the applicable relational database type (e.g. SQL Server, Analysis Services, etc.) > enter your database server name and database name > select the arrow next to 'Advanced options' > in the 'SQL statement' box, write your query > select OK select either 'Import' or 'DirectQuery' > select other advanced options > sign-in using one of the three options: 1) Windows - use your Windows account (Azure AD credentials) 2) Database - use your database credentials. If the database administrator gave you a unique sign-in to the database. 3) Microsoft account - User your Microsoft account credentials. This option is often used for Azure services > enter your UN and PWD > click 'Connect' > [optional] select the a table or entity to preview its contents > select the check box(es) of the table(s) that you want to bring in to Power BI Desktop > select either 'Load' or 'Transform Data'
What actions can you assign to a button?
Go to the Action slider near the bottom of the Visualizations pane and move the slider to the On position. Then, expand the Actions section to view the options. The options for the button action types are as follows, some of which are explained in more detail in the subsequent sections. 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. Bookmark - Presents the report page that's associated with a bookmark that is defined for the current report. Drill through - Brings the user to a drillthrough page that is filtered to their selection, without using bookmarks. 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. 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. 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.
What are the steps to connect to data in a file in Power BI Desktop?
Home > select 'Get data' > from the list, select the file type you require > depending on your selection, find and open your data source > [optional] sign-in > [optional] select a table or entity to preview its contents > select the check box(es) of the table(s) that you want to bring in to Power BI > choose from the 'Load' and 'Transform Data' buttons
What are the steps to change the data source connection in the 'Data source settings' in Power Query?
Home > Data source settings > Change Source...
In the PQE, what are the steps to merge two queries?
Home ribbon > Merge Queries > Merge Queries as New > chose the table you want from the drop-down list > select the matching column between the tables > chose how to join the tables together (Left, Full, Inner) > click OK
In Model view, how do you view the related columns?
Hover the cursor over the relationship to reveal the related columns.
How do you edit a tile on a dashboard?
Hover the cursor over the tile, and then at the top-right of the tile, click the ellipsis, and then select Edit Details. In the Tile Details pane (located at the right), select the box you wish to edit, and enter or select the relevant information. At the bottom of the pane, click Apply.
When might you not be able to import data into Power BI?
However, sometimes there may be security requirements around your data that make it impossible to directly import a copy. Or your datasets may simply be too large and would take too long to load into Power BI, and you want to avoid creating a performance bottleneck.
Why might you decide to use aggregations in your data model?
If you are dealing with a large amount of data (big data), aggregations will provide better query performance and help you analyze and reveal the insights of this large data. Aggregated data is cached and, therefore, uses a fraction of the resources that are required for detailed data. If you are experiencing a slow refresh, aggregations will help you speed up the refresh process. The smaller cache size reduces the refresh time, so data gets to users faster. Instead of refreshing what could be millions of rows, you would refresh a smaller amount of data instead. If you have a large data model, aggregations can help you reduce and maintain the size of your model. If you anticipate your data model growing in size in the future, you can use aggregations as a proactive step toward future proofing your data model by lessening the potential for performance and refresh issues and overall query problems.
What are the ways you can create aggregations in your data model?
If you have access to the database, you could create a table with the aggregation and then import that table into Power BI Desktop. If you have access to the database, you could create a view for the aggregation and then import that view into Power BI Desktop. In Power BI Desktop, you can use Power Query Editor to create the aggregations step-by-step.
What are two indications that your dashboard has been refreshed?
If you have configured a tile to include the refresh date, then in the tile's subtitle, you will see the data was refreshed. You may also notice that there is now new data in the visual.
After using Performance analyzer, what can you do to examine the DAX queries further?
If you want to examine the DAX query, select Copy query and then paste it into DAX Studio for further analysis. DAX Studio is a free, open-source tool that is provided by another source that you can download and install on your computer.
What are sings that the potential exists for poor performance?
If your data model has multiple tables, complex relationships, intricate calculations, multiple visuals, and redundant data
What is the drawback of defining a higher level of granularity?
If your users want to drill into every single transaction, summarizing the granularity will prevent them from doing that, which can have a negative impact on the user experience.
The use of variables in your data model provides what advantages?
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. Improved readability - Variables have short, self-describing names and are used in place of an ambiguous, multi-worded expression. You might find it easier to read and understand the formulas when variables are used. Simplified debugging - You can use variables to debug a formula and test expressions, which can be helpful during troubleshooting. 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.
Give an example of how you would create a hierarchy in Model view.
In Model view, in the Fields pane, if necessary, expand the Product table. To create a hierarchy, in the Fields pane, right-click the Category column, and then select Create Hierarchy. In the Properties pane (to the left of the Fields pane), in the Name box, replace the text with Products. To add the second level to the hierarchy, in the Hierarchy dropdown list, select Subcategory. To add the third level to the hierarchy, in the Hierarchy dropdown list, select Product. To complete the hierarchy design, click Apply Level Changes.
How do you create a calendar table using the DAX function CALENDAR()?
In Power BI Desktop, go to the Modeling tab on the ribbon. Select New Table, and then enter in the following DAX formula: Dates = CALENDAR(DATE(2011, 5, 31), DATE(2021, 5, 31)) You can add columns by selecting New Column on the ribbon and entering the following DAX equation, which will retrieve the year from your Date table: Year = YEAR(Dates[Date])
Where do you create Power BI paginated reports?
In Power BI Report Builder
Why is it important to understand how and when to apply different calculation techniques?
In Power BI, you can use different calculation techniques and functions to create measures or calculated columns. Primarily, you will be able to achieve the same result by using these techniques; however, the key is to know how and when to apply them. By having a basic understanding of when and how to use which technique, you will be able to create robust and high-performance data models.
What is one way that reports and dashboards differ? They are the same. In reports, you can use the slicers and filter by selecting a data point on a visual; in dashboards, you can only filter a dashboard tile in focus mode, but can't save the filter. In reports, you can have multiple pages; in dashboards, you can have only one page. You can only build reports and dashboards in Power BI service.
In reports, you can have multiple pages; in dashboards, you can have only one page.
Where can you see field descriptions?
In the Fields pane, description text is revealed in a tooltip when a report author hovers their cursor over the field.
What visual indication do you have that a table is a calculated table?
In the Fields pane, notice that the table icon is a shade of blue
How do you mark a table as date table?
In the Fields pane, select the Date table (not field). On the Table Tools contextual ribbon, from inside the Calendars group, click Mark as Date Table, and then select Mark as Date Table. In the Mark as Date Table window, in the Date Column dropdown list, select Date. Click OK. Power BI Desktop now understands that this table defines date (time). This is important when relying on time intelligence calculations.
How do you add a custom visual?
In the Visualizations pane, select the Get more visuals icon and then select Get more visuals. On the Power BI Visuals window that displays, select the Advanced Analytics category. Browse the available options and then select the Add button for the visual that you want to import. In this example, you will add the Variance Chart custom visual. When the visual has been imported, an icon for the new custom visual displays under the other visual icons in the Visualizations pane. You can then add fields to the new visual and customize it in the same way that you would for any other visual. In this example, you will add Country to the Category field, Gross Sales to the Primary Value field, and Net Sales to the Comparison Value field.
Give an example where a key influencer visual would be useful?
In this example, you've built several visuals for the Customer Service team, and now you want to understand the factors that most influence your metrics. Specifically, you want to figure out what factors are affecting the total number of logged tickets. One factor might be the client type and another might be location, but you're uncertain. The Key influencers visual will find that information for you.
Give an example where a Decomposition tree visual would be useful.
In this example, you've built visuals for the Supply Chain team, but the visuals do not answer all the team's questions. In particular, the team wants to be able to analyze the percentage of products that the organization has on back order, in other words, the percentage of products that are out of stock. The Decomposition Tree visual can help you accomplish that task.
Describe flattening the hieararchy
In this process, you are 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.
Where might a stream of telemetry data be sent in their native, messy format?
IoT hub
Why is defining data granularity important?
It can have a big impact on the performance and usability of your Power BI reports and visuals.
How is the Q&A feature valuable to report authors and users?
It gives you ideas for the type of visuals that you can display in your report and lets you quickly add those visuals. Additionally, it gives your report users an effective tool that they can use to get quick answers to their questions about the data, independently. This self-help aspect to Power BI saves time for everyone involved.
Why is DirectQuery useful?
It solves the problem when you cannot import data into Power BI. It ensures you always viewing the most recent version of the data instead of slowing down performance by having to load large amounts of data into Power BI, you can create a connection to source, solving data latency issues
What should you consider when configuring visual interactions?
Keep in mind that the number of interactions between your visuals will impact the performance of your report. To optimize the performance of your report, consider the query reduction options that are available within Power BI Desktop. You have the option to send fewer queries (which will reduce query chattiness) by disabling cross-highlighting/filtering by default. You can also disable certain interactions that would result in a poor experience, if the resulting queries take a long time to run.
What are the cardinality options in Power BI?
Many-to-one (*:1) - This relationship is the most common, default type. It means that the column in one table can have more than one instance of a value, and the other related table, often known as the lookup table, has only one instance of a value. One-to-one (1:1) - In this relationship type, the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value. One-to-many (1:*) - In this relationship type, the column in one table has only one instance of a particular value, and the other related table can have more than one instance of a value. Many-to-many (:) - With composite models, you can establish a many-to-many relationship between tables, which removes requirements for unique values in tables. It also removes previous workarounds, such as introducing new tables only to establish relationships.
What are the advantages of having clean data?
Measures and columns produce more accurate results when they perform aggregations and calculations. Tables are organized, where users can find the data in an intuitive manner. Duplicates are removed, making data navigation simpler. It will also produce columns that can be used in slicers and filters. A complicated column can be split into two, simpler columns. Multiple columns can be combined into one column for readability. Codes and integers can be replaced with human readable values.
How do you create a calculated table?
Modeling tab > New table > Enter table name + "=" + table definition Press ENTER A calculated table is created by first entering the table name, followed by the equals symbol (=), followed by a DAX formula that returns a table. The table name cannot already exist in the data model. Calculated tables are defined by using a DAX formula which returns a table. The formula bar supports entering a valid DAX formula. It includes features like auto-complete, Intellisense and color-coding, enabling you to quickly and accurately enter the formula. When a table definition creates a copy a table, it copies the data only, however properties like visibility, formatting, etc. are not copied.
What are the benefits of query folding?
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. 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.
What do Power BI-hosted models that import data require?
Must be fully loaded into memory so that they can be queried. Require refresh to keep data current, and must involve gateways when source data isn't accessible directly over the internet.
Where can you see a list of the steps applied to a query in the PQE?
On the right side (pane) called "Applied Steps"
Describe both cross-filter directions or 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.
Describe a 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. For a one-to-many or many-to-one relationship, the cross-filter direction will be from the "one" side, meaning that the filtering will occur in the table that has unique values.
Give an examples of two tables you might want to merge.
Order Header and Order Detail Product and Product Category
What are the key principles of the Web Content Accessibility Guidelines (WCAG)?
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.
What Power BI tool helps fix problems and streamlines the process of addressing performance issues?
Performance Analyzer
What can you to help you find out how each of your report elements are performing when users interact with them?
Performance analyzer - it helps you identify the elements that are contributing to your performance issues, which can be useful during troubleshooting.
What are the core categories of data analytics?
Planning: What is our plan? Descriptive: What happened? Diagnostic: Why did it happen? Predictive: What will happen next? Prescriptive: What should be done about it? Cognitive - mimic the human brain by drawing inferences from existing data and patterns, drawing conclusions based on existing knowledge bases, and inserting this back into the knowledge base for future inferences - a self-learning feedback loop.
What connectivity do Power BI-hosted models that use DirectQuery storage mode require?
Power BI-hosted models that use DirectQuery storage mode require connectivity to the source data. When the model is queried, Power BI issues queries to the source data to retrieve current data. This mode must involve gateways when source data isn't accessible directly over the internet.
Name the five key areas that data analysts engage in.
Prepare Analyze Model Manage Visualize
What are other ways to optimize query performance in Power BI?
Process as much data as possible in the original data source. Power Query and Power Query Editor allow you to process the data; however, the processing power that is required to complete this task might lower performance in other areas of your reports. Generally, a good practice is to process, as much as possible, in the native data source. Use native SQL queries. When using DirectQuery for SQL databases, such as the case for our scenario, make sure that you are not pulling data from stored procedures or common table expressions (CTEs). Separate date and time, if bound together. If any of your tables have columns that combine date and time, make sure that you separate them into distinct columns before importing them into Power BI. This approach will increase compression abilities.
What query reduction options can help performance, especially with DirectQuery tables and models?
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. 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. 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: - Add an apply button to all basic filters to apply changes when you're ready - Add a single apply button to the filter pane to apply changes at once (preview)
How do you edit a group in a bar chart?
Right-click the group field in either the Legend bucket or the Fields pane, and then select Edit Groups. In the Groups window that displays is a list of the groups and the different items within those groups. The following image shows the States with Sales > 500k group and its members, along with the Other group (States with Sales < 500k) that contains all other values that have not been put into the first group. If you refresh your data, and new items appear in the ungrouped values list, they'll all go into the Other group. You can now make changes to the group. You can rename any group by double-clicking the group title in the Groups and members section and entering a new name. You can add ungrouped values into an existing group, remove values from an existing group, and create a new group.
Write a DAX measure to calculate the percentage of all sales in each country. How does it work?
Sales % Country = DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region[Region]) ) ) the denominator modifies the filter context by removing filters on the Region column of the Region table. It means that any filters applied to the group or country columns are preserved. It will achieve a result which represents the sales as a percentage of country. the Sales % Country measure only returns a value when a region is in scope.
How would you write a DAX formula to calculate the sum of sales for all regions, removing any filter on region in the current filter context? How does it work?
Sales All Region = CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Region)) The CALCULATE() function is a powerful function used to manipulate the filter context. The first argument takes an expression or a measure (a measure is just a named expression). Subsequent arguments allow modifying the filter context. The REMOVEFILTERS() function removes active filters. It can take either no arguments, or a table, a column, or multiple columns as its argument. In this formula, the measure evaluates the sum of the Sales column in a modified filter context, which removes any filters applied to the Region table. The measure computes the total of all region sales for each region, country (subtotal) and group (subtotal).
What are the limitations of DirectQuery connections?
Security - If you use multiple data sources in a DirectQuery model, it is important to understand how data moves between the underlying data sources and the associated security implications. You should also identify if security rules are applicable to the data in your underlying source because, in Power BI, every user can see that data. 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. Modeling - Some of the modeling capabilities that you have with imported data aren't available, or are limited, when you use DirectQuery. Reporting -- Almost all the reporting capabilities that you have with imported data are also supported for DirectQuery models, provided that the underlying source offers a suitable level of performance. 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 do you create a common date table using Power Query?
Select Transform Data in Power BI Desktop, which will direct you to Power Query. In the blank space of the left Queries pane, right-click to open the following drop-down menu, where you will select New Query > Blank Query. In the resulting New Query view, enter the following M-formula to build a calendar table: = List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0) In this example, you want the start date to reflect the earliest date that you have in your data: May 31, 2011. Additionally, you want to see dates for the next 10 years, including dates in the future. This approach ensures that, as new sales data flows in, you won't have to re-create this table. You can also change duration. In this case, you want a data point for every day, but you can also increment by hours, minutes, and seconds. The following figure shows the result.
What are two ways to add a chart to your paginated report?
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.
What can you do with dashboards?
Share them with other users or groups
When might you use a scatter chart?
Show relationships between two numerical values. Plot two groups of numbers as one series of x and y coordinates. Turn the horizontal axis into a logarithmic scale. Display worksheet data that includes pairs or grouped sets of values. Show patterns in large sets of data, for example, by showing linear or non-linear trends, clusters, and outliers. Compare large numbers of data points without regard to time. The more data that you include in a scatter chart, the better the comparisons that you can make.
Describe a scenario in which a NoSQL database is being used.
Software developers at Tailwind Traders created an application to manage shipping and tracking products from their warehouses that uses CosmosDB, a NoSQL database, as the data repository. This application uses Cosmos DB to store JSON documents, which are open standard file formats that are primarily used to transmit data between a server and web application. You need to import this data into a Power BI data model for reporting.
Explain how using variables can improve performance of a data model.
Some expressions involve the use of many nested functions and the reuse of expression logic. These expressions take a longer time to process and are difficult to read and, therefore, troubleshoot. If you use variables, you can save query processing time. l.
What should you note about using custom visuals?
Some organizations prefer not to use custom visuals, or only permit certain custom visuals, for security or other reasons. Before you import custom visuals, check with your organization to see whether they are allowed or not. 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.
Describe causes and resolutions for data type errors when refreshing data.
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. Instead of using this query: SELECT CustomerPostalCode FROM Sales.Customers Use this query: SELECT CAST(CustomerPostalCode as varchar(10)) FROM Sales.Customers By specifying the correct type at the data source, you eliminate many of these common data source errors. You may encounter different types of errors in Power BI that are caused by the diverse data source systems where your data resides. If you encounter an error that was not discussed in the preceding sections, you can search Microsoft documentation for the error message to find the resolution you need.
What are three ways you can create a date table?
Source data DAX Power Query
The specific schema that is the foundation of organized reports
Star schemas
How do you create a KPI?
Start by adding the KPI visual to the design surface. When configuring the KPI visual, enter the unit of measurement that you are tracking in the Indicator prompt. Then, enter the goal under Target goals and select the time series from the Trend axis drop-down list, as shown in the following screenshot. KPIs work best in a series, for instance, showing the daily, monthly, and yearly goals in the section of a Power BI report.
How do you create clusters?
Start by adding the scatter chart visualization to your report and then add the required fields to the visual. In this example, you will add the Order Qty field to the x-axis, the Sales field to the y-axis, and the Product Name field to the Legend section. The following image shows considerable data in the scatter chart, so it is difficult to discern any natural groups. To apply clustering to your 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. Enter the number of clusters that you want (3) into the box and then select OK. Power BI will run the clustering algorithm and create a new categorical field with the different cluster groups in it. Now, when you look at the visual, you can clearly see the clusters that are in your data and proceed to perform analysis on them. The new cluster field is added to your scatter chart's Legend field well bucket, which you can now use as a source of cross-highlighting like any other Legend field. You can also find the new cluster field in your field list and use it in new visuals, just like any other field. If you want to edit the cluster, right-click the cluster field and select Edit clusters.
How would you show the top 10 products using a DAX function?
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] )
Starting with a query in the PQE, how would you create an aggregation on the current query?
Start by selecting Choose Columns on the Home tab. select the Group By option on the Home tab. On the window that displays, select the column that you want to group by and enter name for the new column Select the Advanced option and then select the Add aggregation button to display another column row. Enter a name for the aggregation column, select the operation of the column, and then select the column to which you want to link the aggregation. Repeat these steps until you have added all the aggregations and then select OK. Select the Close and Apply button to close Power Query Editor and apply the changes to your data model. Return to the Power BI Desktop page and then select the Refresh button to see the results.
What is profiling data about?
Studying the nuances of the data: determining anomalies examining and developing the underlying data structures querying data statistics such as row counts, value distributions, minimum and maximum values, averages, and so on
How do you create an automatic Date table?
Switch to Data view. On the Home ribbon tab, from inside the Calculations group, click New Table. In the formula bar, enter the following, and then press Enter: Date = CALENDARAUTO(6) The CALENDARAUTO() function returns a single-column table consisting of date values. The "auto" behavior scans all data model date columns to determine the earliest and latest date values stored in the data model. It then creates one row for each date within this range, extending the range in either direction to ensure full years of data is stored. This function can take a single optional argument which is the last month number of a year. When omitted, the value is 12, meaning that December is the last month of the year. In this case 6 is entered, meaning that June is the last month of the year. The dates shown are formatted using US regional settings (i.e. mm/dd/yyyy).
How do you group columns or measures in a folder?
Switch to Model view, and then multi-select the four price measures > Assign to a display folder named Pricing
What DAX function returns the top N rows of a specified table?
TOPN
What DAX function allows you to show totals increment for each month but then reset when the year changes? What are it's parameters?
TOTALYTD This function takes an argument for the type of calculation. You can use the SUM function to get the Total Price, as you've done throughout this module. The second argument that you want to operate over is the Dates field. You can use your Dates table and add this measure to your visual, and you'll get the running total result that you're looking for. You can use all functions with YTD, MTD, and QTD in a similar fashion.
Describe a scenario where data from different operational data must be combine and reported on.
Tailwind Traders uses SharePoint to collaborate and store sales data. It's the start of the new financial year and the sales managers want to enter new goals for the sales team. The form that the leadership uses exists in SharePoint. You are required to establish a connection to this data within Power BI Desktop, so that the sales goals can be used alongside other sales data to determine the health of the sales pipeline.
In the PQE, which pane displays the number of active queries in addition to the name of the query.
The Queries pane
What does this formula do? Sales YoY Growth = VAR SalesPriorYear = CALCULATE( SUM(Sales[Sales]), PARALLELPERIOD( 'Date'[Date], -12, MONTH ) ) RETURN SalesPriorYear
The SalesPriorYear variable is assigned an expression which calculates the sum of the Sales column in a modified context that uses the PARALLELPERIOD() function to shift 12 months back from each date in filter context.
What is the benefit to a business of making fast, precise decisions?
The business will be more competitive, with a better advantage
How are calculations built in Azure Analysis Services?
The calculations are built using data analysis expressions (DAX).
What should you be aware of about custom visuals?
The certified status means that the visual meets the Microsoft Power BI team code requirements; the visual is tested to verify that it doesn't access external services or resources and that it follows secure coding patterns and guidelines. The certification process is optional, so an uncertified visual is not necessarily unsafe to use. Some organizations prefer not to use custom visuals for security or other reasons. Before you import custom visuals, check with your organization to see whether custom visuals are allowed or not. 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.
What happens when you import data from a local Excel file?
The file is not moved into Power BI A link does not remain A new dataset is created in Power BI, and the data in the Excel file is copied into it. Changes to the original Excel are not reflected in your Power BI dataset
How to you add a data classification to your dashboard?
The first action that you will need to take is to ensure that your organization's custom data classification settings are added into the Power BI system. Data classification is done by an administrator. Hover over the ellipsis (...) by the name of the dashboard and then select Settings. In the resulting window, under Dashboards, you can use the drop-down menu under Data classification to choose how you want the data to be classified. After you have made this selection, the dashboard will follow the default data rules or the rules that you have established under Tenant settings. When you open the dashboard, it will now be marked by this new data classification, as shown in the following screenshot. You have now added custom data classification to your dashboards and the Sales team is pleased. Data classification is an important feature because it allows you to add a level of security to your Power BI dashboards. Additionally, because you can personalize them in any way that your organization requires, data classification also adds a layer of personalization to your dashboards.
In a forecast on a line chart, what does the gray area represent? What does the width of the gray area indicate?
The gray area represents the confidence. The wider the confidence, the less stable and, therefore, the less accurate the forecast is likely to be.
What is data analysis?
The process of reducing, organizing, and giving meaning to the data that has been collected the process of... - identifying - cleaning - transforming - modeling ...data to discover meaningful and useful information.
The Q&A visualization consists of the what four core components?
The question box, where users enter their question and are shown suggestions to help them complete the question. A pre-populated list of suggested questions. An icon that users can select to convert the Q&A visual into a standard visual. 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. The following example illustrates how to implement Net sales by countr
In PBI Report Builder, what happens when a dataset query includes a parameter?
The report parameter was added automatically when the dataset was created.
What does the size of your data model impact?
The size of your data model has a direct impact on its performance; a smaller sized data model uses less resources (memory) and achieves faster data refresh, calculations, and rendering of visuals in reports.
Why are parameters important in Power BI paginated reports? They allow the report developer to control the refresh interval of the report. They allow the user to control aspects of how the report is rendered when the report is run. They are required so that Power BI can call the paginated report.
They allow the user to control aspects of how the report is rendered when the report is run.
How do built-in data categorizations help?
They can provide hints to the report designer. For example, categorizing the column as country or region, provides more accurate information when rendering a map visualization.
Describe how you can use the information provided in the Columns statistics pane in the Column profile.
This column provides several different values, including the count of rows, which is important when verifying whether the importing of your data was successful. For example, if your original database had 100 rows, you could use this row count to verify that 100 rows were, in fact, imported correctly. Additionally, this row count will show how many rows that Power BI has deemed as being: - outliers (and therefore "errors"), - empty rows and strings - the min and max, which will tell you the smallest and largest value in a column, respectively. This distinction is particularly important in the case of numeric data because it will immediately notify you if you have a maximum value that is beyond what your business identifies as a "maximum." This feature calls to your attention these values, which means that you can then focus your efforts when delving deeper into the data. In the case where data was in the text column, as seen in the previous image, the minimum value is the first value and the maximum value is the last value when in alphabetical order. On a numeric column, this will also include how many zeroes and null values exist, along with the average value in the column, the standard deviation of the values in the column, and how many even and odd values are in the column. These statistics give you an idea of the distribution of data within the column, and are important because they summarize the data in the column and serve as a starting point to determine what the outliers are.
Describe why you might get a query "timeout expired" error.
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. Relational source systems often have many people who are concurrently using the same data in the same database. Some relational systems and their administrators seek to limit a user from monopolizing all hardware resources by setting a query timeout. These timeouts can be configured for any timespan, from as little as five seconds to as much as 30 minutes or more.
Describe what this M code does for a calculated column does: if [TotalProductCost] = null then [OrderQuantity] * [StandardCost] else [TotalProductCost]
This expression tests if the TotalProductCost value is missing. If it is, produce a value by multiplying the OrderQuantity value by the StandardCost value; otherwise, it uses the existing TotalProductCost value.
What happens when you connect to SharePoint - Team Sites in Power BI?
This is similar to connecting to OneDrive for Business. Except, you specify a URL instead of a root folder.
What happens when you connect to OneDrive - Personal from Power BI?
This is similar to using OneDrive for Business, except you will need to sign in with your personal OneDrive account, and select the 'Keep me signed in' option. This option may not be allowed in your organization. Check with your system administrator.
What are the common visual formatting options?
Title, Background, Border
What are some ways to use navigation buttons?
To design the navigation within your report, you can create a new Navigation page in your report and add navigation buttons on that page. You can also use a combination of both options. When users select one of these buttons, they are brought directly to a different page within the report, which you can hide so that it can only be accessed through the Navigation page buttons.
Before enabling Cross-report drillthrough, what must you validate?
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.
How do you import an R or Python visual?
To import an R or Python visual from AppSource, in the Visualizations pane, select the Get more visuals icon and then select Get more visuals. On the window that displays, locate and select the R or Python visual that you want to import and then select Add. The new visual icon will appear under the other visual icons in the Visualizations pane.
What should you know about report viewability and accessibility?
To make your reports even more accessible, ensure that enough contrast exists between the text and background colors; the contrast ratio should be at least 4.5:1. Several tools are available for you to use to check your report colors, such as Color Contrast Analyzer, WebAIM, and Accessible Colors. Some of your report viewers might have color vision deficiencies. Using fewer colors or a monochrome palette in your report can help mitigate the creation of inaccessible reports.
How can you interact with the a visual in the Quick insights page?
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. You can then perform the following actions: Filter the visualization by using the available options in the Filters panel. Pin the insight card to a dashboard by selecting Pin visual. 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. Return to the original insights canvas by selecting Exit Focus mode in the upper-left corner.
In what circumstances might you combine tables into a single table?
Too many tables exist, making it difficult to navigate an overly-complicated data model. Several tables have a similar role. A table has only a column or two that can fit into a different table. You want to use several columns from different tables in a custom column.
What DAX function allows you to make additional calculations on inactive relationships by overriding the default active relationship between two tables in a DAX expression?
USERLATIONSHIP as shown in the following example: Sales by Ship Date = CALCULATE(Sales[TotalPrice], USERELATIONSHIP('Calendar'[Date], Sales[ShipDate]))
What options are available in the PQE to make performance enhancements to queries?
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. 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. 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. 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. 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. * 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. * 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. * Column profile - Shows more statistics for the column and a chart showing the distribution of the unique items.
What is the 'Connect live' option for Azure Analysis Services?
Using the Connect live option helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI.
Why do some authors hide visible numeric columns and instead expose aggregation logic defined by measures?
Visible numeric columns allow report authors to decide at report design time how a column will summarize (or not). This can result in inappropriate reporting. Some data modelers do not like leaving things to chance, however, and choose to hide these columns and instead expose aggregation logic defined by measures. This is the approach you will now take in this lab. This design approach ensures that report authors won't inappropriately aggregate prices, for example, by summing them.
What can waterfall charts be used for?
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 accessibility standards is Power BI committed to?
Web Content Accessibility Guidelines (WCAG)
What are ways to analyze customer buying habits?
Website analytics can help you track customer search queries—the searches people use that brings them to your website. Knowing what brought someone to your business's website puts you into the mindset of the customer and can give you valuable insight into the influences that drive sales. Another way to track customer buying habits is through the use of loyalty programs. Loyalty programs offer an incentive to get return customers, which can show patterns in sales and help you build a rapport with customers and get to know them better.
What are questions a report authors should always ask themselves?
What purpose is this report for? Who is using the report? How can I help people do a better job? What is the most important information and how can I highlight it? Is this report readable? Can people change the elements that they need to if their questions change? Do I have visuals that are distracting from the core message of the report? Is this report staying focused in a single topic or only a few topics? Am I providing all information that the user expects to see in the report?
How can you turn the results of Q&A into a visual?
When Power BI answers a question, and you find the visual result to be engaging or helpful, you can add it as a standard visual to your report. For example, if you review the questions that are being asked, and you see that several users are asking the same question, you can add the answer to this question as a standard visual in the report so they no longer have to ask the question. Similarly, you can also use the Q&A feature to start building your report by asking questions and adopting the suggested visual result formats from Power BI. To turn a Q&A result into a standard visual, select the icon next to the question box. The Q&A feature is unique in that it does not require users to have knowledge of Power BI to use the visual; users can ask their questions and they, too, can create insightful visuals.
Give an example of how you might train a Q&A visual.
When a red underline occurs, Power BI is telling you that it does not understand this term. Knowing that the word country is not used in your dataset, you decide to use the term region instead and ultimately discover why the question is not being answered. Accordingly, you realize that you need to teach Power BI what you mean by adding a new term to its thesaurus. Select the settings icon to the right of the question box to open 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. The following image shows that Power BI displays a preview result so that you can see if this new term will return the results that you are looking for. If this result is correct, select Save. Now, when users search for sales by country, Power BI will know that they really mean sales by region and will automatically display the associated data in the visual.
Funnel charts are great options in what contexts?
When the data is sequential and moves through at least four stages. When the number of items in the first stage is expected to be greater than the number of items in the final stage. 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.
How do you run the Performance analyzer?
When you have cleared the caches and opened the report on the blank page, to run the Performance analyzer, go to the View tab, select Performance analyzer, and then select 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 you are finished, select the Stop button. Then, you can analyze the results in the Performance analyzer pane. Performance results of each item in the report will display, in milliseconds, under the Duration column. You can expand an item in the list to view more detailed information and identify the exact cause of the issue, such as the DAX query, the visual display, or something else (other).
How do DirectQuery connections behave?
When you initially use the Get Data feature in Power BI Desktop, you will select the source. 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. When you load the data, 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. The time it takes to refresh the visual depends on the performance of the underlying data source. 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. The necessary queries are resent for each visual, and the visuals are updated accordingly. 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. 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. 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. When you open a dashboard, the tiles reflect the data at the time of the last refresh and might not include the latest changes that are made to the underlying data source. You can always refresh an open dashboard to ensure that it's up-to-date.
What are two ways relationships are created in PBI?
When you load data, Power BI automatically looks for relationships that exist within the data by matching column names. You can also use Manage Relationships to edit these options manually.
If you attempt to create a date hierarchy on a column that is not of type date, what will you see?
When you right-click on the column, you will not see the option to create a hierarchy.
When creating a chart in a paginated report, how do you add columns to the chart?
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 happens if you create a security role called Salespeople?
When you share reports and dashboards to non-owners of the dataset, their account (or a security group of which they're a member) must be mapped to the Salespeople role.
Describe how to resolve a "Could not find file" error.
While importing data from a file, you may get the "Could not find file" error. 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. Open Power Query by selecting the Transform Data button in Power BI. Highlight the query that is creating the error. On the left, under Query Settings, select the gear icon next to Source. Change the file location to the new location.
Given an employee and manager column, how would you create the hierarchical path for each employee?
While on the table, go to the Modeling tab and select New Column. In the resulting formula bar, enter the following function, which creates the text path between the employee and manager. This action creates a calculated column in DAX. Path = PATH(Employee[Employee ID], Employee[Manager ID]) The completed path between the employee and the manager appears in the new column, as shown in the following screenshot.
In the Model view, how can you bulk update properties, such as hiding columns, or formatting values?
While pressing the Ctrl key, select the columns. Then, set the properties for all fields selected.
Do you need to import custom visuals each time you want to use them when you are developing a new report?
Yes, custom visuals must be imported from AppSource each time you start developing a new report.
Does PBI allow you to stream data and update dashboards as soon as the data is logged?
Yes, with Power BI's real-time streaming capabilities
Can you access the Q&A feature by using buttons? No, to use the Q&A feature, you will need to add the Q&A visual to your reporting canvas. Yes, you can access the Q&A feature by selecting Q&A button type. 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. .
Yes, you can access the Q&A feature by selecting Q&A button type. j
Can you use bookmarks to create a slide show in Power BI? No, you cannot, because bookmarks are not dynamic. Yes, you can, by adding buttons as navigation to go between saved bookmarks. No, you will require a specific visual to achieve this task.
Yes, you can, by adding buttons as navigation to go between saved bookmarks.
How can you refine the groups that are aggregated based on the underlying data in Power BI
You can also create new groups by grouping two or more data points in a visual or putting values into equal-sized groups (binning).
What do you need to know when connecting to a SharePoint Online List?
You can copy the URL from your SharePoint site and paste it into the connection window in Power BI. 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. Depending on the URL that you copied, you might need to delete the last part of your URL, as illustrated in the following image.
How might you resolve a "timeout expired" error?
You can resolve this error by pulling fewer columns or rows from a single table. While you are writing SQL statements, it might be a common practice to include groupings and aggregations. You can also join multiple tables in a single SQL statement. Additionally, you can perform complicated subqueries and nested queries in a single statement. These complexities add to the query processing requirements of the relational system and can greatly elongate the time of implementation. If you need the rows, columns, and complexity, consider taking small chunks of data and then bringing them back together by using Power Query. For instance, you can combine half the columns in one query and the other half in a different query. Power Query can merge those two queries back together after you are finished.
How do you set up a Drill through page?
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 fields). 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.
How can you use conditional formatting for navigation?
You can use conditional formatting to set the navigation destination based on the output of a measure. One reason that you might want to use this type of navigation method is to save space in your report. For example, rather than using multiple navigation buttons (as illustrated in the previous image), you can use a single button to navigate to different pages based on the user's selection (as illustrated in the following image). Other reasons for using this type of navigation include: To specify the logical path that your report users should take. In other words, you determine the order in which users view each page. To tell a data-driven story. For example, you could use it to give your employees a message that is backed up by the data. This method could be useful to help drive change, such as increase sales. To create a reporting portal where users can navigate to a set of reports. 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.
Give an example where you replace a specific column value with another value.
You can use the Replace Values feature in Power Query Editor to replace any value with another value in a selected column. In this example, you notice that, in the Attribute column, the month December is misspelled. You need to correct this spelling mistake. Select the column that contains the value that you want to replace (Attribute in this case), and then select Replace Values on the Transform tab. In the Value to Find box, enter the name of the value that you want to replace, and then in the Replace With box, enter the correct value name and then select OK. In Power Query, you can't select one cell and change one value, like you might have done in Excel.
Describe how context affects a measure such as Total Sales.
You may have three different visuals that use the same measure, Total Sales. Though each visual uses the same DAX measure and, therefore, the same DAX formula, the visuals produce different results. For instance, one visual shows the Total Sales measure for the entire dataset. In this dataset, Total Sales is USD1.35 million. In a second visual, Total Sales is broken down by year. For instance, in 2014, Total Sales is USD0.23 million. In a third visual, Total Sales is broken down by Product ID. With Power BI, even though the measure was only defined once, it can be used in these visuals in different ways. Each of the totals is accurate and performs quickly. It is the context of how the DAX measure is used that calculates these totals accurately. Interactions between visuals will also change how the DAX measure is calculated. For instance, if you select the second visual and then select 2015, the results will (by default) filter all visuals on the report page.
In order to create a table with unique column values, what might you do before removing duplicates?
You might consider copying the table before removing the duplicates. The Copy option is at the top of the context menu, as shown in the following screenshot. Copying the table before removing duplicates will give you a comparison of the tables and will let you use both tables, if needed.
How do you add the Play Axis visual to a visual?
You start by adding your visuals to the report page to show the sales data. Next, you will import the animation custom visual to use with the visuals. In the Visualizations pane, select the Get more visuals icon and then select Get more visuals. On the Power BI Visuals window that displays, search for play axis, and then select the Add button for the Play Axis (Dynamic Slicer) visual. A message will display, stating that the visual was successfully imported. When you return to Power BI Desktop, you'll see the new Play Axis icon in the Visualizations pane. Select the page, and then select the Play Axis icon to add that visual to the page. With the new visual selected, select the field (Quarter) that you want to use as the slicer in the Play Axis animation. Animation controls become available on the visual. You can now resize and reposition the new visual and customize its formatting so that it's consistent with the other visuals on the page. Specific formatting options that you might want to use include: 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. In the Colors section, you can change the appearance of the Play Axis visual by adjusting its overall color, or selecting the Show all option, and then changing the color of each control button. The Enable Caption On section allows you to turn on/off the text that is displayed next to the visual or adjust the formatting of it.
What should you consider about your report audience?
Your report audience might have hearing, motor, cognitive, or visual impairment. To accommodate those needs, you should create a report that offers an accessible experience, which means that the report will be simple to navigate and understand by keyboard or screen-reader users.
What do you get when you connect your account to a content service such as GitHub, GoogleAnalytics, Salesforce, or Facebook?
a collection of ready-made visuals that are pre-arranged on dashboards and reports for your organization.
What is a Power BI report?
a collection of visualizations that appear together on one or more pages. the items on the report are related to each other
What components help with storytelling?
a complimentary and accessible color scheme fonts and sizing picking the right visuals for what is being displayed
In a paginated report, before you begin adding visuals to the design surface, what must be set up?
a data source and a related data set
What is more effective to use than a radial gauge chart?
a pair of gauges with a spark line so users can see the trend and know what to do about it.
Give examples of how people in various roles might use Power BI.
a person might: -view reports and dashboards in the Power BI Service - make extensive use of Power BI Desktop for number crunching, and business report creation, and publishing to the Power BI service, sharing the reports with others - use the phone app to monitor progress on her sales quotas and drill into new sales lead details - use different elements of Power BI at different times, depending on what he/she is trying to achieve - view inventory and manufacturing progress in a real-time dashboard in the service, and also use Power BI Desktop to create reports for your own team about customer engagement statistics
Data Analysis Expressions (DAX)
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. You can use DAX to solve a number of calculations and data analysis problems, which can help you create new information from data that is already in your model.
What does publishing a report add to the PBI service?
a report and a dataset The data model has been published to become a dataset.
Give an example of a complex dataset
a single dataset that combines: - three database fields - a website table - an Excel table - online results of an email marketing campaign
The card visualization displays what?
a single value: a single data point. This type of visualization is deal 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.
What is Power BI tile?
a single visualization on a report or dashboard a rectangular box that holds an individual visual
What do Power BI datasets represent?
a source of data ready for reporting and visualization
What is a slicer visualization used for?
a standalone chart that can be used to filter the other visuals on the page. Slicers provide a more advanced and customized way of filtering, in comparison to the Filters pane, which is suited to more basic filtering operations. You can learn more about these two filtering options in another module. Slicers come in many different formats, including list, drop-down, and buttons, and they can be formatted to allow the selection of only one, many, or all available values. Slicers are ideal to: Visualize commonly used or important filters on the report canvas for easier access. Simplify your ability to see the current filtered state without having to open a drop-down list. Filter by columns that are unneeded and hidden in the data tables. Create more focused reports by putting slicers next to important visuals. Tip Using a slicer that is set to a drop-down format will defer the queries that are being sent to the dataset and can help improve performance.
Relationships between those tables with different data sources can be treated as what?
a unified dataset
What tools and techniques are used to provide insights into what might happen in the future?
a variety of statistical and machine learning techniques such as: neural networks - Neural nets are a means of doing machine learning, in which a computer learns to perform some task by analyzing training examples. Usually, the examples have been hand-labeled in advance. An object recognition system, for instance, might be fed thousands of labeled images of cars, houses, coffee cups, and so on, and it would find visual patterns in the images that consistently correlate with particular labels. decision trees -This means that Decision trees are flexible models that don't increase their number of parameters as we add more features (if we build them correctly), and they can either output a categorical prediction (like if a plant is of a certain kind or not) or a numerical prediction (like the price of a house). They are constructed using two kinds of elements: nodes and branches. At each node, one of the features of our data is evaluated in order to split the observations in the training process or to make an specific data point follow a certain path when making a prediction. regression - Suppose you're a sales manager trying to predict next month's numbers. You know that dozens, perhaps even hundreds of factors from the weather to a competitor's promotion to the rumor of a new and improved model can impact the number. Perhaps people in your organization even have a theory about what will have the biggest effect on sales. "Trust me. The more rain we have, the more we sell." "Six weeks after the competitor's promotion, sales jump." Regression analysis is a way of mathematically sorting out which of those variables does indeed have an impact. It answers the questions: Which factors matter most? Which can we ignore? How do those factors interact with each other? And, perhaps most importantly, how certain are we about all of these factors?
What is a visualization sometimes also referred to as?
a visual
What do custom visuals provide?
a wider choice of options when it comes to using advanced analytics. A custom visual might exist that solves a business problem that the standard visuals can't solve, or you might find one that presents your data in a way that the standard visuals can't.
In the PQE, what can you do in the Transform tab?
access common data transformation tasks, such as adding or removing columns, changing data types, splitting columns, and other data-driven operations
What is important to keep in mind when designing and creating reports?
accessibility who will be accessing and reading the reports no special modifications should be needed in the future
What do businesses need to be able to do with their data?
act on the data to effect change within the business
What can you do in the visual Tooltips section?
add a customized tooltip that appears when you hover over the visual, based on report pages that you create in Power BI Desktop. Tooltips is a great feature because it provides more contextual information and detail to data points on a visual. The default tooltip displays the data point's value and category, but your custom tooltips can include visuals, images, and any other collection of items that you create in the report page. The subsequent section shows an example of customizing a tooltip.
What can you do in the visual Title section?
add a title to the visual, if it does not have one, or edit the title, if it has one already. The aim of the title is to clearly describe what data is being presented in the visual. You can format the title by changing the text, text size, font, color, background, and alignment.
What are you doing when you append queries?
adding rows of data from one table or query to another table or query
What does the Analyze feature provides you with?
additional analysis that is generated by Power BI for a selected data point. You might want to use this feature to determine if Power BI has found something that you haven't seen before, or if you want Power BI to give you a different insight into your data. This feature is particularly useful for analyzing why your data distribution looks the way that it does. Instead of exploring the data manually, you can use the Analyze feature to get fast, automated, insightful analysis of your data. Note This feature does not work if you have non-numeric filters applied to your visual and/or if you have measure filters applied.
What feature allows you to ask a question by using your own words.
advanced analytics AI capabilities. One feature of Power BI is that it allows you to ask questions by using natural language, and then it will answer those questions for you.
to summarize data and present it at a higher grain (level)
aggregate
What you can do with values from fact tables.
aggregate them to create visuals
Providing set rules for what makes a good data model is difficult because__________________________
all data is different, and the usage of that data varies.
Which Power BI service features can you use stored in the Import mode?
all service features, including Q&A and Quick Insights
What is shown in the Query Settings pane?
all steps associated with a query
What are unique values values?
all the values in a column, EXCLUDING duplicates and null values
What are distinct values?
all the values in a column, including duplicates and null values
What is the benefit of prescriptive analytics?
allows business to make informed decisions in the face of uncertainty.
What does a calculated column allow you to do? Give an example.
allows you to augment the data that you bring in from different data sources by creating a column that didn't originally exist in the data source. For example, you can create a column that multiplies the unit price with the quantity
What is performance optimization?
also known as performance tuning, involves making changes to the current state of the data model so that it runs more efficiently. Essentially, when your data model is optimized, it performs better.
What is NoSQL?
also referred to as non-SQL or not only SQL or non-relational it is a flexible type of database that does not use tables to store data
If you are unable to create a relationship between two columns in two different tables, what might be the issue?
always ensure that both of the columns that you are using to participate in a relationship are sharing the same data type. Your model will never work if you try to build a relationship between two columns, where one column has a text data type and another column has an integer data type.
Business decision makers depend on what to drive better business decisions?
an accurate story
What can you use Performance analyzer to do?
analyze the duration of the queries that are being sent to the underlying source to identify the queries that are taking a long time to load. In other words, you can identify where the bottlenecks exist.
In the PQE, how is any change that you make to the column data type saved?
as a programmed step
How does the 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).
How are tables that comprise only visible measures are automatically listed?
at the top of the list.
What do you do to add a tile based on a question?
at the top-left of the dashboard, click Ask a Question About Your Data. You can use the Q&A feature to ask a question, and Power BI will respond with a visual. Click any one of the suggested questions beneath the Q&A box, in gray boxes. Review the response. To pin the response to the dashboard, at the top-right corner, click Pin Visual. When prompted to pin the tile to the dashboard, click Pin. There's a possible bug that will only allow you to pin to a new dashboard. It's because your Power BI session has reverted to your "My Workspace". If this happens, do not pin to a new dashboard. Return to your Sales Analysis workspace, open the dashboard again, and recreate the Q&A question. To return to the dashboard, at the top-left corner, click Exit Q&A.
In circumstances where you have made necessary changes to tune report performance and have established that the report is performing well, but some users are still experiencing poor performance, what other factors might be affecting performance?
bandwidth, server, firewall, and other external, uncontrollable factors. You might need to speak to the IT team in your organization to see if they can explain why users are experiencing poor performance when using your reports.
Why might you want to use a scatter chart instead of a line chart?
because it allows you to change the scale of the horizontal axis.
Why is the concept of data profiling important?
because it allows you to shape and organize the data so that interacting with the data an identifying the distribution of the data is uncomplicated, therefore helping to make your task of working with the data on the front end to develop report elements near effortless.
When looking at an existing data model, how can you tell which table is a fact table?
because it contains values which can be aggregated.
Why fact tables are usually much larger than dimension tables
because numerous events occur in fact tables
Why is data often intertwined with statistics?
because statistics are one way in which you can explore your data.
Why might you hide columns?
because they are either used by relationships or calculation logic.
What can you do once you have a dataset?
begin creating visualizations that show different portions of it in different ways
How do you turn off stepped layout? In what Format pane group is this feature?
beneath the Visualizations pane, select the Format pane. In the Search box, enter Stepped. Set the Stepped Layout property to Off. This feature is in the Row headers group of the Format pane.
Give an example of a 1:* relationship
between the CountryName and Territory tables, where you can have many territories that are associated with one unique country.
Give example of common custom visuals.
box-and-whisker plots, variance charts, hierarchical trees, Gantt plots, clustering plots, and much more. Using advanced analytics visuals adds a layer of complexity to your reports and allows you to further analyze the data and develop granularity within your visuals.
What comes first in using data to drive business decisions?
business leaders partnering with data professional within your organization, such as data engineers and data scientists
How is each visual represented in PBI Desktop
by an icon in the Visualizations pane
When you import a table from any data source, how does Power BI Desktop try to detect the type of data in the columns?
by automatically scanning the first 1,000 rows (default setting) Some situations might occur where Power BI Desktop does not detect the correct data type. Where incorrect data types occur, you will experience performance issues.
In Power BI terms, how can you represent a summary statistics?
by creating histogram using a bar or column chart visuals or by creating a bell curve with an area chart visual, You can create the charts manually or using the Q&A visual to ask a direct question.
How can variables help simplify the task of troubleshooting your DAX calculation?
by evaluating each variable separately and by recalling them after the RETURN expression.
How do you access the query reduction options?
by selecting File > Options and settings > Options, scrolling down the page, and then selecting the Query reduction option.
How do you open the Power Query Editor?
by selecting the Transform data option on the Home tab of Power BI Desktop.
In the PQE, what are two ways to promote headers?
by selecting the Use First Row as Headers option on the Home tab or by selectin the drop-down button next to Column1 and then selecting User First Row as Headers
How can you mitigate the tradeoff between using a detailed fact table and a summarized fact table?
by using a mixed model design.
How can you provide an effective report that guides the reader through the content quickly and efficiently?
by using appropriate visualizations and interactions
In DirectQuery mode, how do you query the specific tables that you will need?
by using native Power BI queries
How does Power BI solve the problem when you cannot import the data?
by using the DirectQuery storage mode, which allows you to query the data in the data source and not import a copy into Power BI.
How can you customize filters in the Filter pane?
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.
What is the name for the blank backdrop in the Power BI Desktop?
canvas
If you are reviewing a large dataset with more than 1,000 rows, and you want to analyze that whole dataset, what do you do?
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.
What types of visuals are available in PBI Desktop?
charts, maps, cards, a table, a matrix, and many more.
What is the role of the Business Analyst?
close to business specializes in interpreting the data that comes from the visualization
What do you do with Performance Analyzer log results?
compare the duration of each element in the report and identify the elements that have a long duration.
What feature can be used to notify you or a user that a specific data point is above, below, or at a specific threshold that you can set?
data alerts
What can make managing assets and latency difficult?
data duplication, which can overuse resources
What is a challenge that most businesses face in today's data-driven world?
data is not being used to its full potential
From where is a paginated report's data retrieved and stored?
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 it.
What does the Navigator window show?
data this is available in your data source (e.g. an Excel file)
What roles can transition to a data engineer role?
database administrators business intelligence professionals
What are some the aspects of a data scientist's work?
descriptive analytics predictive analytics deep learning
What is the process of data modeling?
determine how your tables are related to each other define and create relationships between tables enhance the model by adding metrics and custom calculations to enrich the data
Which data analytics category helps answer questions about why events happened?
diagnostic analytics
Power BI allows relationships to be built from tables with what?
different data sources
Thsi table type typcially contains unique values, for instance, one row for each product in the Products table and one row for each customer in the Customer table.
dimension
What are the two table types when you build a start schema?
dimension and fact tables
In a star schema, each table within your dataset is defined as a one of these two table types
dimension or fact
These tables are connected to the fact table through key columns.
dimension table
Unlike other database management systems, Power BI has the concept of _______________ to a relationship
directionality, or cardinality. This directionality, or cardinality, plays an important role in filtering data between multiple tables.
What is the difference between distinct values and unique values?
distinct tells you the total count of how many values are present, while unique tells you how many of those values are not duplicates or nulls. For example, you could have a count of 1000 rows, in which there are 10 distinct values, and 0 of those values are unique.
What is the best practice with regard to unneeded columns? Give an example.
do no import them Ask yourself if you really need each column and try to identify the benefit that each one adds to your data model. If you find that a column adds no value, you should remove it from your data model. 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.
What should you do if you can't find a visual that meets your needs?
download other visuals from Microsoft AppSource or import your own custom visuals.
What can organizations do with advanced analytics?
drill into the data to predict future patterns and trends identify activities and behaviors, enable businesses to ask the appopriate questions about their data
Rather than using multiple visuals, you can provide information in what other ways?
drillthrough pages and report page tooltips.
What do reports do for an organization?
drive the overall actions, decisions, and behaviors
When are a PQE query's steps applied?
each time the query connects to the data source
What is a scatter chart?
effective when you are comparing large numbers of data points without regard to time. 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.
Describe the role of the data analyst.
enables businesses to maximize the value of their data assets through visualization and reporting tools such as MS Power BI. responsible for: - profiling - cleaning - transforming - design and build scalable and effective data models - enable and implement advanced analytics capabilities into reports for analysis - works with pertinent stakeholders to identify appropriate and necessary data and reporting requirements - turn data into relevant and meaningful insights also: - manage Power BI assets, including reports, dashboards, workspace, and the underlying datasets - implement and configure proper security procedures also: - work with data engineers to determine and locate appropriate data sources - ensure proper access to the needed data sources - identify new processes or improve existing processes for collecting data
What can you do to key business data to help ensure trust in that data?
endorse a dataset as certified
What should you do before you run Performance analyzer?
ensure that you clear the visual cache and data engine cache; otherwise, the results will not be accurate. Also, you should set up the report so that it opens on a blank page.
What should you do in reviewing the relationships in your data model?
ensure that you have established the correct relationships. Check that relationship cardinality properties are correctly configured. For example, a one-side column that contains unique values might be incorrectly configured as a many-side column.
Before adding a data classification to your dashboard, what must be done?
ensure that your organization's custom data classification settings are added into the Power BI system. Data classification is done by an administrator.
Once you've created a date table using DAX, what will you still need to do
establish relationships between your date table and the other (transaction) tables, and then mark your table as the official date table of your data model.
What does TOTALYTD do in this measure: Sales YTD = TOTALYTD(SUM(Sales[Sales]), 'Date'[Date], "6-30")
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. It performs filter manipulation, specifically time filter manipulation. For example, to compute YTD sales for September 2017 (the third month of the fiscal year), all filters on the Date table are removed and replaced with a new filter of dates commencing at the beginning of the year (July 1, 2017) and extending through to the last date of the in-context date period (September 30, 2017).
What types of critical business insights does data analysis provide?
evaluating customer sentiment performing market research identifying trends across the business
What should you carefully consider when designing a report?
every visual and element that you plan on using in the report. Everything should have a purpose, and you should consider how each element will appear to your report users.
Which models can enforce row-level security (RLS)?
external-hosted models Power BI desktop models
What metadata about the data model as a whole should you consider? Where do you find this?
file size data refresh rates You can find this metadata in the associated Power BI Desktop (.pbix) file.
What does the TOTALYTD function perform?
filter manipulation, specifically time filter manipulation. For example, to compute YTD sales for September 2017 (the third month of the fiscal year), all filters on the Date table are removed and replaced with a new filter of dates commencing at the beginning of the year (July 1, 2017) and extending through to the last date of the in-context date period (September 30, 2017).
What should you do if you identify visuals as the bottleneck leading to poor performance?
find a way to improve performance with minimal impact to user experience. Consider the number of visuals on the report page; less visuals means better performance. Ask yourself if a visual is really necessary and if it adds value to the end user. If the answer is no, you should remove that visual. Rather than using multiple visuals on the page, consider other ways to provide additional details, such as drill-through pages and report page tooltips. Examine the number of fields in each visual. The more visuals you have on the report, the higher chance for performance issues. In addition, the more visuals, the more the report can appear crowded and lose clarity. The upper limit for visuals is 100 fields (measures or columns), so a visual with more than 100 fields will be slow to load. Ask yourself if you really need all of this data in a visual. You might find that you can reduce the number of fields that you currently use.
How do you merge two columns?
first select the [first column] column header. While pressing the Ctrl key, select the [second column] column. Right-click either of the select column headers, and then in the context menu, select Merge Columns. Many common transformations can be applied by right-clicking the column header, and then choosing them from the context menu. However, all transformations and more are available in the ribbon. In the Merge Columns window, in the Separator dropdown list, select Space. In the New Column Name box, replace the text with [new name].
What is the name for the process of viewing multiple child levels based on a top-level parent in a hierarchy?
flattening the hierarchy
How do you import a custom visual?
from AppSource. In the Visualizations pane, select the Get more visuals icon and then select Get more visuals. On the window that displays, locate and select the visual that you want to import and then select Add. The new visual will appear under the other visuals in the Visualizations pane. To add the visual to your report, select its icon. You can then add fields to the visual and customize its formatting, just like you would for any other visual.
From what object can people ask questions by using the Ask a question about your data?
from the dashboard view, in the search bar
What is an example of descriptive analytics?
generating reports to provide a view of an organization's sales and financial data
How do you enable/disable this Auto date/time option?
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.
How do you create a relationship between a date table and transaction tables?
go to Model tab > Manage Relationships, where you can create relationships between the common date table and the transaction tables by using the foreign key date column.
When you have set up the target page, how do you set up Cross-report drillthrough?
go to the Drillthrough section of the Visualizations pane and set the Cross-report option to the On position. Then, drag the fields that you want to use as drillthrough targets into the Drill through filters well. For each field, select whether you want to allow drillthrough when the field is used as a category or when it's summarized like a measure. Select whether you want the Keep all filters option On or Off for the visual. If you don't want to pass filters that are applied to the source visual to your target visual, select Off. Similar to when you create a drillthrough for a single report, Power BI Desktop automatically adds a Back button to the target drillthrough page. However, in this case, you should delete the Back button because it only works for navigation within a report.
How do you create a measure?
go to the Fields list and select New measure. Text will now appear in the formula bar underneath the ribbon. You can replace the "Measure =" text with the following text: Total Sales = sum('Sales OrderDetails'[Total Price]) The new measure will now appear in the Fields list.
How do you manually create a hierarchy?
go to the Fields pane on Power BI and then right-click the column that you want the hierarchy for. Select New hierarchy. Next, drag and drop the next-level column into the new hierarchy that you've created.
What are the steps to access the storage modes?
go to the Model view > select a data table > in the Properties pane, select the 'Storage mode' drop-down list
What do statistics show you?
he distribution of your data and help you to identify key takeaways and trends and determine whether outliers exist.
What do data professionals within your organization do?
help get the data that you need to tell the story that allows business decision makers to act
What are hierarchies? What do they allow you to do?
help you find detail in dimension tables. Hierarchies allow you to view increasing levels of data on a single view. They form through natural segments in your data. For instance, you can have a hierarchy of dates in which your dates can be segmented into years, months, weeks, and days. Hierarchies are useful because they allow you to drill down into the specifics of your data instead of only seeing the data at a high level.
What does sorting allow you to do?
highlight the important information without removing any of the data.
What does Performance Analyzer allow you to discover?
how each of your report elements, such as visuals and DAX formulas, are performing.
On what does the performance of a report depend?
how quickly data can load onto the report page.
What do the DAX 'X' functions or iterators allow you to do?
however, 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.
When working with file connections in the PQE, what is the first step in shaping your initial dat?
identify the column headers and names with the data then evaluate where they are located to ensure they are in the right place
What must you first do to optimize performance?
identify where the problem is coming from; in other words, find out which elements of your report and data model are causing the performance issues.
Describe the role of the Database Administrator.
implements and manages the operational aspects of cloud-native and hybrid data platform solutions built on Azure services and SQL Server. responsible for the overall availability and consistent performance and optimizations of the database solutions. work with stakeholders to identify and implement the policies, tools, and processes for data backup and recovery plans monitors and manages the overall health of a database and the hardware it resides on manages security of the data - grants or restricts user access and privileges to the data
How can you make dashboards viewable on mobile devices?
in Power BI Desktop, select View on the ribbon and then select Mobile Layout. This view emulates the view of a user who is looking at visuals on their phone, so you can add visuals to this view, resize them, and change the formatting on them Alternatively, you can also optimize your dashboards for mobile view in Power BI service. To see a dashboard in mobile view, select the ellipsis (...) on the home ribbon and select Mobile view You can also resize and reorient the tiles and visuals in whichever order you want. 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.
Where is data that comes from a streaming dataset stored?
in a temporary cache
Why might you rename a column containing email addresses "UPN"?
in case the column would be used for row-level security
Where do the Column quality and Column distribution appear?
in graphs above the columns of data in the PQE
How do you create a hierarchy?
in the Fields pane, right-click the field that you want to set as the top level of the hierarchy and select New hierarchy. The new hierarchy displays in the list in the Fields pane. You can now drag and drop other fields into the new hierarchy or right-click each field and select Add to hierarchy.
How do you turn on markers to convey different series.
in the Format pane, expand the Shapes section, and then scroll down and move the Show marker slider to the On position.
In the PQE, how do you remove excess rows?
in the Home tab > select Remove Rows > Remove Top Rows
How should you test a report?
in the Power BI Report Server to see how it works from a user's perspective. If you experience issues, or if the report users have reported issues, you need to investigate the cause of those issues and take measures to tune the report for more optimized performance.
Paginated reports can only be rendered where and when?
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.
Where is the Quick insights feature available?
in the Power BI web service only. Also, this feature doesn't work with DirectQuery; it only works with data that is imported to Power BI.
When preparing data in Power BI, where and when should you evaluate column data types?
in the Power Query Editor, before you load the data into a Power BI data model.
Where do you verify a query name change
in the Queries pane on the left side of the Power Query Editor
In the PQE, how do you rename a query?
in the Queries pane to the left of your data, select the query that you want to rename. Right-click the query and select Rename. Edit the current name or type a new name, and then press Enter.
In the Query Editor, what order are query steps carried out?
in the order in which they appear in the Applied Steps pane
Describe that data the a retail business collects and stores.
in-store browsing behavior sales purchases pages visited on their site the aisles in which purchases were made spending habits ...and much more Retail analytics focuses on providing insights related to sales, inventory, customers, and other important aspects crucial for merchants' decision-making process. The discipline encompasses several granular fields to create a broad picture of a retail business' health, and sales alongside overall areas for improvement and reinforcement. Essentially, retail analytics is used to help make better choices, run businesses more efficiently, and deliver improved customer service. The field of retail analysis goes beyond superficial data analysis, using techniques like data mining and data discovery to sanitize datasets to produce actionable BI insights that can be applied in the short-term. Moreover, companies use these analytics to create better snapshots of their target demographics. By harnessing sales data analysis, retailers can identify their ideal customers according to diverse categories such as: - age - preferences - buying patterns - location - and more
What are the two states of relationships between tables?
inactive or active.
Give an example of filtering data.
include only those customers who received emails from the marketing campaign
What type of error is this, "A column specified in the call to function 'TOTALYTD' is not of type DATE."
incorrect data type
What are performance tuning techniques for Microsoft SQL Server?
index creation hardware upgrades execution plan tuning data compression
What is included in Power BI metadata?
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.
Before you create the R visual, what must you do?
install R on your local computer so that Power BI Desktop can run R scripts. You can download and install R for free from many locations, including the Microsoft R Application Network and the CRAN Repository . When you have downloaded and installed R, Power BI enables it automatically, but you should verify that it has been enabled in the correct location. In Power BI Desktop, select File > Options and settings > Options and then select R scripting in the Global options list. Verify that your local R installation is specified in the Detected R home directories drop-down menu and that it properly reflects the local R installation that you want Power BI Desktop to use. In the following image, the path to the local installation of R is C:\Program Files\R Open\R-3.5.3.
When multiple visuals exist on the same report page, what can you configure them to do?
interact with each other.
What can users or groups do with dashboards?
interact with them in the Power BI service or on their mobile devices
neural networks
interconnected neural cells. With experience, networks can learn, as feedback strengthens or inhibits connections that produce certain results. Computer simulations of neural networks show analogous learning. Neural nets are a means of doing machine learning, in which a computer learns to perform some task by analyzing training examples. Usually, the examples have been hand-labeled in advance. An object recognition system, for instance, might be fed thousands of labeled images of cars, houses, coffee cups, and so on, and it would find visual patterns in the images that consistently correlate with particular labels. see https://news.mit.edu/2017/explained-neural-networks-deep-learning-0414
What is the default display view? How do you change it?
is Fit to page, which means that the contents are scaled to best fit the page. If you need to change this view, go to the View tab, select Page view, and then select your preferred page view option, as illustrated in the following screenshot.
When looking at an existing data model, how can you tell which table is a dimension table?
it contains columns which can be used to filter or slice data.
In SQL, what does the WHERE clause do?
it filters the rows to pick only filtered records that you want
What happens each time you perform a step to shape your data?
it is recorded
In a data model diagram, how can you interpret the cardinality?
it is represented by the 1 and * indicators.
What is one benefit of importing data into Power BI?
it makes it easier for you interact directly with your data
What does the Quick insights feature do?+
it uses machine learning algorithms to goes 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. It also helps you find insights you might have missed when building your reports. From the insights that Power BI discovers, you can generate appealing, interactive visualizations.
How was data analysis performed previously and by whom?
it was a difficult and intricate process that was typically performed by data engineers or data scientists
In shaping your data, why is it best to remove unnecessary columns in the early stage?
it will help you focus on the data that you need and it will help improve the overall performance of your datasets and reports
What is the impact of reducing the data that is loaded into your model?
it will improve the relationship cardinality of the report.
How is using an 'X' function better than a calculated column?
it will perform better and use less disk space than a calculated column
Generally, a smaller data model is better because ________________________
it will perform faster and will be simpler to use.
Why defining what a smaller data model entails is problematic.
it's a heuristic and subjective concept.
In the PQE, what happens when you select a query from the Queries pane?
its data is displayed in the center pane, where you can shape and transform the data to meet your needs.
What happens when you select a query in the Queries pane in the PQE?
its data is displayed in the center pane, where you can shape and transform the data to meet your needs.
When you import data from multiple sources into Power BI Desktop, what column names does the data retain?
its predefined table and column names
Which visual helps you understand the factors that are affecting a specific metric?
key influencers 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.
What does lower cardinality lead to? What can you do to optimize performance?
leads to more optimized performance reduce the number of high cardinally columns in your dataset.
What should you do before you begin examining the data in Power Query Editor?
learn about the underlying data structures that data is organized in.
In what locations might you find an Excel file?
local OneDrive for Business OneDrive - Personal SharePoint - Team Sites
What does Performance analyzer provide you with? What can you do with this information?
logs that measure (in time duration) how each of your report elements performs when users interact with them. By examining the durations in the logs, you can identify which elements of the report are the most (or least) resource intensive. You can find where bottlenecks exist, which is a good starting point for making changes.
When you examine the results in the Performance analyzer pane, you can see how long it took the Power BI Desktop engine to evaluate each query (in milliseconds). A good starting point is any DAX query that is taking how long?
longer than 120 milliseconds.
What tool is used in predictive analytics to apply modeling techniques that can detect anomalies or patterns?
machine learning
Once you have loaded data into Power BI using Power Query, what can you then do?
make further transformations to your data, such as renaming or deleting columns, appending, parsing, filtering, or grouping data.
What does effective data modeling do?
makes reports more accurate allows the data to be explored faster and more efficiently decrease the time for the report writing process simplifies future report maintenance
In Import mode, how are data refreshed
manually (set up or refreshed in a one-off)
How many data sources does Power Query offer?
many
How many performance tuning techniques are there for each Power Query data source?
many
What does does the PQE ribbon contain?
many buttons you can use to select, view, and shape your data.
What do reports let you create?
many visualizations multiple pages arrange visualizations in whatever way best tells your story.
What can you use to convey different series in a chart?
markers
What is an app like?
microwaving a dinner or ordering fast-food meal: You just have to press a few buttons or a make a few comments, and you're quickly served a collection of entrees designed to go together, all presented in a tidy, ready-to-consume package
What does connecting Power BI to your organization's relational database allow you to do?
monitor the progress of your business and identify trends forecast sales figures, plan budgets, set performance indicators and targets
Describe how you can change a visual design
move or arrange them make them bigger or smaller change their height or width position them next to other tiles
Do PBI paginated reports use Power Query when connecting to data sources?
no a Power BI paginated report does not involve data cleaning steps.
In Power Query, does the underlying data change?+ It's important to know that the underlying data is not changed; rather, .
no Power Query Editor adjusts and shapes its view of the data, and any interaction it has with the underlying data is based on that shaped and modified view
With fact tables, it is common to see columns that are filled with what two data types?
numbers and dates.
How do you locate a column in a query that has many columns?
on the Home ribbon tab, from inside the Manage Columns group, click the Choose Columns down-arrow, and then select Go to Column. then, in the Go to Column window, to order the list by column name, click the AZ sort button, and then select Name. select the desired column, and click OK
In PQE, where can you see a list of the available queries?
on the left side (pane)
How do you apply AI Insights to your data?
open Power Query Editor 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. On the Add column tab, the most relevant AI Insights option for this example is Text Analytics. This option includes Azure Cognitive Services models, such as Sentiment Analysis, Key Phrase Extraction, and Language Detection, that derive meaning or specific pieces of language from text data. You can use either the Sentiment Analysis or Key Phrase Extraction option to determine the customer sentiments in the Help tickets and visually show the results in Power BI.
How do you view or add comments?
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. OR select the visual in the report to open it in focus mode. Select Comments from the top menu and enter your comment in the Comments pane that displays.
How do you use the Quick insights feature?
open your Power BI web service and then select the Content tab. Locate your report for which you want to get quick insights, which in this case is TailwindTraders. Then, select More options (...) > Quick insights. Power BI will use various algorithms to search for trends in your dataset. This process might take a few seconds, but when it is finished, you'll see a message in the upper-right corner letting you know that the results are ready to be viewed. Select View insights to open the Quick Insights page for the selected dataset, and then view the insights that Power BI has found for you. The Quick Insights page contains up to 32 separate insight cards, and each card has a chart or graph plus a short description. Add a Quick insights result card to a report If you see an insight card that is particularly compelling, you can 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 does Properties pane in the Model view provide?
options within the column properties that you can view or update
What are data anomalies?
outliers within your data
Describe the role of the Data Scientist.
perform advanced analytics to extract value from data work can vary from descriptive analytics to predictive analytics
What is deep learning?
performing iterative experiments to solve complex data problems by using customized algorithms
What objects can you pin to a dashboard?
pin an entire report page, or you can pin individual tiles
What is best practice regarding placement of visuals on the report page?
place the most important visual in the upper-left corner of your report because your users most likely read left-to-right and top-to-bottom. You might also want to place your organization's logo in or near this area. You can then arrange the other visuals accordingly.
In what two areas do data analysts divide most of their time?
preparing modeling
When using pie charts, donut charts, and Treemaps, try to avoid what?
presenting too many categories because it results in thin slices (or rectangles) that provide no added value to the user. If you do need to present all categories in your dataset, it's better to use another type of visual, such as a column chart.
Relationships between tables are formed when what is common common between different tables.
primary and foreign keys
What is this an example of: in a Customers table, you have an index that identifies each unique customer?
primary key
How do data analysts prepare data?
profiling cleaning transforming take raw data and turn it into information that is trusted and understandable follow a series of steps and methods to prepare data for placement into a proper context and state that eliminates poor quality and allow it to be turned into valuable insights. ensure integrity of data: - correct wrong or inaccurate data - identify missing data - convert data from one structure to another, or from one type to another - make data more readable figure out how to get and connect to the data makes decisions to ensure that models and reports meet, and perform to, acknowledged requirements and expectations provide privacy and security assurances: - anonymizing data - prevent people from seeing personally identifiable information - removing data completely as needed
What can you use Tooltips for?
providing more contextual information and detail to data points on a visual. When you add a visual, the default tooltip displays the data point's value and category, but you can customize this information to suit your needs. For example, you might want to provide your report users with additional context and information, or specify additional data points that you want users to see when they hover over the visual.
How do you publish a mobile-optimized version of your report?
publish the main report as you did previously. The web and mobile versions are published at the same time.
What are two options when optimizing performance when retrieving, importing, and preparing data?
query folding query diagnostics
What PBI tool can you use to help you write a measure? What are the advantages of using a quick measure?
quick measure Many available categories of calculations and ways to modify each calculation exist to fit your needs. Another advantage is that you can see the DAX that's implemented by the quick measure while jumpstarting or expanding your own DAX knowledge.
What optimization techniques can be used in either DirectQuery or Import mode tables?
reduce the number of visuals on the report page or reduce the number of fields that are used in a visual. You can also remove unnecessary columns and rows.
What can you do in the Query Settings pane?
rename steps, delete steps, or reorder the steps as you see fit
What kinds of things can you accomplish with the Power Query editor?
renaming columns or tableschanging text to numbers removing rows setting the first row as headers and much more
Columns within the fact table type can contain what kind of valuess
repeated For example, one product can appear multiple times in multiple rows, for different customers on different dates.
Name some of the components of Power BI
reports dashboards, workspaces datasets more...
Where are comments available?
reports, paginated reports, dashboards, and visuals.
Which capacity is required for AI Insight's Text Analytics and Vision options?
required to use the Text Analytics and Vision options.
What can you do once streaming data stored in IoT Hub has been aggregated and cleaned in Azure Stream Analytics?
retrieve the data into Power BI as a streaming dataset you can then consume the information and build visuals
What should your starting point be to identify bottlenecks that exist in the code?
review the measures and queries in your data model to ensure that you are using the most efficient way to get the results that you want. Your starting point should be to identify bottlenecks that exist in the code. When you identify the slowest query in the data model, you can focus on the biggest bottleneck first and establish a priority list to work through the other issues.
What will you need to do if your report is performing slowly, or your refreshes are taking a long time?
revisit the data preparation and modeling tasks to optimize your report
What is the best visual to use for identifying outliers?
scatter chart which shows the relationship between two numerical values. Scatter charts display patterns in large sets of data and are, therefore, ideal for displaying outliers.
How do you connect to data in an application?
select 'Get data' > select 'Online Services' > select (for example) 'SharePoint Online List' > select 'Connect' > provide (for example) your SharePoint URL select 'OK' > sign in to SharePoint with your Microsoft account > select 'Connect' > in the 'Navigator' window, select the list that you want to load > select 'Load' or 'Transform Data' > select 'Close & Apply'
How do you enable to controls to edit interactions between visuals?
select a visualization, go to the Format tab in the ribbon, and then select Edit interactions. 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. In the following image, notice that the donut chart is cross-filtering the card visuals and cross-highlighting the column chart. You can now change how the selected visualization interacts with the other visualizations on the report page You can now select each visualization on your report page, one at a time, to see how it interacts with the other visualizations. If you don't like the behavior that you see, you can change the interactions.
You want to get JSON data from a NoSQL database. You have set up the connection. What do you do when you arrive at the 'Navigator' window?
select the 'table' you want under the list of database > records will represented as the value 'Record' in the 'Document' table because all records in the document are of type Record > select the 'Edit' button to open the records in Power Query > select the 'Expander' button to right side of the 'Column1' header. > select the fields that you want to load > clear the 'Use original column name as prefix' checkbox' > click 'OK' > review the selected data > select 'Close & Apply' to load the data the data now resembles a table with rows and columns
In PBI Report Builder, if you are viewing a report, what do you do to return to the design surface?
select the Design button
From the Power BI service, how do you connect to data?
select the Get Data button in the lower-left corner of the home page
From the PQE, how do you connect to data and begin the query building process?
select the Get Data button. A menu appears, providing the most common data sources.
How do you publish a report?
select the Publish button on the Home tab. You might be prompted to save your changes, in which case, select Save and then proceed to save your Power BI (.pbix) file. You might also be required to sign in to Power BI. If so, enter your sign-in credentials to continue. In the Publish to Power BI window, select the destination in which you want to publish the report. For example, you can publish to a workspace within Power BI. For production reports, we recommend that you publish to an app workspace. When the report is successfully published, you'll get a success message that contains a link to your report in your Power BI site. Select Got it to close the Publishing to BI window and return to your report in Power BI Desktop. From the report page, you can select the provided URL to go to Power BI service and see your new report.
How do you set the tab order?
select the View tab in the ribbon and then select Selection Pane. On the Selection pane that displays, use the arrow buttons to move the objects to the correct order, or select an object with your mouse and drag it into the position that you want in the list.
How do you open the Advanced Editor?
select the View tab on the ribbon, and then select Advanced Editor. A window appears, showing the existing query code.
In PQ, how do you remove columns
select the column headers while holding the SHIFT or CTRL keys > right click any selected column header > click Remove
In the PQE, what are two ways to remove columns?
select the columns that you want to remove > on the Home tab > select Remove Columns or select the columns that you want to keep > go to the Home tab > select Remove Columns > Remove Other Columns
When unpivoting data, which data do you select to unpivot?
select the columns whose column names you want to convert to values in a single new column
How do you add a data alert?
select the ellipsis (...) in the corner of the tile and then select Manage Alerts. In the resulting window, select + Add Alert Rule, which will add a new alert. Ensure that the Active toggle switch is turned On, name the alert, and then set the condition. At this point, you can choose the threshold that you want to create the alert for, which includes options for Above or Below a specific threshold. Then, select at which frequency that you want the alerts to be sent. These alerts will be sent directly to your Notification Center in Power BI, but you can also configure emails to be sent to you if the threshold is crossed. After selecting Save and Close, you will have successfully created a data alert in Power BI service. you can enable or disable the alert by using the toggle switch.
How do you pin a live page, where all report page visuals are pinned to a dashboard at once?
select the ellipsis (...) on the navigation bar of the report and then select Pin a live Page you can choose whether you want to pin this report to a new dashboard or an existing one. 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.
How do you hide an object from the tab order?
select the number next to that object. For example, it's best to hide decorative shapes and images that you have in your report.
What are the steps to change your data source settings from within Power Query?
select the table > select the 'Data source settings' option on the Home ribbon --- or --- go to 'Query Settings' panel and select the settings icon next to 'Source' update the server and database details > click OK click 'Close and Apply'
How do you set up conditional formatting?
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
An effective technique to reduce the model size is to set what for larger fact-type tables?
set the Storage Mode to DirectQuery
What can you do in the visual General section?
set the precise size and place for your visual on your canvas. This option might be suitable if the drag-and-drop feature is not placing the visual exactly where you want it to be. It can also be useful to ensure that you have aligned specific visuals consistently.
What does the Power Query Editor allow you to do?
shape (transform) imported data
What two forms of collaboration are important for the right people to get the answers that they need.
sharing discovery
What does proper data management help to reduce?
silos within an organization
Name some of the things you do when cleaning a preparing data for analysis.
simplify a complicated model change data types rename objects pivot data profile columns fix inconsistencies fix unexpected or null values fix data quality issues replace values modify table structures combine queries
When you have identified the outliers in your data, what can you then use to highlight those outliers?
slicers or filters 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.
Power BI Desktop provides what three tools that you can use to edit and configure interactions between the visualizations that you add to your report?
slicers, filters, and sorting.
Why is it important to negotiate the level of data granularity with report users?
so they understand the implications of these choices.
What are sources for streaming data?
social media factory sensors service-usage metrics other sources that contain a constant stream of data points
What is Power BI a collection of?
software services apps connectors
What is the 'Dual (Composite)' storage mode?
some data can be imported and other data must be queried. The 'Dual' mode allows Power BI to choose the most efficient form of data retrieval
What do you commonly find once you have imported data into Power BI Desktop?
some data you don't need some data are in the wrong format
What does conditional formatting in PBI-D allow you to do?
specify customized cell colors, including color gradients, that are based on field values. Additionally, you can use it to represent cell values with data bars, KPI icons, or active web links. For example, you could set up conditional formatting for your sales figures. If the sales amount falls below zero, you could display this value in red, a color that is associated with danger, so that users will see it clearly and know that they need to take immediate action. Conversely, you could set a value for your sales target, displaying amounts over that target amount in a green color to signify that the target is met and all is going well.
The schema optimized for high performance and usability.
star
How do you design a mobile report layout?
start by opening the mobile layout view for that page. 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. To add a visual to the mobile layout canvas, drag the visual from the Visualizations pane to the phone canvas, or double-click the visual in the Visualization pane. You can then resize and reposition the visual in the same way that you would a report page. Repeat these steps to add other visuals to the mobile layout canvas.
What should you do to ensure you get the most accurate results in your analysis.
start with a clear visual cache and a clear data engine cache
What is a vital component and aspect of large and small businesses?
storytelling through data analysis
What feature and service can you use to aggregate streaming data coming from the source to IoT Hub?
stream insight job in Azure Stream Analytics - this will aggregate the data, meaning that it will clean the data and quiet the noisy messages.
When shaping data, why is it best to unpivot any pivoted data?
streamlines the process of creating DAX measures on data later creates a simpler way of the slicing the data
When a table is created in PBI Desktop, the PQE assumes that all data belongs in what?
table rows
In a data model view, the boxes contain _______ of data, where each line item within the box is a ________. The lines that connect the boxes represent ______________ between the tables.
tables column relationships
How do you sort a visual?
tart by selecting the More options... button in the upper-right corner of the visual, where you will have three sorting options: Sort descending - Sorts the visual by the selected column in the order of greatest value to smallest value. Sort ascending - Sorts the visual by the selected column in the order of smallest value to greatest value. Sort by - Sorts the data by a specific column. Hover over this option to display the list of columns that you can select from.
What should a well-designed report do?
tell a compelling story about the data, which will enable business decision makers to quickly gain needed insights.
What does the ISINSCOPE function do in this DAX measure: Sales % Country = IF( ISINSCOPE(Region[Region]), DIVIDE( SUM(Sales[Sales]), CALCULATE( SUM(Sales[Sales]), REMOVEFILTERS(Region[Region] ) ) )
test whether the region column is the level in a hierarchy of levels. When true, the DIVIDE() function is evaluated. The absence of a false part means that blank is returned when the region column is not in scope.
What does HASONEVALUE do? If this is used in a measure summing the target value and it is added to a matrix with Salesperson on rows, what will the value be for the Total row?
tests whether a single value in the specified column (e.g. Salesperson) is filtered. When true, the expression is returned (e.g. the sum of target amounts for just that salesperson. When false, BLANK is returned. In the example, the Target total column is BLANK.
When importing data in the PQE, it is common for date data to be interpreted as what data type?
text
How do text values, numbers, and dates sort by default
text values sort alphabetically, numbers sort from smallest to largest, and dates sort from earliest to latest.
What should you do if your data source already defines a date dimension table? What does this do?
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.
What can you use the grouping and binning features to ensure?
that the visuals in your reports display your data according to your preference. Using these features will help you to clearly view, analyze, and explore the data and trends in your visuals. Additionally, you'll be able to identify clusters, patterns of behavior, data averages, and more. The results of this analysis will provide your users with more specific insights on their data, which can help drive business decisions.
When writing a calculated column, what does the value on the right side of the equal sign? Give an example
the DAX expression. For example, a simple DAX expression takes the quantity value and multiplies it with the unit price value for each individual row.
What happens after you have connected a file to Power BI Desktop?
the Navigator window opens
In the PQE, which ribbon tab provides many of the same options as the righ-click context menus?
the Transform tab
When writing a calculated column, what does the value on the left side of the equal sign?
the column name.
In PQ, what does it mean when the values in a column say "Value"?
the column represents a related table
What does the Home tab in the PQE contain?
the common query tasks, including the first step in any query, which is Get Data.
Within the Column profile feature, what does the Value distribution graph tell you?
the counts for each unique value in that specific column. From this, you can easily determine which value appears the greatest number of times or the least number of times. This information helps you easily identify outliers. If a value appears far more than other values in a column, this feature allows you to pinpoint a place to begin your investigation into why this is so.
When pinning visuals to a dashboard, what filters do they use?
the current filter context.
What rights are required to make changes to data classifications?
the dashboard owner can manually make changes to the classification. To manually make changes, admin rights are required in Power BI service.
In PQE, what is displayed in the middle of the screen?
the data in your selected query
In the PQE, what is displayed in the middle of the screen?
the data in your selected query
What does importing data into Power BI mean?
the data is stored in the Power BI file and gets published along with Power BI reports.
relationships that make up what?
the data schema
What is the first stop in optimizing a DirectQuery data connection?
the data source
What is data granularity?
the detail that is represented within your data, meaning that the more granularity your data has, the greater the level of detail within your data.
What do the built-in AI capabilities in Power BI enable?
the discovery of data by: - using the built-in AI visuals - asking questions - using the Quick Insight feature - creating machine learning models directly within Power BI
What does the Column distribution feature show you?
the distribution of the data within the column and the counts of distinct and unique values, both of which can tell you details about the data counts.
What is a statistical summary?
the information that provides a quick and simple description of your data. gives the user a high-level view of the available data, where they can see clusters, patterns on behavioral data, data averages, and more. They can gain insights about their data that will help drive business decisions.
What does selecting Diagnose Step show you?
the length of time that it takes to run that step This selection can tell you if a step takes longer to complete than others, which then serves as a starting point for further investigation.
In the learning path "Get started with Microsoft data analytics," what do you learn about?
the life and journey of a data analyst: skills tasks processes ...they go through in order to tell a story with data so trusted business decision can be made - how the suite of Power BI tools and services are used by a data analyst to tell a compelling story through reports and dashboards, and the need for true BI in the enterprise
What does the FROM statement speficy?
the name of table(s) that you want to pull the data from
In the PQE, what does the Queries pane display?
the number of active queries in addition to the name of the query
When you use the DirectQuery method, the overall user experience depends heavily on what?
the performance of the underlying data source. and the number of users connecting and Network latency; faster networks return data quicker. and The performance of the data source's server and how many other workloads are on that server. For example, consider the implications of a server refresh taking place while hundreds of people are using the same server for different reasons.
What is required to append queries in order to see one consolidated view?
the pertinent column that you require in your combined table must be named the same in your original tables
What is query folding?
the 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.
What can you see as PBI applies the changes in you made in the Query Editor?
the status of the operation
What does a Mixed model allow you to determine?
the storage mode for each table.
Data-driven business make decisions based on what?
the story that their data tells
Give an example of using a summary table to reduce a model size.
the 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. OR if you group by date, customer, and product, and individual transaction detail is not needed.
On what do the visual formatting options that are available depend?
the type of visualization that you selected
When you create visuals, Power BI Desktop aggregates your data into groups, based on what?
the values that it finds in the underlying data.
In SQL, what character should you try to avoid? Why?
the wildcard character (*) because you import all columns that you don't need from the specified table it can lead to redundant data in your data model it may cause performance issues and require additional steps to normalize your data for reporting
In PQE, what happens to all the steps that you take to shape your data?
they are recorded
From where can Power BI paginated reports get a data model?
they can use a dataset from Power BI service. These datasets have used Power Query to clean and alter the data. The difference is that this work was done in Power BI Desktop or SQL Server Data Tools prior to using Power BI Report Builder, which doesn't have that tool in the user interface.
Can users view any Power BI report on a phone?
they can view any Power BI report page in landscape orientation
What is the impact of a calculated table on the data model? When are calculated tables recomputed?
they 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.
What must you do to JSON records before you can report on them?
they must be extracted and normalized
What problems do incorrect data types cause?
they prevent: - certain calculations - deriving hierarchies - creating proper relationships with other tables
What will happen if you change a data connection file path to a file that does not have the same file structure?
this will break the reporting model
How dimension and fact tables are connected
through key columns
How relationships are defined between tables.
through primary and foreign keys.
Which type analysis provides the best data to use for forecasting?
time series analysis
In the PQE ribbon, what do you use the Transform tab for?
to access common data transformation tasks, such as: - adding or removing columns - changing data types - splitting columns
When you load data into PBI Desktop, what is it good practice to do?
to analyze the corresponding metadata so you can identify any inconsistences with your dataset and normalize the data before you start to build reports. Running analysis on your metadata will improve data model performance because, while analyzing your metadata, 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), and much more.
Why is it better to create statistical measures yourself it's better to create the statistical measures yourself by using DAX functions to calculate average, sum, min, max, and so on, rather than using the ones available by right-clicking on field?
to avoid performance issues
In the PQE, when might you pivot columns?
to be able to identify patterns in the data to be able to summarize data by using different math functions such as Count, Minimum, Maximum, Median, Average, or Sum.
What is the first step in designing an exceptional report layout?
to choose the correct format. Likely, your manager (or whoever requested the report) will give you some requirements in terms of format. If not, you'll need to consider the audience and speculate about the kind of format that they'll want. For example, if your report users have a technical background and are looking for specific facts, you can use multiple, complex visuals that offer the most detail, along with interactive slicers. Conversely, if your users are looking for quick data insights at a high level, you could use a small range of basic visuals. If you have been given some layout requirements, you still need to carefully consider the report audience. Your goal is to provide the audience with the information that they need, in an optimal way.
What does the Cross-report drillthrough feature allow?
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.
How can you use buttons?
to 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.
What can you use the Drill through feature for?
to 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, as illustrated in the following image
What is another way to use tooltips?
to display graphical information
Why is it important to shape your data?
to ensure that it meets your needs and is suitable for use in reports.
What is the purpose of query folding?
to ensure that transformations can take place in the original data source server and do not overwhelm Power BI computing resources.
What is the purpose of shaping (transforming) your data?
to ensure that your data is suitable for use in reports
When creating a paginated report, how might you best use report headers and footers?
to help the user interpret the report. You can provide guidance to the user by documenting why this report was created. Adding a report implementation date and time is an excellent practice. Occasionally, reports are run and then saved. People who are looking at a report will not know that they are looking at an older version unless that fact is highlighted in a footer.
What can you use variables in your DAX formulas for?
to help you write less complex and more efficient calculations.
Why would you want to identify outliers in your data?
to isolate data points that significantly differ from other data points, and then take action to investigate the reasons for the differences. The results of this analysis can make a significant impact on business decision making.
What is the goal of a visual?
to present data in a way that provides context and insights, both of which would be difficult to discern from a raw table of numbers or text
What can you use buttons for?
to switch between two visuals in a report (as required in the previous example), to drill down into the data in a visual, or to move from one page in your report to another, to switch between two bookmarks
What is the problem with providing all data points on a given report?
too much data can make detecting key points difficult.
M code is written in what direction?
top-down
What are KPIs? What information do you need to create onee?
track progress toward a specific goal over time. To use a KPI, you need three pieces of information: - 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. - A goal for the measurement so that you can compare your progress with that goal. - A time series, for instance daily, monthly, or yearly.
What are line and area charts good for presenting?
trends over time
What should you do when when you are using Power BI with a screen reader?
turn scan mode or browse mode off.
What does a typical bar or column chart visual in PBI relate? How does a histogram differ?
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.
Where can you view the current data model?
under the Model tab on Power BI Desktop
What makes data modeling easier?
understanding and preparing your data first
What is the challenge that organizations have today regarding their data and information?
understanding and using their data to positively effect change within the business.
What is an indicator of a high level of cardinality?
unique count is high
What are inferences?
unstructured hypotheses that are gathered from several sources and expressed with varying degrees of confidence
How can you directly query Azure Analysis Services?
use DAX or MDX
When pinning a tile that has a time-based filter to a dashboard, what's a good practice?
use a relative date slicer
When creating a Q&A question that has a time-frame, what is best practice
use a relative date time-based question
What is a shape map?
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.
When creating variables, what is the best practice?
use descriptive names Using clear, concise, meaningful names will help make it easier for you to understand what you are trying to calculate, and it will be much simpler for other developers to maintain the report in the future.
How do you evenly distribute the distance between visuals on a canvas?
use the Align function. Use CTRL+click to select all visuals that you want to align, select the Format tab, and then select Distribute horizontally.
What is the first step to investigating the cause of performance issues?
use the Performance analyzer tool within Power BI Desktop.
What can you do if you want to create your own custom visual?
use the custom visual software development kit (SDK), which is an open-source tool based on NodeJS (JavaScript programming language) that is available on GitHub. The custom visual is packaged as a single Power BI Visual Tools (.pbiviz) file that you can import into Power BI Desktop.
Naming conventions for tables, columns, and values have no fixed rules; however, what is recommend?
use the language and abbreviations that are commonly used within your organization and that everyone agrees on and considers them as common terminology. A best practice is to give your tables, columns, and measures descriptive business terms and replace underscores ("_") with spaces. Be consistent with abbreviations, prefaces, and words like "number" and "ID." Excessively short abbreviations can cause confusion if they are not commonly used within the organization. Also, by removing prefixes or suffixes that you might use in table names and instead naming them in a simple format, you will help avoid confusion. When replacing values, try to imagine how those values will appear on the report. Values that are too long might be difficult to read and fit on a visual. Values that are too short might be difficult to interpret. Avoiding acronyms in values is also a good idea, provided that the text will fit on the visual.
Give an example of row-level security.
users assigned to the Salespeople security group can only view report data for the sales region(s) to which they're assigned.
What is fill map?
uses shading, tinting, or patterns to display how a value differs in proportion across a geographical region.
When can the data modeling process begin?
when the data is in a proper state
In the PQE, when is each step iterated?
when the data is refreshed
When does connecting to Analysis Service makes sense?
when there are existing model investments, typically forming part of an enterprise data warehouse (EDW). Power BI can make a live connection to Analysis Services, enforcing data permissions by using the identity of the Power BI report user. For SQL Server Analysis Services, both multidimensional models (cubes) and tabular models are supported. a live connection dataset passes queries to external-hosted models.
What do the three modes of models determine?
whether data is imported into the model, or whether it remains in the data source
What should you keep in mind when optimizing performance?
while the basic performance and best practices guidance in Power BI will lead you a long way, to optimize a data model for query performance, you will likely have to partner with a data engineer to drive data model optimizing in the source data sources.
What background color is best practice?
white, so the presented data can be seen clearly
How should you name tables in your data model
with concise yet friendly names
What feature allows you to analyze non-numerical data?
with the AI capabilities of Power BI, you can analyze text data to get more insights than before. For example, you might have an abundance of comments or reviews from customers or the results of an employee survey that have been left long forgotten in a database. Now, you can analyze this additional data and transform it into valuable information
How does Power BI help reduce data silos?
with the use of shared data sets
Can you view and modify the M language behind the steps in the PQE?
yes
Is it possible to select a step to preview the query results at that stage of transformation?
yes
For many-to-many relationships, what filtering directions can you choose?
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. For this reason, many-to-many relationships and/or bi-directional relationships are complicated. Unless you are certain what your data looks like when aggregated, these types of open-ended relationships with multiple filtering directions can introduce multiple paths through the data.
After you've retrieved your data and cleaned it in Power Query, where do you go next?
you can then go to the Model tab, where the data model is located.
What do you do if you want to find clusters by using more than two measures?
you can use a table visual instead, add all the fields you want to use, and then run the clustering algorithm by using the same process.
What are some the advantages of creating a date table from source data?
you can: Identify company holidays Separate calendar and fiscal year Identify weekends versus weekdays Source data tables are mature and ready for immediate use. 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
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.
What happens when you connect to an Excel file in OneDrive for Business into Power BI?
you keep the Excel file and your dataset, reports, and dashboards in Power BI synchronized. Power BI connects regularly to your file in OneDrive to refresh the data.
What is the disadvantage of using a summarized fact table?
you may lose the ability to drill into data because the detail no longer exists.
What might you do to optimize viewing reports on mobile devices?
you might want to create an additional view that is optimized for mobile devices and displays in portrait orientation. Power BI gives you the power to use visuals that make sense for mobile users and rearrange those visuals in the most effective way. When you have finished creating your report for the regular web view, you can then change the report so that it's optimized for use on phones and tablets.
What is required in order to merge two queries?
you must have column that is the key between the two tables
Once you have built a common date table either by source data, DAX, or Power Query, what do you need to do? How do you do that?
you need to mark your table as the official date table so that Power BI can recognize it for all future values and ensure that formatting is correct. find the new table on the Fields pane. Right-click the name of the table and then select Mark as date table, as shown in the following figure.
What is the advantage of importing data by writing an SQL query?
you select only the tables, columns, and rows that you need you can: - run specific calculations - join different tables - create logical statements - filter data
What happens when remove unnecessary columns from the import?
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.