B.

Sc (ECS)-II | Data Visualization Unit - 1

 Data Cleansing:
Viewing a Full Record, Changing Data Type , Detecting Data Types, Replacing Values,
Transforming Column Contents, Filling Down, Using the First Row As Headers, Grouping
Records, Extending Data, Appending Data, Merging Data- Adding Data, Aggregating Data During
a Merge Operation, Extending the Data Model with Calculated Columns, Creating Custom
Columns, Index Columns, Types of Join- Joining on Multiple Columns, Preparing Datasets for
Joins, Correct and Incorrect Joins, Examining Joined Data, The Expand and Aggregate Buttons.
 Power Query Editor –
What is DAX, Different type of DAX functions-Aggregate functions, Date functions, Logical
functions, Math functions, String functions, Trigonometric functions and other functions.
 Adding Measures to the Data Model:
Basic Aggregations in Measures, Using Multiple Measures, Cross-Table Measures, More
Advanced Aggregations, Filtering Data in Measures, Analyzing Data over Time.
 Data Visualizations
Charts in Power BI-Types of charts, Maps in Power BI, Table and Matrix in Power BI, Subtotal and
Total in Matrix, Cards and Filters in Power BI, Conditional Formatting, Slicers in Power BI- slicers,
adding a Slicer, Applying Slicers, clearing a Slicer, deleting a Slicer, modifying a Slicer, Formatting
Slicers-Slicer Orientation, Modifying the Outline, Adjusting Selection Controls, Setting the Exact
Size and X and Y coordinates of a Slicer, Slicer Header, Slicer Items Designing Power BI
Dashboards and Reports Dashboards, reports, Dashboards versus reports, Dashboard design- What
is KPI, When to use KPI, Requirements for KPI, KPI Visualizations, Visual selection, Layout,
Navigation pane, Full screen mode, Supporting tiles, Custom date filters, Single dashboard,
Multiple-dashboard, Organizational dashboards, Multiple datasets
 Dashboard tiles-
Tile details and custom links, Images and text boxes, SQL Server Reporting Services.
 Deploying the Power BI Report Server-
Live Dashboard pages, Live report pages, Mobile-optimized dashboards Case study – Superstore,
IPL Analysis, Product Sales Data Analysis, Marketing Campaign Insights Analysis, Financial
Performance Analysis, Loan Application Analysis
******************************************************************************
 Data cleaning is a process of transforming and preparing raw data into a clean, structured format
for analysis.
 Power BI that enables you to import, clean, and transform data from various sources.
Viewing a Full Record
 Before starting to clean a data, you need to display full records.
 Power BI Desktop Query Editor is scrolling up and down columns to view complete data.
 It is not easy to view an entire contents of a single record. So avoid scrolling across rows of data.
 If you click a row the Power BI Desktop Query Editor will display the contents of an entire record
in a single window under the dataset.

1|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1

Detecting Data Types:

 In Power BI Desktop, automatically detect data types of the column.
 This feature can be turned on and off.
 In Power BI, go to File->Options and Settings->Options->Select Data Load, and under Type
Detection you will see Automatically detect column types and headers for unstructured sources:

Transforming Column Contents

 Data transformations are preparing and shaping your data into standard format for analysis and
visualization.
 Power BI Desktop has a powerful toolbox of automated data transformations that allow you to
standardize the contents of a column.
• Setting the capitalization of text columns
• Rounding numeric data or applying math functions
• Extracting date elements such as the year, month, or day (among others) from a date column
 Transforms are totally dependent on the data type of the selected column
1. Text Transformations
 Text transformations allow users to manipulate and format text data according to their analysis
requirements and extract valuable insights and drive data-driven decision making.
 It is essential for businesses to efficiently analyze and interpret their data.
 Text transformations in Power BI:
 Splitting and merging: Splitting a column into multiple columns or merging multiple columns
into one.
2|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1
 Trimming and padding: Removing extra spaces at the beginning or end of a string or adding
spaces to align text.
 Changing case: To convert text to uppercase, lowercase, or proper case.
 Extracting substrings: To extract specific parts of a text string using text functions.
 Replacing values: To replace specific text values with other values or remove them.

Transformation Description
Lowercase Converts all the text to lowercase.
Uppercase Converts all the text to uppercase.
Capitalize Each Word Converts the first letter of each word to a capital.
Trim Removes all spaces before and after the text.
Clean Removes any nonprintable characters.
2. Number Transformations
 Number transformations manipulating numerical values to perform calculations, conversions, or
formatting adjustments.
 To perform number transformations:
 Round and Truncate: Use functions like ROUND, TRUNC, or INT to round or truncate
decimal places.
 Convert Data Types: Transform numbers from one data type to another using functions like
INT, FLOAT, or DECIMAL.
 Calculate Percentage: Use the DIVIDE function to calculate percentages based on numerical
values.
 Apply Mathematical Operations: Use functions like SUM, AVERAGE, MAX, or MIN to
perform mathematical operations on numeric data.
 Format Numbers: Use formatting functions like FORMAT or TEXT to adjust the display
format of numbers.

3. Date/Time Transformations
 Date/Time transformations are effectively manipulating and analyzing temporal data.
 Date/time transformations in Power BI:
3|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1
 Extract specific components: Extract the year, month, day, hour, minute, or second from a
date/time column.
 Create date hierarchies: Generate hierarchies such as year > quarter > month > day to
facilitate drill-down analysis.
 Calculate time-based metrics: Calculate the duration, age, or time differences between two
dates.
 Convert data types: Convert text or numeric values to the date/time format and vice versa.
 Apply conditional formatting: Highlight dates based on specific criteria, such as highlighting
overdue dates in red.
 Handle time zones: Adjust dates to different time zones or convert them to a standardized
time zone.

Filling Down:
 When dataset contains blank (null) cells or rows and we want to fill the value from the below or
above cell.
 FILL operation allows to fill up and fill down to replace null values with the last non-empty value
in a column. i.e. By selecting Fill Down on a particular column, a value will replace all Null values.
 It is a quick and easy way that fill all of null or empty rows.

Using the First Row As Headers:

 The headers are showing as Column1, Column2, and Column3. It’s not meaningful.
 To eliminate them and replace with the first row’s text. i.e. Promote the first row as column headers.
 Go to Transform > Use First Row As Headers option

4|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1

 Under Power Query Editor > Click on dataset table icon as shown in below image > then select “Use
First Row as Headers”.
Extending Data Model:
 Extending data means adding more columns into table. These will be derived from existing columns
of data. i.e. Adding custom columns maybe contain calculations or extract part of a column’s data
into a new column
 Example- To extract years or months from a date column into separate columns.
 You can create calculated columns based on existing data.
• Duplicating columns and altering format of the data in the copied column.
• Extracting part of the data into a new column.
• Analyzing the data in a column so that each data element appears in a separate column.
• Merging columns into a new column.
1. Calculated Columns:
 If you want to add a new column to the table that is derived from the other column.
 Calculated columns performs calculation based on existing data and generate new data.
 Example: Calculate Total Sale for each row by multiplying the sales price and quantity, and
subtracting the discount.
 Calculated columns are beneficial for creating new metrics or dimensions that may not be present
in the original data.
 Calculated columns are suitable for static calculations, where the calculated value remains
constant regardless of the context or filters applied to the data model.
 A calculated column is evaluated row by row in the table, and the result is stored in memory.

 It will open up a new column section in the “Formula Tab.”

Rename column before we apply the DAX expression formula. Give the name as “Total Sales” and
enter the formula.

5|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1

2. Measures
 Measures are used for aggregating values from various rows in a table instead of calculating the
values for each row.
 A measure is a calculation or aggregation performed on a dataset to derive meaningful insights.
 Measures can be helpful for summarizing important data, performing calculations and creating
complex metrics.
 Measures are perform complex calculations and develop key performance indicators (KPIs) on
multiple tables or columns. These measures require the use of the DAX functions.
 Measures are not stored in the data model and in the memory as new columns.
 They are calculated at runtime based on the data slice or filters applied to the visual or report.
 It can perform arithmetic calculations, ratios, percentages, and aggregate functions like SUM,
