Data Analysis - Power BI Notes

Power BI DAX (Data Analysis Expressions) functions are grouped into categories. I’ll explain the main types, then give sample data and queries for each so you can see how they’re used in practice.

What is DAX Used for?

• Create New Columns on a table
• Method of connecting Disparate Data Sources with multiple Key Columns

Calculated Columns
Create New Columns on a table
Method for Connecting Disparate Data Sources with Multiple Key
Columns

Calculated Measures
Create Dynamic Calculations for Reporting
Ratios/Percentages
Time Intelligence Calulations
Complex Relationships

Calculated Tables
Create a new table derived from another table
Can be used to create a date table when one doesn’t exist already

Navigation Funcations:

RElated

RElated TAble

🔹 Categories of DAX Functions

Aggregate Functions

SUM, AVERAGE, MIN, MAX, COUNT, DISTINCTCOUNT

Logical Functions

IF, AND, OR, SWITCH

Text Functions

CONCATENATE, LEFT, RIGHT, LEN, SEARCH, REPLACE

Date & Time Functions

TODAY, NOW, YEAR, MONTH, DAY, EOMONTH, DATEDIFF

Filter Functions

CALCULATE, FILTER, ALL, ALLEXCEPT, RELATEDTABLE

Time Intelligence Functions

TOTALYTD, SAMEPERIODLASTYEAR, DATESYTD, PREVIOUSMONTH, PARALLELPERIOD

Mathematical & Trigonometric Functions

ROUND, ROUNDUP, ROUNDDOWN, ABS, DIVIDE, POWER

Information Functions

ISBLANK, ISNUMBER, ISTEXT, ISFILTERED

Relationship Functions

RELATED, RELATEDTABLE, USERELATIONSHIP

Iterator (X) Functions

SUMX, AVERAGEX, MAXX, MINX, COUNTX

Stacked Bar Chart:

• select Y axis and X axis and legend
• Rename
• Small multiples in Power BI is a visualization feature that breaks down a single chart into a grid of smaller, individual charts, each displaying data for a specific category of a chosen dimension

Types Of Bar Charts
There are mainly two variations of Bar Chats. Depending on the situation charts are used.

Clustered Bar Charts: This is a default Bar chart where all the categories are displayed against the same value category.
Stacked Bar Charts: These are the basic typed charts that allow the comparison of one category to another category.

Main Parts of Stacked Bar Charts
Title: It denotes the information about the chart
X-axis: It is the individual entry for the category to be presented
Legend: It is the different category that will contribute to the charts
Y-axis: It is for the value against each type of category
Bars: These heights represent the total value of all the legend

The format pane in Power BI custom visuals

https://www.microsoft.com/en-us/security/business/microsoft-intune

Types of graphs:
Bar chart(Stacked, Clustered, 100% stacked)
Column chart(Stacked, Clustered, 100% stacked)

visual calculations

A visual calculation is a DAX calculation 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.

Semantic Model:

Paginated Reports:

1: Dimension Modeling

1. Multiple Fact Table
2. Role Playing Table
3. Data Analyst of the future
4. Data Modeling

Dimension Model - organize data
fact table - fact is an event that may or may not include measures
Dimension table - category of information or noun , descriptive
Attribute - (Column in dimension table ) descriptor of the object

Fact tables

Conceptual Model
Logical Model

surogate key

calculate

useRElationship