PowerBi notes

Key influencer visuals

 Key influencer visual is Microsoft’s attempt to visualize a multiple regression algorithm where multiple
variables (number of cars owned, Occupation, Education) affect the customer’s Yearly income.
 Using standard deviation, the key influencer brings all the input variables on a common scale.
 So, whenever you see different numeric input variables, you would know that for every increase in 1
standard deviation (of its value), the yearly income increases by Y.
 The Key influencers visual helps you understand correlated factors impacting a particular metric.
 The Q&A visual allows end-users to ask natural language questions to create AI generated charts based on
the questions.
 The Decomposition Tree visual lets you visualize data between multiple dimensions and drill down in any
order. The Smart Narrative visual lets you combine natural language text with metrics from your model in
sentence forms.

Functions
DAX FORMAT function :converts the Date into a specified format.

1. YY displays 19 (for the year 2019)

2. YYYY displays the year 2019

3. M displays 1 (for January)

4. MM displays 01 (for January)

5. MMM displays Jan (for January)

6. MMMM displays January (for January)

CAST (sql func) :

CAST function in SQL isused to explicitly convert one data type to another. It is part of SQL's data type conversion
functions and is particularly useful when dealing with mismatched data types in queries or when formatting data for
specific needs.

LASTDATE function will ensure that the SUM function applies only to the last date of the time period, resulting in a
semi-additive behavior.

DISTINCTCOUNT function counts the number of distinct values in a column, which results in additive behavior.

CALENDAR function returns a table with a column named Date that contains a contiguous set of dates based on the
start date and end date that you specify.

CALENDARAUTO function generates a range of dates based on the earliest and latest date within the dataset

DATE function returns the specified date in the datetime format.

DATEDIFF function returns an interval between two dates.

Parameters
A parameter in Power BI is a configurable value that can influence how queries are executed. When you use a
parameter in a query, it allows you to define a variable, like a server name or a file path, which can later be updated
without modifying the query itself.
A parameter is the only part of a query that can be updated or changed in the Power BI service, by accessing the
dataset settings. Updating the server source to use a parameter will update all existing queries pointing to the
current server to instead use a parameter with that server name.

They allow users to influence data loading, filter data, or change report settings without having to edit or redeploy
the report.

 Parameters are most often set up through Power Query (in Power BI Desktop).

 They can be referenced in queries, M code, or in connection settings.

Parameter Description Example Usage Example

Text Accepts any text (string) value "West", "2024" Filter data where Region = "West"

Toggle a report page or feature

Decimal
Number Numeric with decimals (floating point) 3.14, 100.0 Filter data where Sales > 3.14

Numeric, only accepts whole (integer) Limit number of rows to load: Top
Whole Number numbers 1, 10, -7 N = 10

Date/Time Accepts date or timestamp values 2022-01-01 Load sales after a certain date

When Are Parameters Needed?

Parameters are needed in Power BI when you want:

 To filter data dynamically (e.g., only load data from a selected region or timeframe).

 To make connections dynamic (e.g., switch between development and production databases).

 For what-if analyses (e.g., see the impact of changing a threshold value).

 To allow user-driven input inside reports (though this use is limited in Power BI Desktop, more interactive
parameter selection is available in Power BI Service with paginated reports).

General Pointers
 Advanced Editor of Power Query Editor provides the ability to work with the M code used for shaping data in
Power Query Editor.
 The Formula Bar in the Power Query Editor provides the ability to edit only one step at a time.
 Unlike a measure, a calculated column can be used in a slicer to place filter options on the report page.
 DAX measures cannot be placed in the “Filters on this page” well. They can only be placed per visual, in the
“Filters on this visual” well of the Filters Pane.
  Both DAX columns and measures may be used as a visual-level filter. Both DAX columns and measures can
be used in the drillthrough well.
 Calculated tables are generated with DAX queries. Numeric range parameters create a table and measure,
both generated with DAX queries.
 Tables and reports cannot be assigned a display folder. Columns, calculated columns, and measures can be
assigned a display folder.
 A column that has a lot of repeated values and a low number of distinct values will have a low cardinality.
Reducing the number of distinct values reduces cardinality. Replacing nulls does not change the number of
distinct values. A static prefix or suffix does not change cardinality.
 By default, report tooltips will apply the filter from the cross-highlighted data into the tooltip. Report page