AVERAGE, and COUNT.
 Example: If your data doesn’t have the profit column, use calculated measure generate from sales
and product cost.
 Right-click on the table and choose “New measure.”
Formula Bar: Measure name = Aggregation function(‘tablename'[column name])

To get the overall sales value against all the cities.

6|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1
SUM function, choose the “Total Sales” column and second argument is Filter 1 for this
open ALL function, select the “City” column.
 To calculate each city’s contribution percentage. Enter the new measure column and name
it “Sales %.”

 Building a table to show each city’s sales percentage.

Calculated Column Calculated Measure

Not efficient for aggregated calculations Efficient for aggregated calculations
Easy to create and manage Slightly more complex to create
Calculations are performed Vertically Calculations are performed Horizontally
Stored in the table and consume memory Measures are not stored in the table and memory
Row-by-row calculation. Aggregated over a table
In tables, charts, slicers, etc. Mainly in charts and visuals
Used when the size of the model is Used when the size of the model is
comparatively smaller. comparatively larger.
Increases model size Minimal
Ex: Deriving the profit of a product by Ex: Deriving the total profit for all the products.
subtracting the product’s cost from the sales
price of the product.
3. Creating Custom Columns:
 They allow you to create new columns based on existing ones, perform calculations, apply logical
conditions, extract specific information, and much more.
 To add additional data columns to the original table. Custom columns also known as derived
columns or calculated columns.
 Custom columns give the freedom to shape your data.
• Concatenate existing columns.
• Add calculations to the data table.
• Extract a specific part of a column.
• Add flags to the table based on existing data.
 Click on Add column tab and Select Custom column option.

7|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1
 Once you click on Custom column tab, a Custom column window opens List of available
columns on the right, these are the columns that you have already in your data model. You can
create a custom columns using these existing columns also.

 Now we will add a new column Cust_FullName in data model. So basically it will be a
concatenation of two columns FirstName and LastName.

 If there are no errors in written formula, you will see a green check mark and the message No
syntax errors have been detected.
 In case, if there are a syntax error, you will see a yellow warning icon along with a link to where
the error occurred in your formula bar.
 Once you click on OK button, Power BI Desktop adds your custom column to the data model.

Create custom column to get the aging, a date difference in days between Delivery date and Order
date. i.e. Waiting_Period(DeliveryDate – Order Date).
 To get the date difference in days, a query function Duration.Days.
 It auto suggests you a list of available columns and M queries functions based on your query, now
select Duration.Days from lists.

8|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1

Now click on OK button, and you can see a newly custom column is added to the data model.
It displays the aging a date difference between Delivery date and Order date in days.

Modifying custom column

In case, if you want to modify, rename custom column. Double click on custom column.

Once you click on OK button, you will see a column name is renamed as Waiting_Period.
4. Index Columns
 An index column is an additional column that assigns a unique number or value to each row.
 This index column can be used to filtering, sorting, creating hierarchies and group your data,
which is useful to analyze and visualize the insights.
 It is used for large datasets or datasets with multiple columns.
 The index column will help you quickly retrieve or reference specific data points, which can save
you a lot of time and effort.
 Index columns can be created starting with:
o From 0 – Starts the new index of rows from Zero
o From 1 – Starts the new index of rows from One
o Custom – To set index starting number and increment.

9|SHK
B.Sc (ECS)-II | Data Visualization Unit - 1
 Custom Index
 Click on “Add Column” > “Index Column” > choose “Custom Index.”
 The “Add Index Column” dialogue box appears. Starting index number as 1 and want it to be
incremented by 2. i.e. each next rows will have an index number that is incremented by 2 to the
index number of previous row.

Appending Data:
 If you have data in different tables or files with similar structures, you want to append them together
to create a single large dataset. i.e. To append additional rows of data to an existing query
 Append operations join two or more tables vertically.
 Data appending can help you identify any missing or incomplete data in your existing dataset,
allowing you to fill in the gaps and create a more comprehensive analysis.
 Appending data of two data sources should be have same identical structures:
• They have the same number of columns
• The columns are in the same order
• The data types are identical for each column
• The columns have the same names
You can append data if above condition is satisfied.
 The data rows from one table are appended (or added) at the end of the data rows in another table
where the column name, data type or values match.
 Click on “Home” Tab, then Click on “Append Queries” in the Combine section.
 After that Click on “Append Queries as New”.

You can also choose to append Three or more tables and add tables to the list.
10 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

 Adding Multiple Files from a Source Folder

 You will get data or import data from a folder that contains multiple files. If data is different but
the Structure of the table should be the same from multiple files.

Merging Data- Adding Data, Aggregating Data During a Merge Operation

 Merging means to add data columns from one table to another. This is useful when you have two
tables with different column, but you want to combine them based on a common attribute.
 Merge operations join multiple datasets or tables horizontally based on common column between the
tables.
 To combine data by matching values in one or more common columns. The merged queries should
have at least one column in common.
Merge Queries Used to merge two tables and does not
create a new.
Merge Queries Used to merge two or more tables and
as New create a new one.

In Home Tab, Select Marge Queries Menu.

11 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

Aggregating Data during a Merge Operation

Data Merging with aggregate function that will perform on merged data.

Merge queries Append queries

Combine tables horizontally Combine tables vertically
Require at least one matching column in each table Require matching columns with the same
data types
Combine tables with different numbers of columns Require tables to have the same number of
columns
Used to create new columns based on the matching Append queries do not create new columns
values
Types of Join-
 Joins can be used to compare or combine rows between tables. They compare tables based on one or
more related columns.
 When the compared row values between two tables are identical, they are considered a match.

12 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

1. Left Outer Join: Returns all rows from the left table and matching rows from the right table.

 We selected the agt_code from Agent and Order table as the common fields.
 A new table called Merge1 with all the fields from the Agent table along with one extra filed
of values table

 Click on the right corner of the Order column header. This will open the list of field that will
select the required fields from the Order table.

 Select the ord_no, amount, cust_code, ord_date from the order table, and click OK
13 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

2. Right Outer Join: returns all rows from the right table and matching rows from the left table.

3. Full Outer Join: returns all matching and non-matching rows from both tables.

14 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Select the Agent table as the first table, Order table as the second table. Here, we select agt_code
is the common filed, and full outer join as the join.

4. Inner Join: Return only matching rows from both tables.

5. Left Anti Join: Return all rows from left table that do not have a match on right table.

15 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

6. Right Anti Join: Return all rows from right table that do not have a match on left table.

Joining on Multiple Columns:

 You can join tables with multiple columns conditions, under merge queries you can perform with all
joins
 To combine multiple columns into one using power query editor.
o If you select “UserDetail (customer)” with “UserAmount (User)” and “UserDetail (City)” with
“UserAmount (City),” then the column selection sequence must be consistent.
o Example: if you choose “Customer” first and then “City” for the first table, you should follow
the same sequence for the second table by selecting “User” first and then “City.”
o This consistent column selection sequence is crucial because Power Query relies on this
sequence to create the join condition and retrieve the data accurately based on your selections.

16 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
o A new dataset has been created. Now, click on the expand icon, and select the column you
want to add to the table. Then, click on OK.

Now you can view the final output of the join with multiple column conditions.

Preparing Datasets for Joins,

 You need to join text-based columns to other text columns, number columns to numbers and dates to
dates.
 If the columns are not the same data type, you receive a warning message when you try to join the
columns in the Merge dialog.
• Removing trailing or leading spaces in text-based columns
• Isolating part of a column (either in the original column or as a new column) to use in a join
• Verifying that appropriate data types are used in join columns

Correct and Incorrect Joins

You could face several major issues:
• The columns do not map
• The columns map, but the result is a massive table with duplicate records

17 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Power Query Editor –
What is DAX, Different type of DAX functions-Aggregate functions, Date functions, Logical
functions, Math functions, String functions, Trigonometric functions and other functions.
**********************************************************************************
What is DAX
 Data Analysis Expressions (DAX) are the collection of formulas, functions, operators, and constants
that allows a user to create measures, dimensions, and custom tables.
 They return one or more values and are used to solve data analysis problems, creating a new
relationship between different data variables.
 DAX is very useful to perform advanced calculations and discover a hidden pattern in an
unstructured dataset.
 DAX are used to create interactive reports and dashboards that help them to see patterns and trends in
the data
 The complete code of an expression is always a function or nested function with conditional
statements, value references, formulas, loops, etc.
 They are evaluated from the innermost to the outermost function of the expressions.
 It is developed by Microsoft to interact with its business intelligence and data modeling tools like
PowerPivot, Power BI.
 DAX works on column values.
 It cannot modify or insert data.
 Helps to create calculated columns and measures with DAX.
 DAX works on a combination of three fundamentals:
o Syntax
o Context
o Functions
1. Syntax
It is the language used in the formula like the command, sign, operators, destination column or row or
table, etc. For example, name, parenthesis, summation, name of the table, etc.
2. Context
Context refers to the target row that has been included in the formula for data retrieval or calculation.
Context is present in two types: Row Context and Filter Context.
3. Functions
Functions refer to the predefined or already existing commands in the system. For example, Sum,
Add, True, False, etc.
 Data Analysis Expression (DAX) is a formula-based language
Syntax

A) Total Sales= Name of measure or column

