𝗧𝗢𝗣 𝗙𝗥𝗘𝗘 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝗧𝗢 𝗕𝗢𝗢𝗦𝗧 𝗬𝗢𝗨𝗥 𝗦𝗞𝗜𝗟𝗟𝗦 𝗜𝗡 𝟮𝟬𝟮𝟲!

Explore FREE online learning resources to develop in-demand skills and improve your career opportunities!

🎓 Why Explore These Courses?
✅ Learn valuable professional skills
✅ Improve your resume
✅ Build practical knowledge
✅ Learn at your own pace

𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗛𝗲𝗿𝗲👇

https://pdlink.in/3VBdt6V

💡 Learn New Skills → Build Confidence → Unlock Career Opportunities!

📊 Power BI Learning Roadmap — Part 4

Topic 10: Merge Queries and Append Queries in Power Query

When working with real-world data, you rarely get everything in one table.

You may have:

• Sales data in one table

• Product information in another

• Customer information in another

• Monthly sales in separate files

Power Query provides two important features to combine these datasets:

1. Merge Queries

2. Append Queries

The key difference is simple:

• Merge Queries = add columns from another table

• Append Queries = add rows from another table

1. Merge Queries

Merge Queries is used when you want to combine two tables based on a common column.

It is similar to a JOIN in SQL or a lookup such as XLOOKUP in Excel.

Example

Sales table:

ProductID Quantity Sales

P101 10 5000

P102 5 3000

P103 8 6400

Products table:

ProductID ProductName Category

P101 Laptop Electronics

P102 Mouse Accessories

P103 Keyboard Accessories

Both tables contain ProductID. We can merge them using ProductID.

After merging and expanding:

ProductID Quantity Sales ProductName Category

P101 10 5000 Laptop Electronics

P102 5 3000 Mouse Accessories

P103 8 6400 Keyboard Accessories

2. How to Merge Queries

In Power Query:

1. Select the first table.

2. Go to Home.

3. Select Merge Queries.

4. Select the second table.

5. Select the matching column in both tables.

6. Choose the Join Kind.

7. Click OK.

8. A new column containing a table will appear.

9. Click the Expand button.

10. Select the columns you want to bring into the first table.

3. Join Keys

The column used to match two tables is called a key.

For example:

ProductID

Both tables should have compatible data types. If one table has ProductID = Text and the other has ProductID = Number, the merge may not work as expected.

Always check the data type before merging.

4. Types of Joins

Left Outer

Keeps every row from the first table and brings matching rows from the second table. This is one of the most commonly used joins.

Inner

Keeps only rows that have a match in both tables.

Right Outer

Keeps all rows from the second table and matching rows from the first table.

Full Outer

Keeps all rows from both tables. Matching rows are combined, while unmatched rows are also retained.

Left Anti

Returns rows from the first table that do NOT have a match in the second table. This is extremely useful for finding missing records.

Example: If Sales has P101, P102, P103, P104 and Products has P101, P102, P103, Left Anti identifies P104.

Right Anti

The opposite of Left Anti. It identifies records that exist in the second table but not in the first.

5. A Very Important Problem: Duplicate Keys

Suppose your Products table contains:

P101 Laptop

P101 Laptop - New

And your Sales table contains P101 5000

If you merge these tables, Power Query may produce multiple rows for P101. This can unintentionally increase your row count. So before merging, understand whether your key is unique.

6. Append Queries

Append is completely different from Merge. Append combines tables by stacking rows.

Example:

January Sales: Laptop 5000, Mouse 2000

February Sales: Laptop 6000, Mouse 2500

After Append: All rows stacked vertically.

7. Append Two Queries

If you have two tables: January Sales and February Sales

Use: Home → Append Queries → Append Queries as New

Then select the two queries.

8. Append Three or More Queries

You can also append more than two tables, for example January to June. All can be combined into one Sales table. This is useful when monthly data is stored separately.

9. What If Column Names Are Different?

Suppose January contains Product Sales and February contains Product Revenue. Power Query treats Sales and Revenue as different columns, resulting in null values. So before appending, make sure equivalent columns have consistent names.

