Dax in power bi, context Dax formulas, date data bars, histogram and pie charts

DAX is the formula language used in Power BI to:

• Create calculated columns
• Create measures (dynamic calculations)
• Perform time-based analysis
• Build KPIs and business logic

1️⃣ What is DAX?

DAX stands for Data Analysis Expressions.

It is similar to Excel formulas but designed for data models.

In Power BI, DAX is used to:

• Calculate totals
• Create percentages
• Compare current vs previous periods
• Perform date analysis
• Apply filtering logic

2️⃣ DAX: Calculated Column vs Measure

Understanding this difference is CRITICAL.

| Feature | Calculated Column | Measure |
|----------|-------------------|----------|
| Calculated When | Data is loaded | When visual is used |
| Stored in Model | Yes | No (calculated on demand) |
| Depends on Filters | No | Yes |
| Used For | Row-level logic | Aggregations & KPIs |

Example Data

Assume we have a Sales table:

| Date       | Product | Sales | Quantity |
|------------|----------|-------|----------|
| 2024-01-01 | A        | 100   | 2        |
| 2024-01-02 | B        | 200   | 4        |
| 2024-01-03 | A        | 150   | 3        |

3️⃣ Basic DAX Formulas

A) Calculated Column Example

Create Revenue Per Unit:

Revenue Per Unit = Sales[Sales] / Sales[Quantity]

This calculates for each row.

B) Measure Example

Total Sales:

Total Sales = SUM(Sales[Sales])

Average Sales:

Average Sales = AVERAGE(Sales[Sales])

4️⃣ Understanding DAX Context (Very Important)

DAX works using Context.

There are two main types:

🔹 Row Context

Applies when calculating for each row.

Example:

Revenue Per Unit = Sales[Sales] / Sales[Quantity]

Each row calculates independently.

🔹 Filter Context

Applies when visuals filter data.

Example:

Total Sales = SUM(Sales[Sales])

If you filter by Product A in a chart,

Power BI recalculates Total Sales for only Product A.

🔥 Example of Filter Context

Sales for A =
CALCULATE(
    SUM(Sales[Sales]),
    Sales[Product] = "A"
)

CALCULATE() changes filter context.

This is the most powerful function in DAX.

5️⃣ Working with Date Data in DAX

Time intelligence is one of DAX’s strengths.

First rule:

You MUST have a proper Date Table.

Create a Date Table

Date Table =
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2024,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date])
)

Mark this table as:

👉 "Mark as Date Table"

Time Intelligence Measures

Year-To-Date (YTD)

Sales YTD =
TOTALYTD(
    SUM(Sales[Sales]),
    'Date Table'[Date]
)

Previous Year Sales

Sales Previous Year =
CALCULATE(
    SUM(Sales[Sales]),
    SAMEPERIODLASTYEAR('Date Table'[Date])
)

Growth %

Sales Growth % =
DIVIDE(
    [Total Sales] - [Sales Previous Year],
    [Sales Previous Year]
)

6️⃣ Data Bars in Power BI

Data bars are a form of conditional formatting inside tables.

They visually show magnitude inside cells.

How to Add Data Bars:

1. Select Table Visual
2. Click Sales column
3. Conditional Formatting
4. Data Bars
5. Choose color

This creates:

| Product | Sales |
|----------|--------|
| A | ████████ 100 |
| B | ███████████████ 200 |
| C | █████████ 150 |

Data bars are excellent for:

• Quick comparison
• Ranking values
• Highlighting performance

7️⃣ Histogram in Power BI

A histogram shows distribution of numerical data.

Example: Sales distribution.

How to Create:

Option 1 (Built-in):

1. Select Sales column
2. Click "New Group"
3. Choose Bin Size (e.g., 50)
4. Use Bin field in column chart

Example Bins

| Sales Range | Frequency |
|-------------|-----------|
| 0–50        | 3         |
| 50–100      | 5         |
| 100–150     | 7         |
| 150–200     | 4         |

Histograms help:

• Detect skewness
• Identify outliers
• Understand spread

8️⃣ Pie Charts in Power BI

Pie charts show proportions of a whole.

Example: Sales by Product.

Add:

• Legend → Product
• Values → Sales

Result:

| Product | % of Total |
|----------|------------|
| A | 40% |
| B | 35% |
| C | 25% |

⚠️ Important:

Pie charts are best when:

• Few categories (≤5)
• Comparing proportions

Avoid:

• Too many slices
• Similar values

9️⃣ When to Use Each Visualization

| Visualization | Best For |
|---------------|----------|
| Table with Data Bars | Comparing exact values visually |
| Histogram | Understanding distribution |
| Pie Chart | Showing proportion of whole |
| Line Chart | Trend over time |
| KPI Card | Highlighting one important number |

🔟 Key DAX Functions You Must Know

| Function | Purpose |
|----------|----------|
| SUM() | Add values |
| AVERAGE() | Calculate average |
| COUNT() | Count rows |
| CALCULATE() | Modify filter context |
| FILTER() | Apply condition |
| DIVIDE() | Safe division |
| TOTALYTD() | Year-to-date calculation |
| SAMEPERIODLASTYEAR() | Compare previous year |