B) Equal Sign Operator = Beginning of the DAX formula
C) SUM = DAX Function adds up all of the numbers in the column
D) Parentheses () = functions or expression containing one or more arguments.
E) Sales = Table name
F) SalesAmount = Column name
 DAX can be used to two different types of expressions and calculations like Calculated columns and
Calculated measures
 Data Analysis Expressions (DAX) is a powerful formula language used in Power BI for data
modeling, calculations, and analysis. Here are some common uses of Power BI DAX:
18 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Data visualization
 Filtering and Slicing
 Hierarchies and Relationships
 Hierarchies and Relationships
 Dynamic Reporting
 Advanced Aggregations

Different type of DAX functions

 Aggregate Functions: DAX has a number of aggregate functions like SUM, AVERAGE, MIN,
MAX, SUMX, AVERAGEX, MINX, and MAXX.
 Counting Functions: DAX has a counting function like DISTINCT COUNT, COUNT,
COUNTA, COUNTROWS, COUNTBLANK
 Logical Functions: DAX has a logical function like AND, OR, NOT, IF, IFERROR, TRUE,
FALSE, IF, AND, OR, NOT, SWITCH, and IFERROR.
 TEXT Functions: CONCATENATE, UPPER, LOWER, SUBSTITUTE, TRIM, SEARCH, FIND,
LEFT, and RIGHT.
 Date Functions: DATE, HOUR, WEEKDAY, NOW, EOMONTH
 Information Functions: DAX has an information function like ISBLANK, ISNUMBER,
ISTEXT, ISNONTEXT, ISERROR
 Parent-Child Functions: Perform data managing and presenting in a hierarchical order, it includes
operations like PATH, PATH CONTAINS, and PATH LENGTH.
 Aggregate Functions
 Aggregate functions are used to calculate a set of values within a column or a table and return a
single value as aggregated result.
 These functions are essential for summarizing and analyzing data, especially with large datasets.
 For Text Data type column it will return sum, min, max Alphabet Text.
Example: sum to calculate the total sales for each product
Function Description Example
Returns the sum numeric value of # Calculate the total sold Qty.
SUM(column)
given column. Total_Qty=SUM(Orders[Quantity])
Returns the minimum numeric # Find the lowest Product Cost
MIN(column)
value from a column. Min_Sales=MIN ('Orders'[Product Cost])
Returns the minimum value in a
column, including any logical MINA_With_Date = MINA(Orders[Order
MINA(column)
values, date, and numbers Date])
represented as text.
# Find the highest Product Cost
Returns the maximum numeric
MAX(column) Max_Sales=MAX(Orders[Product Cost])
value from a column.
Compare_Col=MAX([Total_Sales],[Profit])

19 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Returns the largest value in a
MAXA(<Column>) MAXA_With_Date = MAXA
column including date, text or
(Orders[Order Date])
Boolean values.
# calculates the average Product Cost
Calculates the average numeric
AVERAGE(column) Average_Sales=AVERAGE (Orders[Product
value of a column in a table.
Cost])
Calculates the average value of a
AvgA=AVERAGEA(Orders,
AVERAGEA(column) column in a table, including any
Orders[Product Cost])
text or logical values.

 Iteration Aggregate Functions:

 Iteration functions are used to iterate over a table and perform calculations on each row of the table
based on some criteria.
 These functions are used to perform calculations such as running totals and moving averages.
MINX(table_name, Evaluates an expression for each
MINX_With_Text = MINX(Orders,
expression) row of a table and returns the
Orders[state])
smallest value.
MAXX(table_name, Evaluates an expression for each
Max_Sales_Day=MAXX( Orders,
expression) row of a table and returns the
Orders[Order_Date])
largest value.
SUMX(table_name, Returns the sum of an expression Total Revenue = SUMX(Orders,
expression) evaluated for each row in a table. Sales[Quantity] * Orders[Selling_Price])
Calculates the average of an
AVERAGEX(table,
expression evaluated for each AvgX=AVERAGEX(Orders[Product Cost])
expression)
row in a table.

 Counting Functions
 It is used to count the number of rows in a table that meet a specific condition.
 Count function can also be used with filters.
Function Description Example
Counts the number of rows
COUNTROWS(table) Countrows_Order=COUNTROWS(Orders)
in a table.
Counts the number of non-
COUNT(column) # To count total number of orders
empty rows from a column
#count repeated values Count_Order=COUNT(Orders[Order_Id])
including number, text, date
Count the number of unique
values exist in a column.
Dist_Count_Order =
DISTINCTCOUNT(column) Return incorrect output if a
DISTINCTCOUNT(Orders[Order_Id])
null value is present in the
column.

20 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
# To count total number of Products
Counts all the non-empty
Total_orders=COUNTA(Orders[Product])
COUNTA(column) rows in a given column
Count both numeric and non-numeric
including Boolean values
values.
COUNTX(table, expression) Counts the number of non-
COUNTX_Order=COUNTX(Orders,
# Not applicable to Boolean empty rows when evaluating
Orders[Order_Id])
values. an expression in a table.
Count number of null and Countblank_Order =
COUNTBLANK(column)
blank cells in the column. COUNTBLANK(Orders[Qty])
Counts non-empty results Countax_qty=COUNTAX(Orders,
COUNTAX(table,
when evaluating the result of Orders[Qty])
expression)
an expression over a table. Does not count empty or blank values.

 Logical Functions