10. Merge vs Append

Merge

You have Sales (ProductID | Sales) and Products (ProductID | ProductName | Category). You want ProductID | Sales | ProductName | Category. You are adding columns. Use Merge.

Append

You have January Sales (Product | Sales) and February Sales (Product | Sales). You want one larger table containing all records. You are adding rows. Use Append.

11. Real-World Example

Imagine you work for a bank. You receive transaction files every month: Jan_Transactions.csv, Feb_Transactions.csv, Mar_Transactions.csv.

Each file contains Transaction ID, Account ID, Date, Amount, Currency. You can append all three files into one transaction table.

Then you have a separate Account Master table with Account ID, Customer Name, Region, Account Type.

You can merge the Account Master into the transaction table using Account ID.

12. Common Mistakes

Mistake 1: Choosing the wrong key

Using Customer Name instead of Customer ID can create unreliable matches. Prefer stable identifiers such as CustomerID, AccountID, ProductID.

Mistake 2: Different data types

Text and Number columns cannot reliably match. Check the data type before merging.

Mistake 3: Duplicate keys

Duplicate keys in the lookup table can multiply rows. Always investigate unexpected row-count increases.

Mistake 4: Wrong Join Kind

Choosing Inner instead of Left Outer can cause unmatched records to disappear.

Mistake 5: Appending inconsistent columns

If one file calls a column Sales and another calls it Revenue, Power Query treats them as separate columns. Standardize column names before appending.

🧪 Practical Exercise

Create these three tables.

Sales: P101 5000, P102 3000, P103 7000

Products: P101 Laptop Electronics, P102 Mouse Accessories, P103 Keyboard Accessories

January: P101 5000, P102 3000

February: P101 6000, P102 3500

Try to:

1. Merge Sales with Products.

2. Bring ProductName and Category into Sales.

3. Append January and February.

4. Check the final row count.

5. Try a Left Anti join and identify products missing.

🎯 Double Tap ❤️ For More

𝗗𝗮𝘁𝗮 𝘀𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗪𝗶𝘁𝗵 𝗔𝗜😍

Future Ready Data Science and AI Skills You Build

->Build Python, Machine Learning, and AI Skills
->Get Mentored by Industry Experts
->Practice with Mock Interviews
->Ace Your Placements & Land Dream Tech Job

🤝 500+ Partner Companies |🎓 2000+ Students Placed

🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 :-

https://pdlink.in/47pezFz

📲 Save & Share with Friends Preparing for Data Science & AI Jobs!

𝗜𝗕𝗠 𝗙𝗥𝗘𝗘 𝗧𝗘𝗖𝗛 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓

Explore IBM Cognitive Class and build in-demand tech skills without spending money on course fees!

📚 𝗧𝗼𝗽 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗘𝘅𝗽𝗹𝗼𝗿𝗲:-

💫 Artificial Intelligence & Machine Learning
🔥Python for Data Science
📊 Data Science Fundamentals
☁️ Cloud Computing & Big Data

🔗 𝗦𝘁𝗮𝗿𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗛𝗲𝗿𝗲 👇

https://pdlink.in/4zGswuX

📲 Save & Share with friends preparing for tech careers! 🔥

📊 Power BI Learning Roadmap — Part 4

Topic 12: Power Query M Language

Until now, we have mostly used the Power Query interface to clean and transform data.

But behind almost every transformation you perform in Power Query, there is code.

That language is called M.

Understanding M doesn't mean you need to become a programmer. You just need to understand how Power Query represents the transformations you perform.

1. What is M Language?

M is the language used by Power Query for data transformation.

For example, when you:

• Remove columns

• Filter rows

• Change data types

• Rename columns

• Add custom columns

• Merge queries

• Append queries

• Group data

Power Query creates M code for those actions.

You can see this code in:

View → Advanced Editor

2. A Simple M Example

Suppose your table contains:

Product Sales

Laptop 10000

Mouse 3000

Keyboard 5000

If you filter Sales greater than 4000, Power Query generates an M expression similar to:

Table.SelectRows(Source, each [Sales] > 4000)

This means:

