Data Analysis & Power

BI
Power BI Ecosystem
Power Platform

The ‘Power Platform’ is a collective term

for four key products: Power Apps,
Power Automate, Power BI and Power
Virtual Agents
Power App

Power Apps provides a rapid low code

development environment for building
custom apps for business needs. It has
services, connectors, and a scalable data
service and app platform (Microsoft
Dataverse) to allow simple integration and
interaction with existing data. Power Apps
enables the creation of web and mobile
applications that run on all devices.
Power Automate

Power Automate lets users create

automated workflows between applications
and services. It helps automate repetitive
business processes such as communication,
data collections, and decision approvals.
Power Virtual Agent

Power Virtual Agents enables anyone to

create powerful chatbots using a guided, no-
code graphical interface, without the need
for data scientists or developers.
It minimizes the IT effort required to deploy
and maintain a custom solution by
empowering subject matter experts to build
and maintain their own conversational
solutions..
Power BI

Power BI (Business Intelligence) is a

business analytics service that delivers
insights for analyzing data. It can share
those insights through data visualizations
which make up reports and dashboards to
enable fast, informed decisions. Power BI
scales across an organization, and it has
built-in governance and security allowing
businesses to focus on using data more than
Configure Data
• Rename Table
• Rename Column
• Table Tools
• Column Tools
• Property Pane
• Model View
• Create Relationship
Create Calculated Columns and measures
• Calculated Columns
Gross Amount = [Qty]*Sales[Price]
Discount Amount = [Gross Amount]*[Discount Percent]/100.0
COGS Amount= [QTY] *[COST]

• Measures
Gross = Sum(Sales[Gross Amount])
Discount = sum(Sales[Discount Amount])
Net = [Gross] -[Discount]
COGS = sum(Sales[COGS Amount])
Margin = [Net] -[COGS]

Discount % = DIVIDE([Discount],[Gross])
Margin % = divide([Margin],[Net])

• Difference
Functions
• Calculate
• Filter
• Sum
• Min
• Max
• Count
• DistinctCount
Create Visuals
• Table, Matrix
• Filters/Slicers
• Bar, Stacked, Line
• Pie, Donut
• Tree Map, Scatter
• Card, Multi Row Card
• MAPs
• AI Visuals
Expression Functions
• SUMX
• MINX
• MAXX
• COUNTX
DAX
• ALL, AllSelected
• Removefilters
• Allexcept
• Calculate With and Without Filter
EARLIER
• EARLIER
Create Date Table- Standard
• Calendar, Calendarauto
• addcolumns
• EOMONTH
• Weekday
• Weekno
• Year, Quarter, Month
• Startof*
• Endof*
Conditional
• If
• Switch
Text Function
• Search
• Find
• containsstring
• containsstringexact
• Left
• Mid
• Right
• CONCATENATEX
More DAX
• RANKX,TOPN
• Firstnonblankvalue
• Lastnonblankvalue
• Distinct count using
values/summarize
• Isinscope
• hasonevalue
DAX – New Functions
• Offset
• Window
• Index
• Rank
• RowNumber
Table Function
• CalculateTable
• Summarize,Groupby
• Summarizecolumns
• Distinct
• Except
• Generateseries
• Generate,crossjoin
DAX – Time Intelligence
• Dates* function
• Total* functions
• Previous functions
• Week on Week
• Half year
Power BI Features
• Conditional
formatting
• Interactions
• Numeric Parameter
• Bookmarks
• Field Parameters
• Calculation Groups
• Drill Through
Data Transformation
• Pivot Data
• Unpivot Data
• Transform Data
• Fill up , Fill Down
• Append
• Merge
• Split Column
• Calculated Columns
Publish
• Publish Data
• Dataset/Visual File
• Dataflow
• Dashboard
• Create report on Service
• Create report using dataset
Six key points to remember

1. Use Date Table (Join with Fact Date)

2. The date table is marked as a date
table
3. Use columns from the date table
4. The date does not have a timestamp
5. The date table has all dates
6. Avoid bidirectional join with date
table
Understand till Date
1. Assume my Date is 15-Sep-2024.
2. MTD – 1-Sep-2024 to 15-Sep-2024
3. QTD – 1-Jul-2024 to 15-Sep-2024
4. YTD – 1-Jan-2024 to 15-Sep-2024

1. If My Year Start from 1 Apr 2024

st

2. MTD – 1-Sep-2024 to 15-Sep-2024

3. QTD – 1-Jul-2024 to 15-Sep-2024
4. YTD – 1-Apr-2024 to 15-Sep-2024
(FYTD)
ALL and Allselected

 Net All = CALCULATE([Net], ALL())

 Net All = CALCULATE([Net], ALLSELECTED())

 ALL disregard filters on a specific column or table to retrieve

