Power BI Cheat Sheets

FAST
TRACK
to
POWER BI
CHEAT SHEETS
| Leila Gharani
XelPlus.com 1
Power BI
Power BI is a Business Intelligence Tool for anyone who needs to turn

data into information. It offers data modeling, data visualization &
data sharing.
You get to create your own reports without waiting for IT to set these
up. It’s designed for non-techies although techies love it too.
You get to be in charge and define your data preparation process, data
model and design reports.
POWER BI Desktop: Free Development tool of Power BI Reports.

There, you connect to your data, clean it, model it, add calculations and
visualizations. You can save your work in a .pbix file, and you can
publish it to the cloud or to an on-premises server.
POWER BI Service: Cloud method of sharing Power BI reports

and dashboards. Available for a monthly subscription fee. Limited
version also available for free. Requires a “work” email address.
POWER BI Report Server: On-premises Report Server that enables

sharing of Power BI reports for those that don’t want cloud
deployment. Available as subscription as part of Power BI Premium.
POWER BI Mobile: Free mobile app available for iOS, Android and
Windows. Allows you to connect and interact with your cloud and on-
premises data on the go.
© 2022 XelPlus
ALL RIGHTS RESERVED 2
www.xelplus.com
Power BI Desktop: High Level Overview
1. Start by getting
data here
4. Create reports using visuals and fields
2. View the data here (adjust formatting, add columns)
3. Relate the data here (model)
Every Power BI project begins by connecting to your source data (1).
You can then view the data in the data tab (2) and adjust formatting,
update column categories (for example whether a column is a URL or
a geographical location) and add new columns.
You can relate the data from different sources and tables with one
another in the Model tab (3). This is where you define the
relationships between the data (if you’re using multiple tables).
Finally, you create your reports in the report tab (4).
© 2022 XelPlus
www.xelplus.com
Quick Tour of Power BI Desktop
Most Common Features For Every Menu Option

File
control some important functions, such as
autodetection of relationships and data
types, security & language settings,
and preview features, among others.
Home
Format painter – easily copy and paste formatting between visuals

Get data– connect to a source
Enter data – manually enter data, create a new table
Transform data– open Power Query editor
(+ Data source settings – change the source path)
Refresh – get the latest data from the source
Publish – send your report to Power BI service, where it can be
securely stored and shared with others
Insert
AI visuals
Elements - Text box, Buttons, Shapes, Image
© 2022 XelPlus
www.xelplus.com
Modelling
Calculations -New/Quick measure, New column, New table

New parameters (Fields)
Security - Manage roles & View as
View
Themes – change the design of your report with 1 click

Mobile layout – optimize your report design for a mobile screen
Show panes - Bookmarks, Selection, Performance analyzer
Sync slicers – synchronize slicer selection between pages
Help – array of useful resources
© 2022 XelPlus
www.xelplus.com
Contextual Menus (dependent on selection)
Format (with visual selected)
Column tools (with column selected)

adjust column formatting assign a data category, change the
default summarization, define custom sort order
Measure tools (with measure selected)

adjust measure formatting, move to another table, or rename it
© 2022 XelPlus
www.xelplus.com
You will find yourself operating between the 3 principal views:
Report This is where you create visuals and

build the overall report.
Here you can preview your data,

Data assign the formats, categories and
summarizations to columns/fields.
In this view you add and manage

Model relationships between tables, shaping
them into a data model.
In all views you get the Fields pane, where

you can edit fields’ properties by right-
clicking, and (from Report view) add fields
to visualizations.
© 2022 XelPlus 7
ALL RIGHTS RESERVED
www.xelplus.com
Power BI Visuals
Canvas
Wallpaper
You add visuals to the canvas by

inserting them from the
Visualizations pane on the right
and populate them with fields
from the Fields pane.
Visualizations pane also includes
the formatting options.
If you start building visuals by

inserting a field, the default type
depends on the field you use.
Clustered column chart
if you start with a numeric value,
Table if you start with category,
Map if you start with geography
field.
© 2022 XelPlus
www.xelplus.com
Principles of Data Visualization
1 Know your audience
Who are you designing the report for?
What questions they want answered?
How proficient are they at reading complex charts?
2 Choose the right charts