Take the Source table and keep rows where Sales is greater than 4000.

You don't have to memorize this immediately.

The goal is to understand what the code is doing.

3. Power Query Steps Are M Code

When you perform transformations, Power Query creates individual steps.

For example:

Source

Changed Type

Removed Columns

Filtered Rows

Renamed Columns

Each step represents a transformation.

The formula bar can show the M expression behind the selected step.

This is one of the easiest ways to learn M.

Instead of starting with programming syntax, perform a transformation and observe the code Power Query generates.

4. The let and in Structure

A typical Power Query query looks like:

let
    Source = Excel.Workbook(File.Contents("Sales.xlsx")),
    SalesData = Source{[Item="Sales"]}[Data],
    ChangedType = Table.TransformColumnTypes(
        SalesData,
        {{"Sales", type number}}
    )
in
    ChangedType

Don't worry about every part yet.

The important idea is:

let
    Step 1,
    Step 2,
    Step 3
in
    Final Step

Power Query performs the steps in sequence and returns the step specified after in.

5. Understanding a Step

Consider:

FilteredRows =
    Table.SelectRows(Source, each [Sales] > 5000)

There are two important parts.

Step name

FilteredRows

This is the name of the step.

Transformation

Table.SelectRows(Source, each [Sales] > 5000)

This tells Power Query what transformation to perform.

6. Common M Functions

You will frequently encounter functions such as:

Table.SelectRows

Used to filter rows.

Table.SelectRows(Source, each [Sales] > 5000)

Table.RemoveColumns

Used to remove columns.

Table.RemoveColumns(Source, {"Region"})

Table.RenameColumns

Used to rename columns.

Table.RenameColumns(Source, {{"Sales", "Revenue"}})

Table.AddColumn

Used to create a new column.

Table.AddColumn(Source, "Profit", each [Sales] - [Cost])

These functions become much easier to understand once you recognize the pattern.

7. The each Keyword

You will see each frequently in Power Query.

For example:

each [Sales] > 5000

It means the expression should be evaluated for each row.

Another example:

each [Quantity] * [Price]

This calculates the result for every row.

If:

Quantity = 10

Price = 500

then:

10 × 500 = 5000

8. Column References

In M, a column can commonly be referenced like:

[Sales]

For example:

each [Sales] * 1.18

This calculates a value using the Sales column.

For column names containing spaces or special characters, you may see syntax such as:

[#"Sales Amount"]

You don't need to memorize this immediately. Power Query usually generates the correct syntax for you.

9. M Is Case-Sensitive

M language is case-sensitive.

For example:

Sales

and

sales

are not necessarily treated as the same identifier.

Similarly, function names need to be written correctly.

This is one reason copying and modifying existing M code is often easier than writing everything from scratch.

10. Editing M Code

You can modify M code directly using:

View → Advanced Editor

For example, you might change:

[Sales] > 5000

to:

[Sales] > 10000

This changes the filtering condition.

However, don't edit M code blindly.

If you change a step incorrectly, later steps may stop working.

11. Why Learn M If Power Query Has a GUI?

The interface is enough for many common transformations.

But M becomes useful when:

• A transformation isn't easily available through the interface

• You need dynamic logic

• You want to reuse code

• You need parameters

• You want to create custom functions

• You need more control over transformations

• You want to troubleshoot query errors

Think of the GUI as the easy way to build transformations.

M gives you deeper control over those transformations.

12. M vs DAX

This is an important distinction.

Power Query M

Used mainly for:

• Extracting data

• Cleaning data

• Transforming data

• Combining data

• Preparing data before loading

DAX

Used mainly for:

• Measures

• Calculated columns

• Calculations within the data model

• Analysis and reporting

For example:

Removing unnecessary columns:

Use Power Query.

Creating:

Total Sales = SUM(Sales[SalesAmount])

Use DAX.

🧪 Practical Exercise

Take a simple Sales table containing:

Product Quantity Price

Laptop 2 50000

Mouse 5 1000

Keyboard 3 2000

In Power Query:

1. Change the data types.

2. Add a Revenue column.

3. Filter Revenue greater than 5000.

4. Rename the Revenue column.

5. Open the formula bar.

6. Look at the M code generated for each step.

7. Open Advanced Editor.

8. Identify the let and in sections.

🎯 Key Takeaway

M is the language behind Power Query transformations.

You don't need to learn all of M at once.

Start by performing transformations through the interface, then study the M code Power Query generates.

This approach makes M much easier to learn because you already understand what the code is supposed to accomplish.

Double Tap ❤️ For More

𝟰 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗖𝗼𝗱𝗶𝗻𝗴 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘😍

✅ 100% FREE YouTube Learning
✅ Beginner-Friendly Programming Tutorials
✅ Learn Popular & In-Demand Tech Skills
✅ Study at Your Own Pace
✅ Build a Strong Foundation for Coding Interviews

🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗔𝗟𝗟 𝟰 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 👇:-

https://pdlink.in/3WcuMvi

⚡ Your coding journey can start today—completely FREE!

𝗧𝗔𝗧𝗔 𝗙𝗥𝗘𝗘 𝗠𝗜𝗖𝗥𝗢-𝗜𝗡𝗧𝗘𝗥𝗡𝗦𝗛𝗜𝗣𝗦 𝟮𝟬𝟮𝟲! 🎓

✅ 100% FREE Learning Experience
✅ Short Programs — Approximately 4–6 Hours
✅ Practical, Real-World Tasks
✅ Self-Paced & Online
✅ Certificate Opportunities for Your Resume & LinkedIn
✅ No Traditional Interview Process

🔗 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗧𝗔𝗧𝗔 𝗙𝗥𝗘𝗘 𝗠𝗶𝗰𝗿𝗼-𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽𝘀👇:-

https://pdlink.in/4yWgaPn

⚡ Don't wait for your first job to start gaining practical experience!

📊 Power BI Learning Roadmap — Part 4

Topic 13: Custom M Functions

After learning the basics of M language, the next important concept is Custom Functions.

Custom functions allow you to write a transformation once and reuse it multiple times.

This becomes very useful when you have many files or tables that need the same transformation logic.

1. What Is a Custom Function?

A custom function is a reusable piece of M code that accepts an input and returns an output.

For example, suppose you frequently need to calculate:

Price + 18% GST

Instead of writing the calculation separately for every query, you can create a function.

Conceptually:

Input: Price

Function: Price × 1.18

Output: Price including GST

If the input is ₹1,000:

1000 × 1.18 = 1180

2. Why Use Custom Functions?

Imagine you receive 12 monthly files.

Every file requires the same transformations:

• Remove unnecessary columns

• Change data types

• Clean product names

• Calculate revenue

• Standardize dates

You could repeat the same steps manually.

But with a custom function, you can define the transformation once and apply it to each input.

This saves time and makes your Power Query solution easier to maintain.

3. Basic Function Structure

A simple M function can look like:

(price as number) as number =>
    price * 1.18

Here:

price is the input.

as number specifies the input data type.

price * 1.18 is the calculation.

The final as number specifies the output type.

4. Using the Function

Suppose your function is called:

AddGST

You could use it like:

AddGST(1000)

Result:

1180

Another example:

AddGST(5000)

Result:

5900

The same logic can be reused with different values.

5. Functions Can Accept Multiple Inputs

A function doesn't have to accept only one parameter.

For example:

(price as number, taxRate as number) as number =>
    price * (1 + taxRate)

Now you can provide both the price and tax rate.

For example:

CalculatePrice(1000, 0.18)

Result:

1180

And:

CalculatePrice(1000, 0.05)

Result:

1050

The function becomes more flexible.

6. Custom Functions with Tables

Custom functions become especially powerful when working with tables.

Imagine you receive these files:

January.xlsx

February.xlsx

March.xlsx

April.xlsx

Each file contains raw sales data.

You want every file to go through the same cleaning process.

You can create a function such as:

TransformSalesFile

The function can:

1. Receive a file.

2. Extract the required table.

3. Remove unnecessary columns.

4. Rename columns.

5. Change data types.

6. Clean the data.

7. Return the transformed table.

You can then apply the function to multiple files.

7. Custom Function Workflow

A common workflow is:

Files → Function → Cleaned Tables → Combined Dataset

For example:

Jan.xlsx

Feb.xlsx

Mar.xlsx

Each file is passed through the same transformation function.

The result is a consistent dataset ready for analysis.

This is one of the most useful applications of custom functions in Power Query.

8. Creating a Function from an Existing Query

Power Query can also help you create a function from an existing query.

A common approach is:

1. Build the transformation for one sample file.

2. Turn the query into a function.

3. Define the input parameter.

4. Apply the function to other files.

This is much easier than manually writing the entire function from scratch.

9. Function vs Parameter

These two concepts can sometimes be confused.

Parameter

A parameter stores a value that can change.

Example:

MinimumSales = 5000

Function

A function performs an operation using one or more inputs.

Example:

CalculateProfit(Sales, Cost)

So:

Parameter = value

Function = reusable logic

10. Real-World Example

Suppose your company receives employee files from different branches.

Each file contains:

Name Salary Department

Rahul 50000 Sales

Priya 60000 HR

Every file needs the same cleaning:

• Remove blank rows

• Standardize department names

• Change Salary to number

• Trim employee names

Instead of repeating these steps for every branch, create:

CleanEmployeeFile

Then apply that function to every branch file.

If you later modify the cleaning logic, you can update the function rather than rebuilding the transformation for every file.

11. Important: Functions Need Consistent Inputs

A custom function works best when the input structure is predictable.

For example, if your function expects:

• Name

• Salary

• Department

but one file contains:

• EmployeeName

• Pay

• Team

the function may fail.

Therefore, when working with multiple files, make sure the files have a consistent structure or build logic to handle differences.

12. Common Mistakes

Mistake 1: Creating a function for everything

Not every transformation needs a custom function.

Use functions when the same logic needs to be reused.

Mistake 2: Wrong input data type

If your function expects a number but receives text, the function may produce an error.

Mistake 3: Inconsistent file structures

Different column names or missing columns can break a function.

Mistake 4: Not testing the function

Always test your function with a few inputs before applying it to hundreds of files.

🧪 Practical Exercise

Create a simple function that calculates profit margin.

Use:

• Sales

• Cost

Formula:

(Sales - Cost) / Sales

For example:

Sales = ₹10,000

Cost = ₹7,000

Profit Margin:

(10000 - 7000) / 10000

= 0.30

= 30%

Then test your function with different Sales and Cost values.

After that, think about how you could use the same concept to transform multiple monthly files.

🎯 Key Takeaway

Custom M Functions allow you to write transformation logic once and reuse it many times.

They are particularly valuable when working with multiple files that require the same cleaning and transformation steps.

Double Tap ❤️ For More

𝗠𝗜𝗖𝗥𝗢𝗦𝗢𝗙𝗧 𝗙𝗥𝗘𝗘 𝗗𝗔𝗧𝗔 𝗦𝗖𝗜𝗘𝗡𝗖𝗘 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓

Microsoft offers valuable FREE learning resources to help you develop in-demand technical skills! 💻

✅ 100% FREE Learning Resources
✅ Beginner-Friendly Tutorials
✅ Self-Paced Learning
✅ Practical Skills for Real-World Projects
✅ Useful for Data Analytics & Data Science Career Preparation

🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗔𝗟𝗟 𝟰 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 👇:-

https://pdlink.in/4k6ylNq

⚡ Your Data Science journey can start today — completely FREE!

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 📊

Build a strong foundation in Data Science .Explore Machine Learning and Deep Learning

Eligibility :- Students • Freshers • Working Professionals

📅 Date:- 23 October 2026
⏰ Time:- 7:00 PM–9:00 PM IST
💻 Mode:- Online

🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :-

https://pdlink.in/4i8MKGn

📢 Share this opportunity with your friends and classmates.

🚀𝗠𝗔𝗦𝗧𝗘𝗥 𝗘𝗫𝗖𝗘𝗟 𝗙𝗢𝗥 𝗙𝗥𝗘𝗘 𝗜𝗡 𝟮𝟬𝟮𝟲! 📊

Discover 4 FREE Websites to learn Microsoft Excel from beginner to advanced level! 🎓

🔥 FREE Learning Resources
🔥 Beginner to Advanced Tutorials
🔥 Practical Examples & Exercises
🔥 Learn Anytime, Anywhere

🔗 𝗟𝗶𝗻𝗸👇:-

https://pdlink.in/4jDfcCP

⚡ One powerful skill can open doors to multiple career opportunities!

𝗠𝗔𝗦𝗧𝗘𝗥 𝗦𝗤𝗟 𝗙𝗢𝗥 𝗙𝗥𝗘𝗘 𝗜𝗡 𝟮𝟬𝟮𝟲 📊

🔥 Essential Skill for Data Analytics
🔥 Useful for Data Science & Backend Development
🔥 Practice Real-World Database Queries
🔥 Strengthen Your Technical Interview Skills

🎯 Perfect For:- Students | Freshers | Job Seekers | Working Professionals

🔗 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗔𝗟𝗟 𝟱 𝗙𝗥𝗘𝗘 𝗦𝗤𝗟 𝗪𝗲𝗯𝘀𝗶𝘁𝗲𝘀 👇:-

https://pdlink.in/4yOlWlh

📌 SAVE this post & SHARE it with friends preparing for tech careers!

📊 Power BI — Essential DAX Functions with Examples

DAX is used for calculations in the Power BI data model. These are the functions you should learn first.

1. SUM

Adds values from a column.

Total Sales = SUM(Sales[SalesAmount])

Use it when you need the total sales amount.

2. AVERAGE

Returns the average value.

Average Sales = AVERAGE(Sales[SalesAmount])

3. COUNT

Counts numeric values.

Order Count = COUNT(Sales[OrderID])

4. COUNTROWS

Counts the number of rows in a table.

Total Orders = COUNTROWS(Sales)

This is often more useful than COUNT when you want to count records.

5. DISTINCTCOUNT

Counts unique values.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

If the same customer made 10 orders, they are counted only once.

6. MIN and MAX

Find the minimum and maximum values.

Minimum Sale = MIN(Sales[SalesAmount])

Maximum Sale = MAX(Sales[SalesAmount])

🔹 Logical Functions

7. IF

Returns one result when a condition is TRUE and another when it is FALSE.

Sales Category =
IF(
    [Total Sales] >= 100000,
    "High",
    "Low"
)

8. SWITCH

Useful when you have multiple conditions.

Sales Rating =
SWITCH(
    TRUE(),
    [Total Sales] >= 500000, "Excellent",
    [Total Sales] >= 250000, "Good",
    [Total Sales] >= 100000, "Average",
    "Low"
)

For multiple conditions, SWITCH(TRUE()) is often cleaner than deeply nested IF statements.

9. AND / OR

Eligible =
IF(
    Sales[Amount] > 10000 &&
    Sales[Status] = "Completed",
    "Yes",
    "No"
)

&& means AND.

|| means OR.

🔹 Filter Functions

10. CALCULATE

One of the most important DAX functions.

It evaluates an expression under a modified filter context.

Online Sales =
CALCULATE(
    [Total Sales],
    Sales[Channel] = "Online"
)

This calculates Total Sales only for Online transactions.

Understanding CALCULATE is essential for advanced DAX.

11. FILTER

Creates a filtered table.

High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[SalesAmount] > 10000
    )
)