Logical functions are used to evaluate an expression or argument logically and return TRUE or
FALSE if the condition is satisfy or not.
Function Description
Checks whether both arguments are TRUE then returns TRUE
AND(Condition 1, Condition 2) Otherwise returns false.
Condition 1- that you want to check Ex: AndSales=IF(AND(Orders[Sales]>AVERAGE(Orders[Sales]),
Condition 2- Also you want to check Orders[Sales] >= 7000), "Good", "Bad")
Create a series of calculations or use the AND(&&) operator.
Checks if at least one condition is true then returns true, otherwise
OR(Condition 1, Condition 2) returns false.
Ex: OrSales=IF(OR(Orders[Sales]<AVERAGE(Orders[Sales]),
Orders[Sales] >= 9000), "Watchlist", "Doing Good")
Checks whether all logical conditions are true, then it returns true
otherwise, it returns false.
IF(logical test, Result_If_True,
Ex: Order_size=IF(Orders[Qty]>100,"Big Order","Small Order")
Result_If_False)
NestedIf = IF(Orders[Sales]<1000, "Very Bad",
IF(Orders[Sales] >3000, "Good", "Average"))
Returns value_if_error if the first expression is an error and the
IFERROR(Calculation,
value of the expression itself otherwise.
Value_If_Error)
Ex: ErrorSale = IFERROR(Orders[Sales]/0, 100)
Negates the conditions and to changes FALSE to TRUE, or TRUE
NOT(Condition)
to FALSE.
Ex: NotSale=NOT(IF(Orders[Sales] >2000, "TRUE", "FALSE")
Evaluates an expression against multiple values and returns the
SWITCH(expression, value1, corresponding result.
result1, value2, result2, ..., default Ex: SwitchMonth=SWITCH(MONTH(Orders[Order Date]), 1,
result) "January", 2, "February", 3, "March", 4, "April", 5, "May",
12, "December", "Unknown")
Returns the logical value TRUE.
TRUE()
Truecity=IF(Order[city]=”MUMBAI”, TRUE(),FALSE())
Returns the logical value FALSE.
FALSE()
Ex: FalseSale = IF(Orders[Sales] < 2500, FALSE(), TRUE() )
[Column] IN {field1, Restricts the calculation to specified columns.
field2,...,fieldN} Ex: SalesIN=CALCULATE(SUM(Orders[State]) ,
Orders[State] IN {"Maharashtra", "Punjab", "Delhi"})
21 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Text Functions
These functions evaluate string values.
Function Description
Replaces a specified number of characters in a string with new text.
REPLACE(old text, start
Ex: ReplaceSalary=REPLACE(Emp[Salary],2,3,999)
num, num chars, new_text)
Replaces the characters from 2nd character to 3 characters.
Searches a substring within a string and returns its starting position.
SEARCH(find text, within
Ex: Search_char_position = SEARCH(“s”,Emp[Emp_Name], ,-1)
text, [start num])
If the character is not found within the string, it will return -1.
Converts all characters to uppercase.
UPPER(Column_name)
Ex: UPPER_Name=UPPER(Emp[Emp_Name])
Converts all the characters to lowercase.
LOWER(Column_name)
Ex: LOWER_Name=LOWER(Emp[Emp_Name])
Capitalizing the first letter of each word
PROPER(Column_name)
Ex: PROPER_Name=PROPER(Emp[Emp_Name])
Returns the length of a given string.
LEN(text)
Ex: LEN_name= LEN(Emp[Emp_Name])
Combines two or more columns or strings into a single string.
CONCATENATE(str1, str2)
Ex: Full_Name=CONCATENATE(Emp[FirstName], " ", Emp
[LastName])
SUBSTITUTE(string, old_str, Replaces the string with the user-specified substring.
new_str) Ex:SubstName=SUBSTITUTE(Emp[Emp_Name],”Abhinav”,”Abhi”)
Compares two strings and returns true if they are exactly equal;
otherwise, it returns false.
EXACT(str1, str2)
Ex: Comp_dept= EXACT(Emp[Dept_Name], LEFT(Emp
[Dept_Name],18))
Removes the extra spaces from left, right and in between of string
TRIM
Ex: TrimEmpName= TRIM(Emp[Emp_Name])
Find the position of string within another string.
FIND
Ex: Find_char=FIND(“s”,Emp[Emp_Name], ,-1)
LEFT(string, position) Extract a specified number of characters from the left side of a string
Ex: LEFT_name= LEFT(Emp[Emp_Name],3)
Extract a specified number of characters from the right side of a string
RIGHT(string, position)
Ex: RIGHT_name= RIGHT(Emp[Emp_Name],4)
MID(string, starting_position, Returns a substring from the original string.
length) Ex: MID_Name= MID(Emp[Emp_Name], 4, 7)
 Starting position- Substring start
Substring starts at position no 4 and ends when string length reaches
from this position
 Length- Total length of substring.
to 7.
A value is transformed into text using the provided format.
FORMAT(Value,Format_Str)
Ex: Date_Format=FORMAT(Emp[DOJ], “MM-DD-YYYY”)
Returns Blank (If the condition is true).
BLANK ( ) Ex: If(LEN(Emp [Emp_Name]=0, BLANK( ), Emp[Salary] / Emp
[Emp_Name])
 Date Functions
The date time functions carry out calculations on the date and time values.
Function Description Example
Returns the current date with default
TODAY( ) Current_date = TODAY()
system time
Returns the current date and time in
NOW( ) Current_DateTime=NOW()
Date-Time format.
Returns the day of the month, a
DAY(DATE) DayinDate=DAY(Emp[Joining_date])
number from 1 to 31.

22 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Returns the month as a number from
MONTH(DATE) MonthinDate=MONTH(Emp[Joining_date])
1 (January) to 12 (December).
Returns the year of a date as a four-
YEAR(DATE) YearinDate=YEAR(Emp[Joining_date])
digit integer in the range 2000–2099.
WEEKDAY(Date, Returns numbers from 1 to 7, where WeekDay=WEEKDAY(Emp[Joining_Date],
Number) 1 as Sunday and 7 as Monday 1)
WEEKNUM(date, Return week number (1 as January WeekNum=WEEKNUM(Emp[Joining_Date]
[week_start_num]) first week). , 1)
DATEADD(start Add_date=DATEADD(Orders[Order_Date],
Returns a date increased or decreased
date, number of 7,DAY)
by a specified time interval.
intervals, interval) # OrderDate of Sales table increased by 7 days.
DATEDIFF(start Calculates the difference between
Diff_date=DATEDIFF(Sales[Delivery_Date
date, end date, two dates based on the specified
], Sales[OrderDate], DAY)
interval) interval (days, months, etc.).
EDATE(Start_Date, Returns previous dates or future
Edate=EDATE(Emp[Joining_Date], 2)
Months) dates.
EOMONTH Eomonth=EOMONTH(Emp[Joining_Date],
Display the last day in a month
(Start_Date, Months) 3)
Create a calendar table that includes Generate a calendar table named
a continuous range of dates. “Cal_Table” that includes all the dates from
CALENDAR
It generates a table with a list of January 1, 2023, to December 31, 2023.
(start_date,end_date)
dates to support time-based Cal_Table = CALENDAR( DATE(2023, 1,
calculations and analysis. 1), DATE(2023, 12, 31))
 Time Functions
The DAX Time function is used to return time from a specified hour, minute, and seconds.
Function Description Example
Returns the specified hour as a
HOUR(DATE) number from 0 to 23 (12:00 A.M. to Hours=HOUR(Emp([Joining_date])
11:00 P.M.).
Returns the minute as a number from
MINUTE(DATE) Minutes=MINUTE(Emp([Joining_date])
0 to 59, given a date and time value.
Returns the seconds of a time value,
SECOND(DATE) Seconds=SECOND(Emp([Joining_date])
as a number from 0 to 59.
 Information Functions
These functions are used to provide certain information on the data values contained in rows and
columns. It evaluates the given condition in a function for the value given and return TRUE or
FALSE.
Function Description Example
Checks whether a value is blank, Empty_qty=ISBLANK(Orders[Qty])
ISBLANK(value)
and returns TRUE or FALSE.
Returns TRUE if the specified Empty_table=ISEMPTY(Orders)
ISEMPTY(Table)
table or table-expression is empty.
Checks whether a value is a logical
ISLOGICAL(value) value, (TRUE or FALSE), and
returns TRUE or FALSE.
Checks whether a value is not text Non_txt=IF(ISNONTEXT(Orders[qty]),
ISNONTEXT(value) (blank cells are not text), and "Is Text", "Is Non-Text")
returns TRUE or FALSE.
Checks whether a value is a Num=IF(ISNUMBER(Orders[qty]),"Is
ISNUMBER(value) number, and returns TRUE or number", "Is Not number")
FALSE.

23 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Checks whether a value is text, txt=IF(ISTEXT(Orders[qty]), "Is Text",
ISTEXT(value)
and returns TRUE or FALSE. "Is Non-Text")
Measures_Contains_str =
CONTAINS (<table>, Returns TRUE if there exists at
CONTAINS(Info, Info[Ontime
<column value> <value> least one row where all columns
Delivery],"Yes", Info[Products
[<column value>, <value>]) have specified values.
Returned],"Yes")
Returns TRUE if there exists at
CONTAINSROW (Table,
least one row where all columns
Value1, …)
have specified values.
Depending on whether one string measures_Containsstring =
CONTAINSSTRING
contains another, this function CONTAINSSTRING(SELECTEDVAL
(<within text>, <find text>)
returns true or false. UE(Info[Furniture]),"Sofas")
Whether one string contains measures_Contains_str_exact =
CONTAINSSTRINGEXACT
another is indicated by a return CONTAINSSTRINGEXACT(SELECT
(<within_text>, <find_text>)
value of TRUE or FALSE. EDVALUE(Info[Furniture]),"Sofas")
LOOKUPVALUE(<result_col Finds the row that meets the whole RetrieveCust_Name =
Name>, <search_colName>, criteria provided by LOOKUPVALUE(Customers[Name],
<search_value>[, search_colName & search_value Customers[Order ID],Orders[Order ID])
<search_colName>, and returns the value from
<search_value>]…) result_colName.
 Mathematical and Trigonometric Functions
The mathematical and trig functions are used to perform all sorts of mathematical functions on the
referred values.
Function Description Example
returns the absolute value of the given Profit=ABS(Orders[Selling Price]-
ABS( <number> )
number Orders[Product Cost])
Returns exponent's value raised to the Exp_Val=EXP(4)
EXP( <number> ) Number applied as exponent to the base e,
power of given number.
where e is equals to 2.71828182845904.
FACT( <number> ) Returns the factorial of a given number Fact_Val=FACT(4)
POWER (Number, Returns the result of a number raised to
Power_Val=POWER(4,3)
Power) a power.
SQRT(Number) Return the square root of a number. sqrt_Val=SQRT(64)
MOD(<number>, Returns value after dividing a number
mod = MOD(1035,5)
<divisor>) by a divisor.
ROUND(Number, Round the given values to the closest Profit=ROUND(Orders[Selling Price]-
Number Of Digit) value. Orders[Product Cost],2)
CEILING(Number, Returns the closest value, which is Sales=CEILING(Orders[Sales])
Number Of Digit) greater than or equal to a given value.
FLOOR(expression, Returns the closest value, which is less Sales=FLOOR(Orders[Sales],1)
Number Of Digit) than or equal to a given value.
Converts the value into currency data
CURRENCY(expr) Money = CURRENCY(Orders[Sales])
type.
CONVERT(<Expr>, Converts an expression to the specified convert=CONVERT(DATE(1997,12,23),
<Datatype>) data type. INTEGER)
Converts a given angle from radians into
DEGREES (angle) degrees = DEGREES(PI())
degrees
DIVIDE(Numerator,
Safely divide a function with the ability
Denominator, Safe_divide=DIVIDE(5,2)
to handle divide by zero case.
[Alternate Result])

24 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
EVEN(Number) Returns a number rounded up to the Even_no=EVEN(2.6)
ODD(Number) nearest Even/Odd integer. Odd_no=ODD(5.3)
Returns the sign of a given number. 1 Diff_Price = SIGN(Orders[Selling Price]-
SIGN(expression) for Positive Values, -1 for Negative Orders[Product Cost])
Values, and 0 for Zeros
COS(expression) returns the cosine value Cos_val=COS(Emp[Grade])

COSH(expression) returns hyperbolic cosine value Cos_val=COSH(Emp[Grade])

ACOS(expression) Returns inverse cosine value or arc

cosine value. Accepts values between -1 Asales=COS(Emp[Sales])
and 1.
Returns the inverse hyperbolic cosine or Ahsales=ACOSH(Emp[Sales])
ACOSH(expression) hyperbolic arc cosine value.

SIN(expression) Returns sine value Sin_val=SIN(Emp[Grade])

TAN(expression) Returns the tangent value. Tan_val=TAN(Emp[Sales])

LOG(expression, Returns the logarithmic value of a given Log_val = LOG(Emp[Sales], 2)

base_value) number.
 Parent-child Functions
This DAX function work with the data models following the parent-child hierarchy. It is used to
aggregate and filter data and perform complex calculations.
Function Description Example
PATH(child_column, Find the identifiers of all the parents Paths=PATH(Emp[emp_id],Emp[dept_id])
parent_column) (Parent) of current (Child) identifier.
PathItems=PATHITEM(PATH(Emp[emp_
Returns the item present at specified
PATHITEM id],Emp[dept_id]),2,1)
position.
Fetch second position
Returns the number of elements in a PathLength=PATHLENGTH(PATH(Emp
PATHLENGTH(path)
path returned by the PATH function. [emp_id],Emp[dept_id]))
PATHCONTAINS Returns TRUE if the PathContains =
(<path>, <item>) specified item exists within the PATHCONTAINS(PATH(Emp[emp_id],
specified path. Emp[dept_id]),4)
Returns a table that represents a subset Filter_SUMX = SUMX(Filter(Orders,
FILTER(table, filter)
of another table or expression. Orders[Product]="Peda"),Orders[Sales])
CALCULATE SUM_Measure=
Evaluates the expression given by the
(expression,filter1,filter CALCULATE(SUM(Orders[Sales]),
user with all the applied filters.
2) Filter(Orders, Orders[Product]="Modak"))

 Other functions
There a bunch of functions that do not fit in any particular category. These are also very useful
functions.
FILTER AND VALUE FUNCTIONS
Function Description Example
Removes or modifies filters on
ALL_DAX=CALCULATE(Sum(Orders
ALL(table_or_column) specified columns or tables.
[Sales]), ALL(Orders[Product State]))
temporarily altering the filters
Removes all context filters in the ALL_DAX=CALCULATE(Sum(Orders
ALLEXCEPT
table except filters that have been [Sales]), ALLEXCEPT (Orders[Product
(table,column1,column2)
applied to the specified columns. State]))

25 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Returns all the rows values in a
ALLSELECTED_DAX =CALCULATE
table or column. Removes context
ALLSELECTED (Sum(Orders[Sales]),ALLSELECTED
filters from columns and rows in the
([tableName or col_Name]) (Orders[Product]))
current query. Keeping filters that
come from outside.
BLANK() Returns a blank.
Sale_Maha =
Creates a new table by applying
CALCULATETABLE CALCULATETABLE(Orders,
filters to an existing table or
(expression, filter1, filter2) Orders[Product State]="Maharashtra")
column.
Filter the sales amounts by Product State
countif_city =
Returns the current value of the
EARLIER COUNTROWS(FILTER(Customers,
specified column and at-least needs
(column, number) EARLIER(Customers[City])=Customers
to have 2 nested ROW Contexts.
[City]))
Retrieves a value from a related Cust_name=RELATED(Customer[Name])
RELATED(column) table based on a specified retrieve the corresponding customer names
relationship. based on the Order_IDs
Follows an existing relationship and
Qty=SUMX(RELATEDTABLE(Orders),
returns a table that contains all
RELATEDTABLE(table) Orders[Quantity])
matching rows from the specified
table.
Removes duplicate values and
Get_UniqueValues = COUNTROWS
VALUES(column) return unique values. A blank
(VALUES(Customers [Name]))
values can be added.
Returns the first non-blank values in
FIRSTNONBLANK column, respectively, from a First=FIRSTNONBLANK(Orders[Qty], 0)
(column, expression) specified column or expression.
--------------------------------- ------------------------------------------- ----------------------------------------------------
LASTNONBLANK Return the last non-blank value,
(column, expression) respectively, from a specified Last=LASTNONBLANK(Orders[Qty], 0)
column or expression.

26 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Adding Measures to the Data Model:
Basic Aggregations in Measures, Using Multiple Measures, Cross-Table Measures, More
Advanced Aggregations, Filtering Data in Measures, Analyzing Data over Time.
**********************************************************************************
Basic Aggregations in Measures:
 Measures in Power BI are calculations or aggregations you create to process your data, such as sum,
average, count, or any custom calculation.
 Measures are often used in Power BI to derive insights from your data and create meaningful
visualizations. They are written using Data Analysis Expressions (DAX), a formula language
specific to Power BI
 When we combine or summarize the numerical data, it is called “Aggregation.”
 These measures allow you to create custom calculations and perform data aggregations tailored to
your specific requirements.
 When creating measures is that you should use the table name as well as the field name if there are
fields that have the same name in several tables.
 In Power BI Desktop, ensure that you are in Data View.
 Select the table and click on to create a "New Measure" button in the Modeling ribbon
 Replace Measure with the name that you want to use (Total Sales).

 DAX measures like this provide the flexibility to create custom aggregations and calculations,
allowing you to gain deeper insights from your data and create more informative reports and
dashboards in Power BI.
Using Multiple Measures
 You can also create measures that are the result of combining several DAX functions.
 The number of measures can add up and organize and manage.

Cross-Table Measures

27 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Data Visualizations
 Charts in Power BI-Types of charts, Maps in Power BI, Table and Matrix in Power BI, Subtotal
and Total in Matrix, Cards and Filters in Power BI, Conditional Formatting,
 Slicers in Power BI- slicers, adding a Slicer, Applying Slicers, clearing a Slicer, deleting a Slicer,
modifying a Slicer, Formatting Slicers-Slicer Orientation, Modifying the Outline, Adjusting
Selection Controls, Setting the Exact Size and X and Y coordinates of a Slicer, Slicer Header,
Slicer Items
 Designing Power BI Dashboards and Reports Dashboards, reports, Dashboards versus reports,
 Dashboard design- What is KPI, When to use KPI, Requirements for KPI, KPI Visualizations,
Visual selection, Layout, Navigation pane, Full screen mode, Supporting tiles, Custom date
filters, Single dashboard, Multiple-dashboard, Organizational dashboards, Multiple datasets
*********************************************************************************
Data Visualizations
 Data Visualization is a process of transforming raw data into graphical or pictorial representations
such as charts, graphs, diagrams, pictures, and videos.
 Users can quickly analyze the data and prepare reports to make business decisions effectively.

 Importance of Data Visualization

o Clarifies which element impacts customer behavior.
o Identifies the area to which you need to care.
o Guides you to understand which product should be placed in which location.
o Predicts the sales volume.

Charts in Power BI-

 Charts are used in every report or dashboard to display data to a user.
 Power BI offers a wide range of charts and visualizations that can be used to represent data in various
formats.
1) Bar Chart:
 Bar chart is used to compare and analyze data across the different categories or groups.
 It is suitable for showing the relationship between a numerical and a categorical variable.
 Bar charts are also called horizontal charts that represent the absolute data on the x axis.
 A bar chart is used to represent categorical data in rectangular bars and length of the rectangle
denoted by a specific measure value.
Types of Bar Charts
 Stacked Bar Chart: Displays the distribution of data categories using horizontal bars stacked
on top of each other. Comparing the composition of different data categories while also
showcasing the individual contribution within each category.

 Clustered Bar Chart: Display comparison of multiple series as in horizontal columns. Each
data series shares the same axis labels, so horizontal bars are grouped by category.
28 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

Properties of Bar Charts

o Title: Information about the chart
o X-axis: Mange the X-axis Value font size, color & Title.
o Y-axis: Mange the Y-axis Value font size, color, Title, Display Unit etc.
o Legend: Specified the Legend Text color, font size, Position & Title.
o Bars: These heights represent the total value of all the legend
o Columns: Change the color of chart & maintain the spacing between columns.
o Data Labels: Enable the data labels on chart, manage the display unit of labels
o Total Labels: Enable Total labels on chart.
 100% stacked bar chart: It is used to display relative percentage of multiple data series
in stacked bars, where the total (cumulative) of each stacked bar always equals 100%.

2) Column Charts
 Column charts are similar to bar charts
 To visualize multiple categories and directly compare them against each other.
 The larger the value shows the taller the column.
 It display differences, high & lows, and trends across different categories.
 Column charts are used to compare the same data by forming some clusters and comparison
between those clusters.

29 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Stacked Column Chart: Comparing the total values of different groups, as well as for identifying
the contribution of each group to the overall total. Useful to compare multiple dimensions against
a single measure. A vertical axis represents the numerical values of the data, and horizontal axis
displays the categories or time periods.
 Clustered Column Chart: displays data as vertical columns grouped by categories
and comparing values across different categories
 100% stacked column chart: It is used to display relative percentage of multiple data series
in stacked columns, where the total (cumulative) of each stacked columns always equals 100%.

3) Line Chart:
 Line chart is used to show change or trends, patterns over a period of time like date, month, year.
 Line chart is a series of data points which are connected to each other by lines.
 Each dot point in the line corresponds to a data value in the given category and connects the
previous and next data point with a straight line.
 This chart is effectively for showing trends over time and highlighting changes in data values.
 It is most popular, general, and simple charts to show the overall change of values over time.

Line Chart Properties:

o X-Axis: Set the font size, font family, color and title for x-axis.
o Y-Axis: Set the font size, font family, color and title for y-axis.
o Secondary Y-axis: Set the font size, font family, color, display units, title for secondary y-axis.
o Lines: In line section you can manage the following properties:
 Data colors: Change the colors used for each series in the chart & customize the all line
color individually.
 Shapes: Enable markers on Line & customize the marker shapes for all lines individually.
o Data labels: Its enable values on Line chart.
o Markers: Enable markers on line & customize the marker shapes, color for all lines
individually.

30 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
o Series labels: Display the series labels on the visual.
o Zoom Slider: Enables zoom feature on chart, easily saw the small values on chart.
Types of Line Chart:
 Line & Stacked Column Chart: Combines a line chart and a stacked column chart to show
data trends and the proportion of each group simultaneously

 Line & Clustered Column Chart: It uses a clustered column instead of a stacked column to
show the proportion of each group

4) Area Chart:
 It is a variation of the line chart and used to represent the cumulative total of the data.
 The area between the axis and lines is commonly filled with colors, textures, and patterns.
 You can compare more than two quantities with area charts.
 An area chart is used to show what is the gap between one data point to another data point and
make some decisions whether the sale increased over some time or not.
 Example: Visualizing monthly revenue trends for different product categories can be
accomplished by plotting area charts.

Types of Area Chart:

 Basic (Layered) area chart: Displays multiple data series stacked on top of each other,
showing the cumulative effect of each series. It is ideal for visualizing trends over time and
comparing the relative contribution of different categories to the overall data.
31 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Stacked area chart: A data series stacked on top of one another, filling the area between the
data lines. It highlights the total size of the data and helps observe the changes in the
composition of each category over time or other dimensions.

5) Ribbon Chart:
 A ribbon chart is a type of column chart with some advance functionality.
 Ribbon chart helps you quickly determine the data category with the highest rank or the largest
value.
 It always shows large value in each column at the top then the next value comes after.
 Each legend’s values showing the rankings order of high and low values, using different color
ribbons.
 It compares the values for different categories over time and displays the ribbons’ increase or
decrease in category values at different intervals of time.
 The larger value of the ribbon would mean a higher category value at the reference time point
compared to other time periods.
 Ribbon charts can be display various metrics and categories, providing insights into data trends
and comparisons.

Ribbon chart Properties:

o General: Allows you to set X axis & Y axis, width & height of chart.
o Legend: Set the font size, font-family, color & position of legend on chart.
o X-Axis: Allows you to modify the appearance of the X-Axis section of this chart.
o Title: Specified the Title name for Chart.
o Data labels: Its enable the values for each category on chart & you can set the display
unit, position & orientation of labels.
o Zoom Slider: Enables zoom feature on chart, using this you can easily saw the small values on
chart.
o Ribbons: Allow you to set the spacing, connector color, connector transparency, and border for
the ribbon.
32 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
6) Scatter Plot:
 Scatter charts, also known as Bubble chart, shows the relationship between two numerical values.
 Using two points of data the chart is Scatter chart, when adding a third point of data then the