unfiltered data for calculations.
 ALLSELECTED preserves user selections or filters in the specified
column while removing filters from other columns, allowing selective
adjustments.
 In short, All ignores all the filter context including filter and visual
filter row context of given column/table. On the other hand,
allselected ignore only the and visual filter row context.
What is the difference between filtering data
in CALCULATE with and without FILTER
Expression
 Filter Expression without filter function
 CALCULATE([Net], 'Item'[Brand] = "Brand 1")
 Filter Function
 CALCULATE([Net], FILTER('Item', 'Item'[Brand] = "Brand 1"))
 CALCULATE function without a FILTER function, you directly apply filters to specific columns
within the calculation. This modifies the filter context and affects how the expression is
evaluated. CALCULATE without a FILTER expression applies filters directly to columns,
altering the filter context and impacting calculations.
 Using the FILTER function within the CALCULATE function allows you to create a filtered
table based on specific conditions. This offers a more flexible approach to defining the filter
context and customizing how the calculation interacts with the data. CALCULATE with a
FILTER expression constructs a custom filter context by creating a filtered table based on
specified criteria and returns values based on that.
 DAX

Data Analysis Expressions (DAX) is a formula expression

language used in Analysis Services, Power BI, and Power
Pivot in Excel. DAX formulas include functions,
operators, and values to perform advanced calculations
and queries on data in related tables and columns in
tabular data models.

Source: Microsoft Learn - [Link]

 DAX Calculations

DAX formulas are used in measures, calculated columns,

calculated tables, Visual calculations and row-level
security.

Source: Microsoft Learn - [Link]

 DAX Measures

Measures are dynamic calculation formulas where the

results change depending on context. Measures are used
in reporting that support combining and filtering model
data by using multiple attributes such as a Power BI
report or Excel PivotTable or PivotChart. Measures are
created by using the DAX formula bar in the model
designer.

Source: Microsoft Learn - [Link]

 DAX Calculated columns

A calculated column is a column that you add to an

existing table (in the model designer) and then create
a DAX formula that defines the column's values. When a
calculated column contains a valid DAX formula, values
are calculated for each row as soon as the formula is
entered. Values are then stored in the in-memory data
model.

Source: Microsoft Learn - [Link]

 DAX Calculated tables

 A calculated table is a computed object, based on a

formula expression, derived from all or part of other
tables in the same model. Instead of querying and loading
values into your new table's columns from a data source, a
DAX formula defines the table's values.
 Calculated tables support relationships with other tables.
 Calculated tables are re-calculated if any of the tables
it pulls data from are refreshed or updated.

Source: Microsoft Learn - [Link]

 DAX Visual Calculation

 Visual calculation has been recently added to Power

BI. A visual calculation is a DAX calculation that's
defined and executed directly on a visual. Visual
calculations make it easier to create calculations
that were previously hard to create, leading to
simpler DAX, easier maintenance, and better
performance.

Source: Microsoft Learn - [Link]

 DAX Queries

 DAX queries can be created and run in Dax Query view of Power BI
Desktop and open-source tools like DAX Studio ([Link]).
Unlike DAX calculation formulas, which can only be created in
tabular data models, DAX queries can also be run against Analysis
Services Multidimensional models. DAX queries are often easier to
write and more efficient than Multidimensional Data Expressions
(MDX) queries.
 To Run DAX Queries, you need use EVALUATE. One should make sure
that query should return a table.
 You can also use order by in DAX queries, which we usually do not
use in visual explicitly

Source: Microsoft Learn - [Link]

 DAX Data types
 You can import data into a model from many different data
sources that might support different data types. When you
import data into a model, the data is converted to one of the
tabular model data types.
 Whole Number
 Decimal Number
 Boolean
 Text
 Date
 Date/time: Valid dates are all dates after March 1, 1900.
 Currency
 N/A: Blank- A blank is a data type in DAX that represents and replaces SQL nulls. You can
create a blank by using the BLANK function, and test for blanks by using the logical function,
ISBLANK.
Source: Microsoft Learn - [Link]
 DAX Variables
 You can create variables within an expression by using