FILTER is especially useful when the filtering condition is more complex.

12. ALL

Removes filters from a table or column.

Total Sales All Regions =
CALCULATE(
    [Total Sales],
    ALL(Sales[Region])
)

This can be used to calculate a value against the overall total regardless of the Region filter.

13. REMOVEFILTERS

Explicitly removes filters.

Total Sales =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Sales[Region])
)

This is often clearer than using ALL when your intention is simply to remove a filter.

14. ALLEXCEPT

Removes filters except those specified.

Sales By Region =
CALCULATE(
    [Total Sales],
    ALLEXCEPT(Sales, Sales[Region])
)

Useful when you want to preserve a particular grouping while removing other filters.

🔹 Relationship Functions

15. RELATED

Retrieves a value from a related table.

Suppose:

Sales[ProductID]

is related to:

Products[ProductID]

You can write:

Product Category =
RELATED(Products[Category])

This is mainly used in calculated columns.

16. RELATEDTABLE

Returns the related rows from another table.

It is particularly useful when working from the one-side table toward the many-side table.

Example:

Product Sales =
SUMX(
    RELATEDTABLE(Sales),
    Sales[SalesAmount]
)

🔹 Iteration Functions

Functions ending in X are called iterator functions.

17. SUMX

Calculates an expression row by row and then adds the results.