tooltips need to be manually created and are not assigned to a visual by default
 A List group type is a manual grouping of items into new group names/categories. It doesn’t create auto
(binned) groups.
 The Data Source Settings in the Power Query interface supports editing permissions, clearing permissions,
and modifying the path of the data source file. Any structural changes to a file, such as adding, removing, or
renaming columns are not supported by the Data Source Settings in the Power Query interface.
 The maximum number of refreshes per day for a Power BI workspace in a shared capacity is 8.
 If there are 4 consecutive data set refresh failures, the refresh schedule will be automatically disabled.
 To implement an incremental refresh of the dataset, you first need to define an incremental refresh policy,
which, in turn, requires defining filter parameters. An incremental refresh policy does not require upgrading
to a Premium SKU. Publishing the dataset to the Power BI service is required for the incremental refresh
policy to take effect, so it's the last step of setting up an incremental refresh.
 Unlike a measure, a calculated column can be used in a slicer to place filter options on the report page. DAX
measures cannot be placed in the “Filters on this page” well. They can only be placed per visual, in the
“Filters on this visual” well of the Filters Pane. Both DAX columns and measures may be used as a visual-level
filter. Both DAX columns and measures can be used in the drillthrough well.
 The ODBC connector lets you import data from any third-party ODBC driver simply by specifying a Data
Source Name (DSN) or a connection string. As an option, you can also specify a SQL statement to execute
against the ODBC [Link] details a few examples of data sources to which Power BI Desktop can connect
by using the generic ODBC interface:[Link]
connect-using-generic-interfaces
 the parse function refers to built-in tools and methods in Power Query that convert
unstructured or semi-structured data—such as text, JSON, XML, or delimited values—
into structured columns that you can use in your data model.

Dashboards(PBI Service)

 The Power BI service provides support for creating Power BI dashboards. The Power BI mobile app can be
used to view dashboards, but not to create them. Power BI Desktop does not provide support for creating
Power BI dashboards. Power Query is a Microsoft Excel tool that is used for importing data, but not for
creating dashboard.
 The ability to combine data from multiple datasets is one of the features that is available in dashboards but
not supported in reports. Dashboards do not include the Filter, Visualization, and Fields panes that are
available in Power BI Desktop when working with dashboards. Dashboards, unlike reports, contain only a
single page.
 Individual filters and slicers cannot be pinned to a dashboard. This is because dashboards are designed to be
an “at-a-glance” perspective. Reports are built to be filtered and interactive. Custom visuals, ribbon charts,
and images can be pinned from a report page.
 Workspace apps can be installed automatically for any user in an organization, and without requiring
notifications of installation. Deployment pipelines are used for moving artifacts between test, development,
and production environments. Metrics are where you can create goals based off existing datasets. You can
add members to a workspace, but they receive a notification that they’ve been added to the workspace.
Access :

 The Contributor role is the least privileged role that grants permissions to schedule data refreshes. The
 Member role grants permission to schedule data refreshes but is more privileged than Contributor.
o The member role can add other members of equal or lower privilege.
 The Admin role grants the permissions to schedule data refreshes but is more privileged than Member.
 The Viewer role does not grant the permissions to schedule data refreshes.

Model Perfomance
 DAX Studio, once connected to the data model, can be used to clear the data engine cache. The Clear Cache
function can be invoked from DAX Studio, once you connect it to the data model. The Refresh Metadata
function can be invoked from DAX Studio to update the metadata of the currently selected model. Adding a
blank page to the .pbix file and selecting it is the first step in clearing the visual cache, not the data engine
cache.
 The Reduce number of queries sent by Query reduction setting disables the default behavior that
automatically applies cross highlighting and filtering of visuals within the same report. The Slicers Query
reduction settings allow you to instantly apply slicer changes and add an Apply button to each slicer. The
Filters Query reduction settings allow you to instantly apply basic filter changes. The Persistent filters of
Report settings allow you to prevent users from saving filters in the Power BI service.
 To eliminate the impact of visual caching during a performance evaluation when using Performance Analyzer
to evaluate the visuals, you must first add a blank page to the report, select that page, and then reopen the
PBIX file. Connecting DAX Studio to the data model is the first step to clear the data engine cache. Recording
should not be started until both the visual cache and the data engine cache are cleared.