chart will become a Bubble chart.
 It is useful for identifying correlations and outliers in data
 In Scatter charts, we can set the number of data points, up to a maximum of 10,000.
 Scatter chart does not support data labels, you can only enable category labels for chart.
 It helps us analyze and summaries the data very efficiently. We can also add legends, bubble
sizes, and animations to this chart.
 You can create a storytelling data using a Scatter chart with the Play Axis feature.
 A bubble chart replaces data points with bubbles, where the bubble size represents an additional
third data dimension.
 Example: To compare sales and profit values by category and subcategory

Scatter chart Properties:

o Details: to specify what categorical column the visual represents
o Legend: to specify which column sub-divides the Details data
o X-Axis: specify what column represents the horizontal axis
o Y-Axis: specify what column represents the vertical axis
o Size: specify the column that represents the marker sizes
o Play-Axis: specify what column (usually a date column) is responsible for the trend overtime.
o Tool Tips: add a data field or measure to be included in information displayed when hovering on a
mark in the chart.
7) Waterfall Chart:
 Waterfall charts are useful to show the running total as values where they are added or subtracted.
 It is used to show how an initial value is affected by a series of positive and negative changes.
 Columns in the waterfall chart are color-coded for the view of increase or decrease in the values.
 It is suitable for displaying financial data and identifying the main drivers of change