Total Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[Price]
)

If a row has:

Quantity = 5

Price = 1000

Revenue for that row = 5000.

SUMX then adds the results across all rows.

18. AVERAGEX

Calculates an expression for each row and returns the average.

Average Revenue =
AVERAGEX(
    Sales,
    Sales[Quantity] * Sales[Price]
)

19. COUNTX

Counts the results of an expression.

Product Count =
COUNTX(
    Sales,
    Sales[ProductID]
)

🔹 Date & Time Functions

20. YEAR / MONTH / DAY

Sales Year = YEAR(Sales[OrderDate])

Sales Month = MONTH(Sales[OrderDate])

Sales Day = DAY(Sales[OrderDate])

Useful for extracting date components.

21. TODAY

Returns today's date.

Today Date = TODAY()

22. DATE

Creates a date.

Start Date = DATE(2026, 1, 1)

🔹 Time Intelligence

Once you have a proper Date table, these become extremely important.

23. TOTALYTD

Calculates year-to-date values.

YTD Sales =
TOTALYTD(
    [Total Sales],
    'Date'[Date]
)

24. TOTALMTD

Calculates month-to-date values.

MTD Sales =
TOTALMTD(
    [Total Sales],
    'Date'[Date]
)

25. SAMEPERIODLASTYEAR