VAR. VAR is technically not a function, it's a keyword
to store the result of an expression as a named
variable. That variable can then be passed as an
argument to other measure expressions. For example:
 Measure =
 Var _max = max(‘Date’[Date])
 Return
 Calculate(Sum(Sales[Gross Amount]), filter(all(‘Date’), ‘Date’[Date] =
_max)
 Make sure you use return

Source: Microsoft Learn - [Link]

 Context

 Context is an important concept to understand when creating DAX formulas.

Context is what enables you to perform dynamic analysis, as the results of a
formula change to reflect the current row or cell selection and also any
related data. Understanding context and using context effectively are
critical for building high-performing, dynamic analyses, and for
troubleshooting problems in formulas.
 Formulas in tabular models can be evaluated in a different context,
depending on other design elements:
 Filters applied in a PivotTable or report
 Filters defined within a formula
 Relationships specified by using special functions within a formula
 There are different types of context: row context, query context, and filter
context.

Source: Microsoft Learn - [Link]

 Row context
 Row context can be thought of as "the current row". If you create a
formula in a calculated column, the row context for that formula includes
the values from all columns in the current row. If the table is related to
another table, the content also includes all the values from the other
table that are related to the current row.
 For example, suppose you create a calculated column, = [Qty1] + [Qty2],
that adds together values from two columns [Qty1] + [Qty2], from the same
table. This formula automatically gets only the values from the current
row in the specified columns.
 Row context also follows any relationships that have been defined between
tables, including relationships defined within a calculated column by
using DAX formulas, to determine which rows in related tables are
associated with the current row.
 RELATED function can fetch a related table, based on the relation.

Source: Microsoft Learn - [Link]

 Multiple row context
 DAX includes functions that iterate calculations over a table. These
functions can have multiple current rows, each with its own row context.
In essence, these functions let you create formulas that perform
operations recursively over an inner and outer loop.
 For example, suppose your model contains a Products table and a Sales
table. Users might want to go through the entire sales table, which is
full of transactions involving multiple products, and find the largest
quantity ordered for each product in any one transaction.
 With DAX you can build a single formula that returns the correct value,
and the results are automatically updated any time a user adds data to
the tables.
 Minx(FILTER(Sales,[Customer ID] = EARLIER([Customer ID])),Sales[Sales
Date])
 To summarize, the EARLIER function stores the row context from the
operation
Source: Microsoft that
Learn preceded the current operation.
- [Link]
Query context
 Query context refers to the subset of data that is implicitly retrieved for a
formula. For example, when a user places a measure or field into a report, the
engine examines row and column headers, slicers, and report filters to determine
the context. The necessary queries are then run against model data to get the
correct subset of data, make the calculations defined by the formula, and then
populate values in the report.
 Because context changes depending on where you place the formula, the results of
the formula can also change. For example, suppose you create a formula that sums
the values in the Gross Amount column of the Sales table: = SUM('Sales’[Gross
Amount]). If you use this formula in a calculated column within the Sales table,
the results for the formula will be the same for the entire table, because the
query context for the formula is always the entire data set of the Sales table.
Results will have gross Amount for all regions, all products, all years, and so on.
 However, users typically don't want to see the same result hundreds of times, but
instead want to get the profit for a particular year, a particular country, a
particular product, or some combination of these, and then get a grand total.
 In a report, context is changed by filtering, adding or removing fields, and using
slicers. For each change, the query context in which the measure is evaluated.
Therefore, the same formula, used in a measure, is evaluated in a different query
Source: Microsoft
context for Learn - [Link]
each cell.
 Filter context
 Filter context is the set of values allowed in each column, or in the values
retrieved from a related table. Filters can be applied to the column in the
designer, or in the presentation layer (reports and PivotTables). Filters can
also be defined explicitly by filter expressions within the formula.
 Filter context is added when you specify filter constraints on the set of
values allowed in a column or table, by using arguments to a formula. Filter
context applies on top of other contexts, such as row context or query
context.
 In tabular models, there are many ways to create filter context. Within the
context of clients that can consume the model, such as Power BI reports,
users can create filters on the fly by adding slicers or report filters on
the row and column headings. You can also specify filter expressions directly
within the formula, to specify related values, to filter tables that are used
as inputs, or to dynamically get context for the values that are used in
calculations. You can also completely clear or selectively clear the filters
on particular columns. This is very useful when creating formulas that
calculate grand totals.
Source: Microsoft Learn - [Link]
 DAX

 Data Analysis Expressions (DAX) is a formula

expression language used in Analysis Services, Power
BI, and Power Pivot in Excel. DAX formulas include
functions, operators, and values to perform advanced
calculations and queries on data in related tables and
columns in tabular data models.

Source: Microsoft Learn - [Link]

Row Context
1. Physical Row of Table
I. In a calculated Column
2. iterator like FILTER, SUMX,
AVERAGEX, ADDCOLUMNS
3. Applies to one row at time

Filter Context
1. Slicer, Filter
2. Visual, DAX
3. Applies to Set of row
Filter Context Usage
1. Slicer, Filter – Used as Filter of Filter
context
2. Visual – Used as Context, Filter
context, Visual row filter context, row
filter context
3. DAX- Used filter for formula filter
context or context
What is the difference between filtering data
in CALCULATE with and without FILTER
Expression
 Filter Expression without filter function
 CALCULATE([Net], 'Item'[Brand] = "Brand 1")
 Filter Function
 CALCULATE([Net], FILTER('Item', 'Item'[Brand] = "Brand 1"))
 CALCULATE function without a FILTER function, you directly apply filters to specific columns