33 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
8) Funnel Chart:
 A Funnel Chart is used to represent how the data moves through a process or system.
 It shows how data flows throughout all the stages of some process which is easy to read and
understand.
 It represents a linear process with sequential and connected stages.
 Each shapes represents a stage, where the top stage (Head) is the largest and the bottom (Neck) is
the smallest. The largest or top stage is called the intake stage.
 Move from top to bottom, the size is decreases i.e., sequentially narrows down.
 Each stage of a funnel chart represents a portion as compared to the total value.
 Funnel charts are widely used to represent the sales funnels, recruitment process, and item order
fulfillment process which means multiple stages of a whole long process.
 Funnel chart is used in tracking metrics, especially the stage-wise progress of any process.

9) Pie Chart:
 Pie chart is a round-shaped circle representation of data that displays percentages of each category
within a whole data.
 The whole data can be divided into slices, the size of each slice to show the percentage of each
category.
 It is useful for showing the distribution of data across various types and comparing the relative sizes
of those categories.
 It is suitable for displaying data as a percentage of the whole
 It helps users to understand the data quickly.
 Example: Total sales split by product category. You can see the percentage of each product category
into the total revenue.

Pie chart Properties:

o Legend: Allows you to set position, Legend name, color, font size, font family.
o Slices: Change the colors of each slice and manage the spacing of inner radius.