Compares the current period with the same period last year.

Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

26. DATEADD

Moves a date context forward or backward.

Previous Month Sales =
CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, MONTH)
)

🔹 Ranking Functions

27. RANKX

Ranks values.

Product Rank =
RANKX(
    ALL(Products[ProductName]),
    [Total Sales],
    ,
    DESC
)

This ranks products from highest to lowest based on sales.

🔹 Useful Text Functions

28. CONCATENATE

Combines text values.

Customer Name =
CONCATENATE(
    Customers[FirstName],
    Customers[LastName]
)

However, the & operator is usually simpler:

Customer Name =
Customers[FirstName] & " " & Customers[LastName]

29. FORMAT

Formats a value as text.

Sales Label =
FORMAT([Total Sales], "₹#,##0")

Be careful: FORMAT returns text, not a numeric value.

🎯 Double Tap ❤️ For More

𝗧𝗢𝗣 𝟱 𝗙𝗥𝗘𝗘 𝗧𝗘𝗖𝗛 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝗜 𝗪𝗜𝗦𝗛 𝗜 𝗛𝗔𝗗 𝗧𝗔𝗞𝗘𝗡 𝗦𝗢𝗢𝗡𝗘𝗥! 💻🎓

Discover 5 FREE tech courses to kick-start your learning journey and strengthen your resume. 🚀

✅ Learn Valuable Technical Skills
✅ Beginner-Friendly Learning Resources
✅ Build Practical Knowledge
✅ Strengthen Your Resume & Portfolio
✅ Prepare for Tech Career Opportunities

💡 The best investment you can make in 2026 is upgrading your skills!

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :-

https://pdlink.in/4Bw1PL9

⚡ Start learning today. Your future self will thank you!

𝗔𝗜 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 | 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘😍

AI is changing the way we learn and work. Ready to take your first step?

- Learn the Skills, Hiring Trends, and Preparation Strategies That Matter

🎓 Students | 💼 Working Professionals | 💡 AI Enthusiasts

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :-

https://pdlink.in/4BAxISK

Date:- 31st October 2026 Time:- 5:00 PM (IST)

💫Share this opportunity with your friends and classmates.