Consider what type of chart will be best to represent the data.
Are you showing a trend, a comparison, actual vs target?
3 Keep it simple
Declutter the visuals, remove any unnecessary elements, stick to
a simple color scheme that isn’t distracting.
4 Make it clear
Make sure your data tells a clear story, and is easy to interpret.
5 Stay consistent
Use the same color for the same series throughout the report.
Use consistent abbreviations and labels.
6 Balance the design

Make sure everything is aligned. Use complementary color
schemes with the occasional pop of contrast where it serves to tell
the story.
© 2022 XelPlus
www.xelplus.com
Power BI Visualizations
Comparison
Bar & column charts

(clustered preferred)
Change over time – trend

Line & area charts
Ribbon chart
Column & combo charts
Part-to-whole
Pie & donut charts
Treemap
100% stacked column/bar
Progress towards goal

Gauge
KPI card
Combo chart
Spatial
Map, Filled Map,
Shape Map, Azure Map
Flow – dynamic relations between nodes

Waterfall
Funnel
Decomposition tree
Correlation
Scatter plot
Key influencers
Combo chart
© 2022 XelPlus 10
ALL RIGHTS RESERVED
www.xelplus.com
Visuals / Filtering
Visuals interact with each other – you can filter or cross-
highlight a visual by making a selection on another visual.
Cross-filtering removes data that doesn’t apply.

Cross-highlighting retains the original data points but dims
the portion that doesn’t apply.
Cross-filtered
Cross-
highlighted
Selection
In addition to the various charts and

slicers, from the Insert tab on the
ribbon you can add other visual
elements, such as text boxes, shapes
and images.
© 2022 XelPlus
www.xelplus.com
Filters Pane
Using the filter pane to the right of
the canvas, you can apply filters on:
- Selected visual (visual level filter)
- All visuals on the current page
(page level filter)
- All visuals on all pages of the
report (if more than 1) (report
level filter).
There are several filter types:

Basic, Advanced, Top N, Relative
date and Relative time (for date/time
fields only).
You can see all filters impacting a

visual by hovering over the funnel
icon next to it.
You can collapse the filter pane,

you can hide it (it won’t be visible to report consumers when
the report is published).
You can also hide a specific filter or lock it
(report consumers won’t be able to change it).
To clear filter, use the eraser icon.
© 2022 XelPlus
www.xelplus.com
Slicers
Slicers are visuals that allow you to filter other visuals.
They are more interactive than the Filter pane, as they are part
of the report.
You have several types to choose from
List Slicer (multiselect vs single select)
Button (List Slicer with Horizontal orientation)
Dropdown
Between / Less than or equal to / Greater than or equal to
© 2022 XelPlus
www.xelplus.com
Slicer Selection & Sync
When formatting slicers, you have several options regarding Selection.
The default is to enable Multi-select with Ctrl. If you disable it, you will
be able to multi-select without pressing the Ctrl key. You can also
enable “Select all” option, which will be added to the slicer.
If you enable Single select, one value always must be selected. The
default behavior of a multi-select slicer is that no selection is the same
as all selected. With Single select we can’t have all selected, so a
selection is required.
In the View tab, you’ll find the Sync slicers

pane. It allows you to control the visibility and
syncing of slicers between pages.
Syncing means that a slicer selection made on
one page will automatically be applied on the
other pages. A slicer doesn’t have to be visible
on the page, the visuals will be filtered based on
slicer selection from another page.
The visibility toggle is also a quick method of
replicating the same slicer across multiple
pages.
© 2022 XelPlus
www.xelplus.com
Edit interactions
Power BI visuals are highly interactive, selecting a datapoint in one
visual filters out other visuals.
You can control the way visuals interact with each other.
Select a visual, go to Format, and toggle on Edit interactions.
A new set of header icons will appear next to the unselected visuals.
You can disable the interaction completely, or you can change the
behavior from cross-filtering to cross-highlighting (only for some
visuals) or vice versa. Once done, toggle Edit interactions off.
Cross-highlight on
Cross-filter off
Cross-filter on
(no cross-highlight for this

type of visual)
Cross-filter on
(slicers can only filter, so

no cross-highlight option)
Cross-filter off
(the visual will not react

to slicer selection)
© 2022 XelPlus
www.xelplus.com
Drill down Hierarchy
Power BI supports Hierarchies. Some are generated automatically,
like the Date Hierarchy, when the Auto Date/Time setting is on.
Some can be created, either by dragging and dropping multiple fields
to the category well of a visual, or by creating an official hierarchy in
the data model.
Once a Hierarchy is added to a visual, you can drill down and up, to
explore each Hierarchy level. An additional set of icon headers
becomes available.
Click to Go to the Expand all down