34 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
o Detail labels: Its enable values on chart & you can change Label style, color, background
color, display unit and many more of Data labels.
o Rotation: You can rotate the slices of donut chart.
10) Donut Chart:
 The donut chart is exactly the same as a pie chart, the only difference is pie chart has a circle, but
a donut chart has an empty hole in the circle.
 It is used to show the percentage of data in each category
 Donut Chart are useful to display small categories of data & avoid for many categories, or when
categories do not sum to 100%.

11) Gauge Chart:

 It is also called a dial chart or a speedometer, using a pointer to read data and display the
information on a dial.
 A gauge chart shows progress near a goal or target.
 It is suitable for displaying current status of a KPIs such as revenue, sales, and profits or metrics in
a visual format
 It compares variable values using numerous pointers on the same or distinct gauges.
 The value inside the arc represents the progress value.
 It distributes all arc from the smallest value (left-most) to the most significant value (right-most).

12) Card:
 Card visuals are simple and compact representations of single data values or key metrics (KPIs).
 It is useful for highlighting key metrics and KPIs or summary information such as total sales or
profit.
 They display a single value, such as a number, percentage, or count, just the title as additional
context.

35 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
13) Multi-Row Card:
 It is similar to a card.
 Multi-Row Card visual display multiple values from a dataset in a tabular format, allowing you to
showcase multiple rows of data in a card-style layout.
 This is useful when you want to present detailed information for each data item while saving space
on the report page.

14) Treemap:
 TreeMap is the hierarchical chart, that shows the parent and child data distribution.
 Treemap is used to display large amounts of hierarchical data in Rectangle boxes.
 Treemaps display a hierarchical structure where each level of the hierarchy is labeled with a colored
rectangle, i.e., a branch node.
 The larger values are present with the largest colored rectangles/branch node at the top left, and
lower numeric values are present with the smallest branch node at the bottom right.
 Displays hierarchical data as a set of nested rectangles. It is useful for comparing proportions within
the hierarchy

15) KPI (key performance indicator):

 It is used to assess performance in various areas.
 KPI provide visual indicators of growth and progress by comparing actual results to predetermined
targets. Also known as the threshold.
 KPI indicates the current performance of a business to complete a target.
 To create a KPI, you must add base value, target value, and status thresholds.
 This KPI helps the organization to monitor their performance and tracking progress.
Types of KPI:
1. Single KPI: Single KPI has 3 sets of fields which are Indicator, Trend Axis and Target Goal.

36 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Indicators: Shows the actual values against targeted values.
Trend Axis: This should be the month name or number axis line.
Target Goal: Shows the target which is to be achieved.
2. Dual KPI: Dual KPI has Axis, Top Value and Bottom Values.
 Axis: Has the month name or number.
 Top and Bottom Values: These are Top and Bottom values from the single KPI chart.

KPI Properties:
Callout values: Set display unit, font size, color, horizontal & vertical alignment for Actual sales.
Icons: Set the icon size for KPI.
Trend axis: Enable/ Disable graph for KPI. And set color for Good, Neutral & Bad.
Target label: Provides below options-
o Values: Enable/ Disable Target values.
o Label: Assign name for Target.
o Set Font color, font size & font family.
o Distance to goal: Display difference of Target & sales. Select value & percentage from Label
dropdown.
o Distance direction: Manage Increase/ Decrease direction for positive.
Date: Set font size, text & color for date.
Maps in Power BI
 Shows data geographically. It is useful for identifying patterns and trends in data across regions
 Filled Map:
 Similar to a map, but areas are shaded or patterned in proportion to the data
 Azure Map:
 Provides advanced geospatial capabilities for mapping visualizations

Table in Power BI
 Table contains the data in the form of rows and columns.
 Each row contains detailed low-level information of each column. It may also include the header
section and the row totals.
 Tables help compare multiple fields on a single page.
 One or more measures and columns are placed side by side
 When to use a Power BI Table
 To display the data in a tabular format.
 To view the data at the detailed level.
 To show all the available rows in the given columns.
 If you don’t need a visual representation of data.
 To show more number of numerical column data by categories.
 Create a Table
Step 1) Select the table chart option in Visualization Pane. It will create an empty default table in
the canvas.
Step 2) you need to fill up the arguments under the Value option. For that, drag and drop
variables into Values. It will create a table.

37 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

 Formatting Table Visualization

There are many other options available which you can use on your own very easily.
 You can set the font color, background color, and outline.
 You can also change the font family, text size
 You can also scroll up and down the table with the hover tools.

Matrix in Power BI
 Matrix is similar to a table that contains rows and columns.
 The matrix shows the aggregated data instead of displaying the detailed rows.
 The aggregation of the numeric measure depends on the column and row headers.
 If you add a hierarchy to column or row headers, you can drill up and down based on the levels.
 It can display summaries and subtotals by columns and rows.
 A matrix can display data by removing the repeating values.
 Matrix is called the Cross tab, helps display the relationships between two or more groups.
 Use of matrix
 To display the aggregated information in tabular format.
 To perform row and column grouping.
 To view row and column subtotals, totals, and grand totals.
 To enable drill down action across the report.
Table Matrix
Data presented in 2-Dimension data grid format. Data presented in Multidimensional data grid
format.
By default display all data Display aggregate the data
Rows not fixed but columns are fixed. Rows & Columns both are not fixed.
Only shows the columns and rows that are You can dynamically add or remove columns
explicitly defined in the data model. and rows based on the data
38 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Slicer in Power BI
 A slicer is a visual element that allows users to filter data in a report.
 It presents a list of values that can be selected to filter data.
 Slicers can be used with various types of visualizations and essential tool for data analysis and
exploration.
 They provide an easy way for users to slice and dice data, allowing them to focus on specific subsets
or categories.
 This feature enhances data exploration, making it easier to identify trends, patterns, and outliers.
 With slicers, its simple to navigate through large datasets. Users can quickly filter and analyze data in
