Introduction:

DAX, or Data Analysis Expressions, is a formula language and calculation engine used primarily in Microsoft Power BI, as well as other Microsoft tools like Excel Power Pivot and Analysis Services Tabular models. DAX is designed to perform data modeling and calculations on data stored in tables and columns. It allows users to create custom calculations, aggregations, and business metrics for their data, enabling more advanced data analysis and reporting capabilities.

DAX Functions:

1. SUM:
'SUM([Sales])' calculates the sum of sales values in a column. For example, if you have a sales table with a "Sales" column, this DAX formula would give you the total sales amount.

2. AVERAGE:
'AVERAGE([Revenue])' calculates the average revenue from a "Revenue" column. It adds up all the revenue values and divides by the number of rows.

3. COUNT:
'COUNT([Customers])' counts the number of customers in a "Customers" column. It provides the total count of unique customer IDs.

4. MAX and MIN::
'MAX([Profit])' returns the highest profit value in the "Profit" column, while
'MIN([Temperature])' gives you the lowest temperature recorded in the "Temperature" column.

5. IF:
Suppose you want to categorize sales into "High" or "Low" based on a threshold:

IF([Sales] > 1000, "High", "Low")

This DAX expression checks if the sales value is greater than 1000. If it is, it returns "High"; otherwise, it returns "Low."

6. CALCULATE:
If you want to calculate total sales for the year 2023, you can use CALCULATE with a filter:

CALCULATE([Total Sales], FILTER('Date', 'Date'[Year] = 2023))

This formula calculates the total sales, but only for the year 2023 by applying a filter on the 'Date' table.

7. FILTER:
The FILTER function is used to filter a table or a column based on a specified condition.

High Sales = FILTER(Sales, Sales[Amount] > 1000)

This DAX formula filters the "Sales" table to include only rows where the sales amount is greater than 1000.

8. ALL:
The ALL function removes filters from a table or column, returning the entire table or column.

Total Sales = CALCULATE(SUM(Sales[Amount]), ALL('Date'))

This DAX formula calculates the total sales across all dates, even if there are filters applied to the "Date" table.

9. KEEPFILTERS:
The KEEPFILTERS function preserves existing filters on a table or column while applying new filters.

Filtered Sales = CALCULATE(SUM(Sales[Amount]), 'Date'[Year] = 2023, KEEPFILTERS('Product'[Category] = "Electronics"))

This DAX formula calculates the total sales for the year 2023 while preserving any existing filters on the "Product" category.

10. REMOVEFILTERS:
The REMOVEFILTERS function temporarily removes all filters from a table or column for the duration of the calculation.

Total Sales = CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS('Date'))

This DAX formula calculates the total sales without any filters applied to the "Date" table.

11. SAMEPERIODLASTYEAR
Returns a table of dates for the same period in the previous year.

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

This function is used for year-over-year comparisons.

12. CALENDAR:
Generates a table of dates within a specified date range.

Custom Calendar = CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31))

This function can be used to create a custom date table.

13. TODAY and NOW:
TODAY returns the current date, while NOW returns the current date and time.

Current Date = TODAY()
Current Date and Time = NOW()

14. YEAR, MONTH, and DAY:
These functions extract the year, month, or day component from a date.

Order Year = YEAR('Sales'[Order Date])
Order Month = MONTH('Sales'[Order Date])
Order Day = DAY('Sales'[Order Date])

15. CALENDARAUTO:
Automatically creates a date table based on date columns in your data model.

CalendarAuto = CALENDARAUTO()

This DAX function generates a date table based on date columns present in your dataset.

16. DATEDIFF:
Calculates the difference between two dates in terms of a specified interval (e.g., days, months).

Days Between Orders = DATEDIFF(MIN('Orders'[Order Date]), MAX('Orders'[Ship Date]), DAY)

TThis DAX formula calculates the number of days between the earliest order date and the latest ship date in the "Orders" table.