Drill up
turn on next level in one level in the
Drill down the hierarchy hierarchy
© 2022 XelPlus
www.xelplus.com
Drill down
You can also access drilling options from the ribbon – whenever a
visual with hierarchy is selected, the contextual Data Drill menu
becomes available. From there, you can drill down into all hierarchical
visuals on the page with a single click.
Expands all fields at once

A good way to see the trend of changes through the years
and months, all in one view, or see the categories in their
hierarchical context
Drill down all fields at once

The next level is accumulated. A good way to compare
values in another level of hierarchy regardless of higher
levels (e.g. compare seasonal
performance of different quarters)
When Drill Mode is on, click a data point to drill

down one field at a time
Good for finding and investigating anomalies in
the data
Turn it off once you’re done with data exploration
Takes you one level up in the hierarchy – go back up from

all drill down types
© 2022 XelPlus
www.xelplus.com
AI visuals
Power BI analysis is supported by artificial intelligence capabilities.
That includes AI visuals that automatically generate insights from your
data.
Smart narrative – automatically generates a written summary of

observable trends based on the data from other visuals on the page.
You can format the text & values, and make manual adjustments,
including deleting paragraphs that don’t offer meaningful insight.
It reacts to filters and is fully dynamic, but takes a while to load and
can slow down the report
Q&A - allows you to ask natural language questions about your data
and generates a “standard” visual based on your request. It analyzes
your question, chooses a suitable visual type and applies the relevant
filters.
Convert to
standard visual
Teach Q&A*
What the visual is

showing (series and
filters)
* You can improve the quality

of Q&A results by reviewing
and adding synonyms to your
fields and by training the tool.
© 2022 XelPlus
www.xelplus.com
AI visuals
You’ll find another example of Power BI’s AI capabilities when you
right-click on a datapoint in a line or bar/column chart. It’s called
Analyze. It analyzes fluctuations between data points and looks for
patterns in your data that can explain it.
You can ask it to Explain the decrease/increase or to Find where the

distribution is different.
A window opens with the generated insights. There are multiple

suggested contributors ranked by whatever the machine learning
algorithm deemed most significant. You can switch between various
visualizations of the trends – the selection includes the default
waterfall chart, as well as scatterplot, 100% stacked column chart and
a ribbon chart.
If you’re particularly happy with any of the generated insights and

would like to make it part of your final report, you can click on the plus
icon to add it to the canvas.
It’s available in Power BI Desktop and in Power BI Service.
© 2022 XelPlus
www.xelplus.com
Ribbon chart
Ribbon charts are great at visualizing rank changes. They are
similar to a stacked column chart, but the highest value is
always displayed on top for each time period and we see the
connecting ribbons.
When you hover over the connecting ribbon, the tooltip

shows you the details of the rank change. When you hover
over the category, you see the standard data label for this
category.
Since the ribbon chart doesn’t have y-axis labels, make sure
data labels are on.
© 2022 XelPlus
www.xelplus.com
Analytics pane
You can enhance some of the visuals (in particular line and
column/bar charts) with an additional analytical layer.
The third icon on the Visualizations pane represents Analytics and
allows you to add more information to your charts, like a trend line,
average or median line, error bars, forecast, or a constant line, useful
if you have a static target.
© 2022 XelPlus
www.xelplus.com
Conditional Formatting
When you go to formatting, you may notice some of the options
have an fx button. This means you can apply conditional
formatting to them. And there are many types and many uses for
conditional formatting in Power BI.
There are 3 format styles, not

all are applicable for all
scenarios.
You can apply conditional
formatting to series color based
on a value, on specified
thresholds, or define the colors
in a measure.
Table and Matrix visuals have many conditional

formatting options, similar to Excel.
You can also use conditional formatting to make

titles dynamic, based on a measure.
Next time you see the fx icon in the

Format visual pane, explore the
possibilities.
© 2022 XelPlus
www.xelplus.com
Custom Tooltips
In Power BI, you will see tooltips when you hover over visuals. The
standard tooltip shows the data label and value of the datapoint you
are on. You can customize it by dragging additional fields and measures
to the Tooltip well in the visualizations pane.
Another method for custom tooltips is to create a tooltip page that is