real-time.
1. Select the visual you want to add a slicer to.
2. Click on the “Format” tab.
3. Click on “Edit Interactions.”
4. Select the slicer you want to add.
5. Customize the slicer settings.
 Types of Slicers:
This is the 4 primary types of slicers that can be added to a report.
 Date or Number Range Selectors–
o Filter data within a specific range Filter data within a specific range
 Dropdown Slicer–
o Filter data by selecting options from a dropdown menu.
o It provides a user-friendly way to narrow down data and focus on specific aspects.
 Tiles–
o Buttons that dynamically re-size or can be defined number of buttons or tiles on a visual
 Horizontal or Vertical List Slicer–
o Filter data by selecting multiple items from a list

Power BI allows you to adjust text color, background, color, the number of values users can see at a
single time along with a number of other visualization options.
 Adding a Slicer:
If you want to see “Sales,” “COGS,” and “Profit” value for any specific product. By adding the
Power BI Slicer option, we can create one.
1. From the “Visualizations” pane, click on the “Slicer” visual on the dashboard page.

39 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
2. For the “Fields” pane of the slicer, visual drag and drop the “Product” column.

3. It should insert the product name into the slicer.

We can choose available products, and then the dashboard will show numbers only for the
selected product.
4. We will choose “LG” from the slicer and see its impact on the overall dashboard.

Overall sales showed “99.37 M” since we selected only the “LG” product from the slicer. Therefore,
it only shows “12.34 M,” and the other visuals show values only for the selected product (LG).
 Add Multiple Slicers
We can add more than one slicer type to our Power BI dashboard.
We have added a similar slicer for “Product” and a slicer for “City.”

40 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1

 Now, choose any one city and anyone product and see the magic with dashboard numbers. For
example, we have selected the city as “Bangalore” and the product as “Redmi.”

 Below is the dashboard for selected items.

So, the overall total sale value for the city “Bangalore” for the product “Redmi” is “2.92 M.”
 Formatting Slicers
Once we add the slicers, and you want change this default format of slicers.
1: Select Multiple Items
To select multiple items from the Power BI slicer, you need to hold the Ctrl key and keep clicking on
the values you want to select.

41 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 You can also design the selection option under a slicer’s “Format” tab. Go to “Format” and click on
“Selection controls.”
#2: Insert Underline for Each Value
If you want to show an underline for each item in the slicer, we can do it from the “Item” section
from the “Format” tab. Then, select the “Bottom only” option from the “Outline” to add the
underline.

From this section, you can also change the font color of each item in the slicer, font size, background
color, font name, etc.
#3 – Show Drop Down Instead of List
If you do not want to show a Power BI slicer in the selection list like the above and instead want
to show a drop-down list, you can do this by clicking on the down arrow key of the slicer. It will
show the option of “List” or “Dropdown.”

Based on the selection you make, it will start to show the slicer accordingly. For example, we have
chosen “Dropdown,” and our Power BI slicer looks like this now.

 Slicer Orientation
One more thing you can do to fit in your slicer is you can change the “Orientation” of the slicer to
either “Horizontal” or “Vertical.”

42 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
Since we have chosen “Horizontal” as the “Orientation” type, our slicer will look like this.
 Applying and Clearing Slicers:
 To create a clear all slicers button to remove all filters, navigate to Insert, Buttons, on the
Power BI Ribbon.
 To clear a slicer and stop filtering on the selected data elements in a view, click the Clear Filter
icon at the top right of the slicer.
 When you click on buttons a menu appears with all of the different pre-set button icons and
actions that can quickly be added. At the bottom of the list, select Clear all Slicers.
Why Clear Slicers
1. Resetting the View: Reset the view of your report or dashboard to its initial state, showing all
data without any applied filters.
2. Removing Errors: If you've accidentally selected slicer options that don't make sense or have
caused errors in your visualizations, clearing the slicers can help resolve these issues.
3. Quick Exploration: When you want to explore your data without any specific filters, clearing
slicers is a convenient way to get an unfiltered view.

 A new rectangle button will be added to your report dashboard.

 You can adjust the background, text displayed, colors, and interactions that occur when people
hover over or click the button.
 You can manually assign the clear all slicers or apply all slicers action upon pressing the button.
 Button actions can be updated by selecting a button, navigating to the format options panel,
button, and changing the action to Clear all Slicers.

43 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
 Once the appropriate action is selected, it will become active for the button.
 When the button is pressed, it will clear all of the previously selected slicer options without a user
having to manually refresh the dashboard to reset it to its original state.
 Testing Apply All Filters and Clear all Filters Buttons

 Deleting a Slicer:
 To delete a slicer and remove all filters that which it applies for a view, select the slicer and press
the Delete key.
 Modifying a Slicer:
 If all you want to do is replace the field that is used in a slicer with another field.
1. Select the slicer that you want to modify.
2. Drag the new field over the existing field in the field well.
 The current slicer field is replaced by the new field and the slicer updates to display the contents
of the field that you added. Alternatively, you can delete the slicer and re-create it.
 Modifying the Outline:
 The outline is the line that separates the title from the items in a slicer.
 The following element can format:
1. Select the slicer that you want to modify
2. In the Visualizations pane, click the Formatting icon.
3. Expand the General tab.
4. Click the “Outline weight” up and down triangles to set the weight of the outline to 3 points.
5. Click the “Outline color palette” pop-up and choose a color.
The separator line will change to reflect the modifications that you have made.

 Adjusting Selection Controls:

1. Adding or Removing the Select All Box
1. Select the slicer that you want to modify.
2. In the Visualizations pane, click the Formatting icon.
3. Expand the “Selection controls” tab.
4. Slide the “Select all” switch to the On position.

44 | S H K
B.Sc (ECS)-II | Data Visualization Unit - 1
This will add a Select All item to the top (or left) of the slicer.
2. Enabling Single Select
A slicer’s default mode is to select only a single item, unless the user Ctrl-clicks several items. If you
prefer to add items one at a time and deactivate them individually. You can enable Single Select:
1. Select the slicer that you want to modify.
2. In the Visualizations pane, click the Formatting icon.
3. Expand the “Selection controls” tab.
4. Slide the Single Select all switch to the Off position.
This will set the slicer interaction to single select.
3. Setting the Exact Size and X and Y coordinates of a Slicer
If you want to place a slicer with total accuracy on a dashboard canvas, then you can set the X and Y
(horizontal and vertical) coordinates for the slicer. You can also specify its exact height and width.
1. Select the slicer that you want to modify.
2. In the Visualizations pane, click the Formatting icon.
3. Expand the General tab.
4. Replace the X Position, Y Position, Width, and Height values with the pixel values that
define the size and position of the slicer that you wish to apply.
4. Slicer Header
 Slicer Header is the heading of the slicer, it is not the heading of this visualization.
 We can add a separate heading for the visualization using the title section.
 We have 3 options i.e. Text, Border, and Background.
5. Slicer Items
Slicer items are the individual elements that make up the list of data that appear in a slicer, based on
the underlying field. These can be formatted to focus the attention of the reader.
1. Select the slicer that you want to modify.
2. In the Visualizations pane, click the Formatting icon.
3. Expand the Items tab.
4. Ensure that the Header switch is in the On position.
5. Click the “Font color palette” pop-up and choose a color for the item text.
6. Click the “Background color palette” pop-up and choose a color for the background of each item.
7. Adjust the “Text size” slider to tweak the size of the text of each item.
8. Click the Outline pop-up and select Frame.
You can also format the Background, Title, Lock aspect ratio of a slicer
Slicers Filters
Purpose Visual interaction for specific values. Data refinement at the dataset level.
Click-based selection for visual
Operation Condition-based inclusion/exclusion.
changes.
Alters visuals immediately upon Influences all visuals using the
Impact on Visuals
selection. dataset.
Requires defining specific
User Interaction Intuitive and user-friendly.
conditions.
Use Frequency Frequently used Rarely used.
Can involve complex logical
Complexity Straightforward and simple to use.
expressions.
Selecting months to view sales
Example Use Case Excluding low-performing products.
trends.
Aesthetic
Adds to report aesthetics. Works behind the scenes.
Integration

45 | S H K