Understanding DAX in Power BI

1. What is DAX (Data Analysis Expressions)?

DAX is a powerful formula language used in Power BI for data modelling and analysis. It enables the creation of custom calculations, aggregations, and transformations in your reports.

2. Use of DAX in Power BI:

DAX is the backbone of Power BI, driving calculations, measures, and insights. From simple arithmetic operations to complex business logic, DAX empowers users to derive valuable information from their data.

3. Basic Syntax of DAX:

DAX syntax is similar to Excel formulas. A basic formula structure is:

Result = Expression

?4. Types of DAX Functions:

? Date Functions (e.g., TODAY(), YEAR())

? Aggregate Functions (e.g., SUM(), AVERAGE())

? Information Functions (e.g., ISBLANK(), ISTEXT())

? Logical Functions (e.g., IF(), AND())

? Trigonometric Functions (e.g., SIN(), COS())

? Statistical Functions (e.g., COUNTROWS(), MEDIAN())

? Text Functions (e.g., CONCATENATE(), LEFT())

? Table Manipulation Functions (e.g., FILTER(), RELATED())

? Window Functions (e.g., TOTALYTD(), RANKX())

?5. DAX Date Functions (Formula Examples):

- Total Sales YTD:

? TotalSalesYTD = TOTALYTD(SUM('Sales'[SalesAmount]), 'Date'[Date])

?- Previous Year Sales:

???? PrevYearSales = CALCULATE(SUM('Sales'[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date]))

?6. DAX Aggregate Functions:

- Total Units Sold:

? TotalUnitsSold = SUM('Sales'[UnitsSold])

7. DAX Information Function:

- Check for Blank Value

IsValueBlank = IF(ISBLANK('Table'[Column]), "Blank", "Not Blank")

8. DAX Logical Function:

- Discount Category:

?? DiscountCategory = IF('Sales'[Discount] > 0.1, "High", "Low")

9. DAX Trigonometric Function:

-Calculate Sine:

?? ?SineValue = SIN(30)

10. DAX Statistical Function:

- Median Sales:

???? MedianSales = MEDIAN('Sales'[SalesAmount])

?11. DAX Text Function:

-Combine First and Last Name:

??? FullName = 'Employee'[FirstName] & " " & 'Employee'[LastName]

12. DAX Table Manipulation Function:

-Filter Products with High Sales

? ?HighSalesProducts = FILTER('Products', 'Products'[Sales] > 1000)

13. DAX Window Function:

??? - Rank Products by Sales:

SalesRank = RANKX('Products', 'Products'[Sales], , DESC, Dense)

??DAX is the key to unlocking insights in Power BI. Mastering its functions empowers analysts to extract valuable information from data.