then linked with a visual or visuals on the standard page. Tooltip pages
can include visuals and images, which makes them more compelling,
and can fit more information.
The visuals on the tooltip page are filtered in the background by the
datapoint over which you are hovering.
When you add a new page, go to Page information

and enable “Allow use as tooltip”. Canvas type will
automatically change to Tooltip, but you can also
customize the size to optimally fit your tooltip
visuals.
Design the tooltip, and then
go to format the visual you
want to have the tooltip.
Under General > Tooltips,
select type Report page, and
then your tooltip page.
You can also have the report page tooltip appear over a specific field
whenever used in a visual. You set it up from the Visualization pane
on the tooltip page. © 2022 XelPlus
www.xelplus.com
Navigation
You can improve user experience of your report by adding app-like
navigation buttons, connected to report pages. They allow users to
effortlessly move around the report, and also serve as a “table of
contents” (make sure your page names are informative).
Go to Insert > Buttons > Page navigator
You can format the

shape, rotation, style,
layout, and decide
whether hidden and
tooltip pages should
be visible.
You define the style for each

state, which enhances the
feeling of interactivity, as the
buttons “react” to different
user actions.
For example, you can have the
text push up on hover by
adjusting the padding.
The simplest way to include

icons is to add windows emojis
(use shortcut ) to the
page names (the names in the
navigator are derived directly
from there).
© 2022 XelPlus
www.xelplus.com
Power Query
Power Query is a data transformation and preparation engine of

Power BI. Its purpose is to extract the data – connect with data
source, then transform it in the Power Query Editor, and finally load
the transformed data to Power BI. That’s why it’s called an ETL tool.
Get Data – Import – Direct Query - Live

Power BI has over 150 source connectors, and keeps adding new ones.
They are grouped into the following categories:
✓ File (incl. Excel, Text/CSV, PDF)
✓ Database (incl. SQL Server, Oracle, SAP Hana)
✓ Power Platform (incl. Datasets, Dataflows, Dataverse)
✓ Azure (incl. Azure SQL Database, Azure Synapse Analytics SQL)
✓ Online Services (incl. SharePoint Online List, Salesforce, Google Analytics)
✓ Other (incl. Web, SharePoint List, Microsoft Exchange, Google Sheets)
When connecting to certain database sources, you get to choose the
Data Connectivity mode.
Default is Import: The compressed data is loaded into memory.
The alternative is DirectQuery: Connects directly to source - data is
not imported to Power BI. All queries are processed in the source and
not on your device.
Sources using the same storage engine as Power BI (SQL Server
Analysis Services, Azure Analysis Services) prompt you to Connect
Live instead. You connect directly to the model, there’s no query
involved. You can’t edit the data or the model.
Live Connection (with no Import option) also occurs, when you
connect to a published Power BI dataset.
© 2022 XelPlus
www.xelplus.com
Import – Direct Query – Live Connection
Import Direct Query Live

Connection
Memory Data fully loaded Not impacted Not impacted
to memory
Model size 1/100/400* GB No model size No model size
(scalability) limitation limit limit
(*depending on
license type)
Speed Generally, the Depends on the Fast
fastest method speed of the
source
Transform Data Yes Limited No (you do it in
(Power Query) the source)
Edit Model Yes You can manage No (you do it in

relationships the source)
DAX Yes Limited Calculated in
Calculations the source or
report level
measures
Refresh Manual or Automatic Automatic
scheduled
Features Full Limited (no Limited (no
availability Quick Insights) Quick Insights)
Sources Most Databases, data SQL Server
connectors, warehouses Analysis
except for Services, Azure
Power BI Analysis
datasets Services, Power
BI datasets
© 2022 XelPlus 26
ALL RIGHTS RESERVED
www.xelplus.com
Quick Tour of Power Query Editor
Power Query comes with a user-friendly graphical interface.
You can achieve most transformations with a click of a button.

The underlying M code is being recorded for each step you make (you can
see it in the formula bar and in the Advanced Editor).
You can edit (most) steps, delete and reorder them.
Remove other
columns Sort and filter
Append
or merge
Rename
the query
You can edit

those steps
Change to all rows