within the calculation. This modifies the filter context and affects how the expression is
evaluated. CALCULATE without a FILTER expression applies filters directly to columns,
altering the filter context and impacting calculations.
 Using the FILTER function within the CALCULATE function allows you to create a filtered
table based on specific conditions. This offers a more flexible approach to defining the filter
context and customizing how the calculation interacts with the data. CALCULATE with a
FILTER expression constructs a custom filter context by creating a filtered table based on
specified criteria and returns values based on that.
Similar to SQL Window Functions

 Dec - 2022
 Offset
 Window
 Index

 April -2023
 Rank
 RowNumber
Key Highlights

These DAX functions are similar to SQL window

functions, performing calculations across related table
rows based on the DAX evaluation context. Unlike SQL
window functions, these DAX functions do not return a
single value but a set of rows, which can be used with
functions like CALCULATE or SUMX to compute a value.
These functions execute in the DAX engine rather than
being pushed to the data source, offering improved
performance, particularly when sorting by non-
continuous columns.
Key Highlights

 OrderBy
 Partitionby
 Axis
 Reset
 What is Power Query?

Power Query is a data transformation and data preparation

engine. Power Query comes with a graphical interface for
getting data from sources and a Power Query Editor for
applying transformations. Because the engine is available
in many products and services, the destination where the
data will be stored depends on where Power Query was
used. Using Power Query, you can perform the extract,
transform, and load (ETL) processing of data.

Source: Microsoft Learn - [Link]

 Power Query Experience
The Power Query user experience is provided through the Power
Query Editor user interface. The goal of this interface is to help
you apply the transformations you need simply by interacting with
a user-friendly set of ribbons, menus, buttons, and other
interactive components.

Power Query Online— Found in integrations such as Power BI

dataflows, Microsoft Fabric Dataflow Gen 2, Microsoft Power
Platform dataflows, Azure Data Factory wrangling dataflows, and
many more that provide the experience through an online webpage.
Power Query for Desktop—Found in integrations such as Power Query
for Excel and Power BI Desktop.
Source: Microsoft Learn - [Link]
 Power Query M formula language
The Power Query engine uses a scripting language behind the scenes
for all Power Query transformations: the Power Query M formula
language, also known as M.

The M language is the data transformation language of Power Query.

Anything that happens in the query is ultimately written in M. If
you want to do advanced transformations using the Power Query
engine, you can use the Advanced Editor to access the script of
the query and modify it as you want. If you find that the user
interface functions and transformations won't perform the exact
changes you need, use the Advanced Editor and the M language to
fine-tune your functions and transformations.

Source: Microsoft Learn - [Link]

What is M
M is a case-sensitive language. Used in Power Query in case of
Power BI

To create an M query in the Query Editor, you follow this basic

process:
1. Create a series of query formula steps that start with the
let statement. Each step is defined by a step variable name. An
M variable can include spaces by using the # character as
#"Step Name". A formula step can be a custom formula. Please
note that the Power Query Formula Language is case sensitive.
2. Each query formula step builds upon a previous step by
referring to a step by its variable name.
3. Output a query formula step using the in statement.
Source:Generally, the
Microsoft Learn last query step is used as the in final data set
- [Link]
result.
 Example Code
let
Variablename = expression,
#"Variable name" = expression2
in
Variablename

Source: Microsoft Learn - [Link]

 Primitive value
A primitive value is single-part value, such as a number, logical,
text, or null. A null value can be used to indicate the absence of
any data

Type Example value

Binary 00 00 00 02 // number of points (2)
Date 5/23/2015
DateTime 5/23/2015 [Link] AM
DateTimeZone 5/23/2015 [Link] AM -08:00
Duration [Link]
Logical true and false
Null null
Number 0, 1, -1, 1.5, and 2.3e-5
Text "abc"
Time [Link] PM
Source: Microsoft Learn - [Link]
 Function
A Function is a value that, when invoked with arguments, produces
a new value. Functions are written by listing the function’s
parameters in parentheses, followed by the goes-to symbol =>,
followed by the expression defining the function. For example, to
create a function called “MyFunction” that has two parameters and
performs a calculation on parameter1 and parameter2:

let
MyFunction = (parameter1, parameter2) => (parameter1 + parameter2) / 2
in
MyFunction

Source: Microsoft Learn - [Link]

 Structured data values

List
Record
Table
Additional structured data examples

Source: Microsoft Learn - [Link]