to check for errors
On the Home tab you’ll find some of the most common transformations.
👁 TIP: You’ll also find the common transformations when you right mouse
click on a column.
© 2022 XelPlus 27
ALL RIGHTS RESERVED
www.xelplus.com
Append
A B C
1 1 1 A B C D
2 2 2 1 1 1 null
3 3 3 2 2 2 null
3 3 3 null
4 4 null 4
A B D 5 5 null 5
4 4 4
5 5 5
Merge
A B C
aa bb 1
ab ba 2
ad be 3 A B C D E
aa bb 1 dd ee
ab ba 2 de ed
D E F
ad be 3 da eb
dd ee 1
de ed 2
da eb 3
Unpivot
BC Attributes Values
bc A1 V1
bc A1 V4
BC A1 A2 A3 bc A1 V7
bc V1 V2 V3 bc A2 V2
bc A3 V3
bc A3 V6
bc A3 V9
© 2022 XelPlus
www.xelplus.com
Data Model
Best Practices for Data Modeling
1. Remove unnecessary columns and filter out any

unnecessary rows before loading them to the model.
2. Use a proper dataset (data in columns without subtotals
or gaps, consistent granularity of data).
3. Use a Star Schema – separate dimension tables from the
fact table(s). Use/create tables with a single purpose.
4. Use normalized tables - fact tables store the keys and
not their descriptions. Descriptions are stored in the
dimension tables.
5. Aim for low cardinality where possible. The fewer
unique values the better. You can achieve that by
removing unnecessary precision (e.g. using whole
numbers instead of decimals, separating date and time
fields).
6. One-to-many relationships and single cross-filter
direction, where the “one” side filters the “many” side,
are preferred.
© 2022 XelPlus
www.xelplus.com
Proper Data Set
To conduct any analysis, you need clean data, organized

in a proper data set. What does it mean?
1. Each column has a single purpose (you don’t mix

customers and countries in the same column)
2. There are no empty rows & columns
3. Each column has one single data type (so either

date, number, text, but not a mixture of those in a
single column)
If your source data is not structured this way,

Power Query is the perfect tool to clean it up.
© 2022 XelPlus
www.xelplus.com
DAX
Data Analysis Expressions (DAX) is a collection of functions you can
use in the data model. It’s a formula language that was designed for
working with relational data both in Power BI and Excel Power Pivot.
It can be used to create calculated tables,

calculated columns and measures.
Common DAX Measures
CALENDAR, CALENDARAUTO – to create a calendar table.
SUM, AVERAGE, MAX, MIN – the “standard” functions you’re familiar

with from Excel.
SUMX, AVERAGEX, MAXX, MINX – the iterator versions of those

functions. They move row by row* across a table, calculating a specified
eXpression (hence the X) for each row, and only then apply the
aggregation defined in the name.
Total Sales = SUMX ( 'Sales’, 'Sales'[Quantity] * 'Sales'[SalesPrice] )
RELATED – used when you need to retrieve a value from a specific row
in a related table, a bit like VLOOKUP. Returns a corresponding value
from the “one” side of the relationship to the “many” side.
Total Cost = SUMX ( Sales, Sales[Quantity] * RELATED('Product'[Cost]) )
DIVIDE – avoid the division by zero error.

Gross Margin % = DIVIDE ( [Gross Profit], [Total Sales] )
TOTALYTD – one of many Time Intelligence functions, calculating a

year-to-date total. You can also use TOTALQTD and TOTALMTD for
quarters and months respectively.
* ROW CONTEXT can be interpreted as the current row when

iterating over a table. Functions such as SUMX and RELATED rely on
the ROW CONTEXT to evaluate the result of an expression for each
row of a table. *
© 2022 XelPlus
31
ALL RIGHTS RESERVED
www.xelplus.com
Quick Measures
A quick measure is a type of explicit measure that is created using
a template. You can create calculations without prior knowledge
of the DAX formula language.
© 2022 XelPlus
www.xelplus.com
Calendar Table - The Rules
Minimum requirement is a table with a single column
containing a unique list of dates.
Each date exists once and includes every possible day in a

year without gaps.
The start date in the Calendar table is before or starts

from the first date in your model and ends on or after the
last date in your data model.
It’s marked as a date table.
Can include additional columns, like year, quarter, month,

week, weekday etc.
Can be created with Power Query or DAX,

or loaded to the model from an external source.
Can be used to create relationships between tables.
If you have Date & Time in one column in the fact table,
split them into two separate columns with corresponding
data types.
© 2022 XelPlus
www.xelplus.com
CALCULATE function
CALCULATE is one of the most powerful functions in DAX. It allows you
to change the filter context*.
In the CALCULATE syntax, the first argument is an expression that we

want calculated. It can be a nested formula or an existing measure.
Then come the filter condition(s).
Sales Innovative = CALCULATE (

[Total Sales],
’Product'[ProductType] = "innovative“)
But it is not evaluated in this order. The initial expression (e.g. Total Sales)
is only calculated once the filters are applied – first the surrounding filters
coming from the report page, then the filter(s) specified in the
CALCULATE function (e.g. innovative products).
When the external and internal filters come into conflict when filtering
the same field, the internal filter of CALCULATE overrides the external
one, and only then returns the result of the expression.
This behavior of CALCULATE can be modified or further exploited with

the help of CALCULATE modifiers, such as:
ALL / REMOVEFILTERS – ignores the external filter context entirely,
always returns an unfiltered table
KEEPFILTERS – stops internal CALCULATE filters from overriding
external filters
USERELATIONSHIP – activates an inactive relationship inside a measure
Orders (Shipment) = CALCULATE(
[Orders],
USERELATIONSHIP( ‘Sales'[ShipmentDate], 'Calendar'[Date] ))
* FILTER CONTEXT are all the filters applied to the data model before
a DAX expression is evaluated. This includes external filters, coming
from slicers, filter pane, cross-filtering visuals, as well as any category
breakdowns used in visuals (rows and columns of a matrix or series of
a chart). *
© 2022 XelPlus
34
ALL RIGHTS RESERVED
www.xelplus.com
Power BI Service
POWER BI Service (app.powerbi.com) is the online platform
where you can store your reports, create dashboards, and
securely share them. It requires sign in with a work or school
email address. It’s available for free, but full collaboration
and sharing is only possible with a paid license.
When you publish a report in Power BI Desktop, it is

published to the Service, together with the underlying
dataset that you created by transforming and modelling
source data.
You can create reports directly in the Service, but you are
limited to a few source types that don’t require modelling, like
an existing dataset.
You can edit in the Service a report created on the Desktop,

but not the data model or the measures.
Power BI Desktop Power BI Service

Many data sources Some data sources
Reports
Transforming Visualizations Dashboards
Shaping & modeling Security Workspaces
Measures Filters Sharing
Bookmarks RLS* management
Calculated columns
Q&A
Themes Gateway connections
RLS* creation
© 2022 XelPlus
*RLS = row-level security
www.xelplus.com
Quick Tour of Power BI Service
Your Created when you Created from the

primary published your published report
Power BI report from Power by pinning visuals
“folder” BI Desktop or live page
Analyze in Excel Reset all

Pin visual to
(Pro/Premium only); filters Refresh
a dashboard
Download file; Embed in PowerPoint; and slicers visuals
Publish to Web Generate PDF
© 2022 XelPlus
www.xelplus.com
Dataset
Power BI datasets represent a source of data ready for

reporting and visualization, after it has been transformed,
cleaned, and shaped into a data model, including any
necessary calculations, and then published to Power BI
Service.
One dataset can be used repeatedly for multiple reports
and dashboards.
You can connect to a published dataset from Power BI

Desktop and build a new report, without the need
to transform or model the data again.
With this Live connection to a Power BI dataset, you can’t
change the model or edit the measures. You can create
additional, report-level measures, if needed.
In Lineage view, you can trace the relationships

and dependencies between each element
in a workspace, starting at the source.
Source(s) Dataset Report(s) Dashboard(s)
© 2022 XelPlus
www.xelplus.com
Report vs Dashboard
In Power BI you can create reports and dashboards.
Sometimes you’ll find people referring to the reports created
in Power BI Desktop as dashboards, but in fact, Power BI
dashboards are their own distinct thing.
Dashboards are created

✓ from reports by pinning individual visuals (or an entire
report page),
✓ from new visuals generated by querying the dataset(s)
(the Q&A feature),
✓ and by adding other elements (such as images, web
content, videos, text boxes).
Each element constitutes a tile. You arrange the tiles on the

page to create a dashboard.
Dashboards keep the connection to the underlying datasets
(which means you can refresh them), but they do not react to
the filters applied in the underlying report(s).
Reports Dashboards
✓ created in Desktop or Service ✓ created in Power BI Service
✓ can be multi-page, allow for ✓ single page only, overview
more detail highlighting key metrics
✓ can be used to create multiple ✓ can display visualizations from
dashboards many different reports
✓ can be created using data ✓ can include tiles with data
from one dataset only from many datasets
✓ no alerts ✓ set conditional email alerts
✓ fully interactive ✓ not interactive (unless a whole
report page pinned)
© 2022 XelPlus
www.xelplus.com
Designing for Mobile
You can access both reports and dashboards on your phone

through the Power BI Mobile app.
To improve the experience, you can design a separate mobile

layout for your reports (in Desktop or Service)
and dashboards (in Service).
You can limit the number visuals for the mobile layout,
and streamline them. For example, you can overlay one visual
over the other, like a card inside (under) a donut chart, to save
space.
Any formatting changes you apply in the mobile layout won’t
affect the primary view. You have 2 design options for the
same visual, depending on the layout.
The optimized view will be available in portrait

orientation.
In landscape mode, the default desktop layout is used. That

way the user has access to both a quick mobile-friendly
overview and to the whole detailed report and can easily
switch between them.
© 2022 XelPlus
www.xelplus.com
Licensing & Sharing
Power BI Desktop can be downloaded for free.
Power BI Service requires an account set up in an organizational

domain (a work or school email address).
You can publish to the Power BI Service for free (with a free account),
but in order to share your reports, you need a paid license (Pro or
Premium per User). In order to access reports created by others, you
also need a paid license, or your organization needs to have a Premium
per Capacity subscription and the report needs to be published to a
Premium workspace.
Free Pro Premium per User Premium per

(PPU) Capacity
(Premium)
has access to My basic paid account, includes (some) subscription for

Workspace and licensed per user Premium features (large)
can create reports on a per-user basis organizations
for own
consumption can share content can share content not a license
with Pro and PPU with other PPU assigned to users
can publish to the accounts accounts
Web (public) – only way of managing
way of sharing can publish to a content at scale
Premium Capacity
can only consume workspace no limitations
content from when it comes to
others if published sharing with other
to Premium licenses
capacity
Model size limit Model size limit Model size limit Model size limit
1 GB, 1 GB, 100 GB, 400 GB,
Maximum storage Maximum storage Maximum storage Maximum storage
10 GB, 10 GB, 100 TB, 100 TB,
Refresh rate Refresh rate Refresh rate Refresh rate
8/day 8/day 48/day 48/day
© 2022 XelPlus
www.xelplus.com
Licensing & Sharing
If you create/share Power BI content in the Power

BI Service in this type of workspace
Free Pro Premium Premium
per User Capacity
Free Yes No No Yes
(My Workspace
only)
Users with this
type of license Pro No Yes No Yes
can view/access
the content
Premium No Yes Yes Yes
per User
You can look up and change the

workspace type in Workspace Settings.
Report developers in
organizations with Premium
per Capacity subscriptions
require a named Pro license to
publish and share reports.
© 2022 XelPlus
www.xelplus.com
Workspace Roles
Workspaces are places to collaborate with colleagues and create
collections of dashboards, reports, datasets, and paginated reports.
You can control access to a workspace (and the content they contain)
by assigning workspace roles.
Viewers can only view and interact with a report, dashboard or

dataset in the workspace. They can share items if they have been
granted reshare permissions by a Member or Admin.
Contributors, in addition to viewing and potentially resharing, can

create, edit and delete reports in the workspace. They can publish
reports to the workspace or delete content, as well as schedule
content refresh.
Members can do all of the above, as well as add other members,

contributors and viewers (roles with the same or lower permissions).
They can manage dataset permissions and allow contributors and
viewers to reshare items. They can’t update or delete a workspace.
Admins have full control, they can update or delete a workspace, as

well as add other admins.
© 2022 XelPlus
www.xelplus.com
Dataset Permissions
If you don’t want to give users rights to the
entire workspace, only to a specific dataset or
report, then you control access by Managing
Permissions.
When you add users to a dataset, you have

the following options:
Read
Write
Reshare
Build
A user's role in a workspace implicitly grants them permissions on the

datasets in the workspace.
Admin Member Contributor Viewer
Read ☑️ ☑️ ☑️ ☑️
Build ☑️ ☑️ ☑️ 🚫
Reshare ☑️ ☑️ 🚫 🚫
Write ☑️ ☑️ ☑️ 🚫
© 2022 XelPlus
www.xelplus.com
Sharing Reports
You can also share reports without sharing the underlying dataset.
You do that either from Manage Permissions, or with the Share
button, by unchecking the build permissions to the dataset.
The same applies to

dashboards.
© 2022 XelPlus
www.xelplus.com
Refresh & Dataset Settings
To refresh data in Power BI

Service and to schedule refresh,
you need to either install an on-
premises data gateway (for local
files)
… or provide data source

credentials (for cloud and
database sources).
If you want your dataset to

be discoverable by other
users, mark it as Promoted
in the Endorsement and
discovery settings.
© 2022 XelPlus
www.xelplus.com
Power BI & Excel
You can analyze a Power BI dataset in Excel.
There are 2 ways of going about it:
1 Start in Power BI Service:

Analyze in Excel. 1
2 Start in Excel:
Insert > PivotTable > From Power BI
or Data > Get Data > From Power
2
Platform > From Power BI.
You can create pivot tables and pivot

charts, as you normally would in Excel,
with the Power BI dataset serving as the
single source of truth for the
organization.
Considerations
1) Your data model must have explicit measures.

2) Dataset must be in a Premium capacity workspace, or you must
have a Power BI Pro or Premium per User license.
3) You must have Build permissions to the underlying dataset or be
a Member of the workspace containing the dataset.
You can also Import a data model

created in Excel Power Pivot from
Excel to Power BI Desktop and publish
it as a dataset to Power BI Service,
where it can be more easily and
securely shared within the
organization.
© 2022 XelPlus
www.xelplus.com
THANK YOU!
Please take a few seconds to
leave a review for the course.
Your feedback is very much
appreciated.
47
More Learning…
Our courses to help you succeed at work:
https://www.xelplus.com/courses/
I share free content on YouTube every week. My Channel is

under my name.
I share tips & tricks on my LinkedIn account as well.
You’ll find detailed blog posts and articles related to Excel and
PowerPoint on my Website at www.XelPlus.com
Give yourself a professional boost

Once you complete the course, feel free to post your certificate
to social media. Show potential recruiters that you went the
extra mile to give yourself a professional boost.
48

Power BI Cheat Sheets

Uploaded by

Copyright:

Available Formats

Power BI Cheat Sheets

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Power BI Cheat Sheets

Uploaded by

Copyright:

Available Formats

FAST

Power BI is a Business Intelligence Tool for anyone who needs to turn

POWER BI Desktop: Free Development tool of Power BI Reports.

POWER BI Service: Cloud method of sharing Power BI reports

POWER BI Report Server: On-premises Report Server that enables

4. Create reports using visuals and fields

2. View the data here (adjust formatting, add columns)

3. Relate the data here (model)

Every Power BI project begins by connecting to your source data (1).

Finally, you create your reports in the report tab (4).

Most Common Features For Every Menu Option

Format painter – easily copy and paste formatting between visuals

Calculations -New/Quick measure, New column, New table

Themes – change the design of your report with 1 click

Help – array of useful resources

Contextual Menus (dependent on selection)

Format (with visual selected)

Column tools (with column selected)

Measure tools (with measure selected)

You will find yourself operating between the 3 principal views:

Report This is where you create visuals and

Here you can preview your data,

In this view you add and manage

In all views you get the Fields pane, where

You add visuals to the canvas by

If you start building visuals by

2 Choose the right charts

6 Balance the design

Bar & column charts

Change over time – trend

Progress towards goal

Flow – dynamic relations between nodes

Cross-filtering removes data that doesn’t apply.

In addition to the various charts and

There are several filter types:

You can see all filters impacting a

You can collapse the filter pane,

You have several types to choose from

List Slicer (multiselect vs single select)

Button (List Slicer with Horizontal orientation)

Between / Less than or equal to / Greater than or equal to

In the View tab, you’ll find the Sync slicers

(no cross-highlight for this

(slicers can only filter, so

(the visual will not react

Click to Go to the Expand all down

Expands all fields at once

Drill down all fields at once

When Drill Mode is on, click a data point to drill

Takes you one level up in the hierarchy – go back up from

Smart narrative – automatically generates a written summary of

What the visual is

* You can improve the quality

You can ask it to Explain the decrease/increase or to Find where the

A window opens with the generated insights. There are multiple

If you’re particularly happy with any of the generated insights and

It’s available in Power BI Desktop and in Power BI Service.

When you hover over the connecting ribbon, the tooltip

There are 3 format styles, not