Learn DAX functions in Power BI with Examples

DAX functions in Power BI: DAX (Data Analysis Expressions) is a powerful formula language used in Power BI for creating calculated columns, measures, and custom calculations. It consists of over 250 functions that can be categorized into various types based on their functionality. Below is a comprehensive overview of some key DAX functions along with examples.

DAX Functions in Power BI

• Aggregation Functions
• Logical Functions
• Filter Functions
• Time Intelligence Functions
• Text Functions
• Statistical Functions
• Mathematical Functions

1. Aggregation Functions

These functions perform calculations on a set of values and return a single value.

SUM

• Syntax: SUM(<column>)
• Example: TotalSales = SUM(Sales[SalesAmount])

AVERAGE

• Syntax: AVERAGE(<column>)
• Example: AverageSales = AVERAGE(Sales[SalesAmount])

COUNT

• Syntax: COUNT(<column>)
• Example: dax TotalOrders = COUNT(Sales[OrderID])

MIN

• Syntax: MIN(<column>)
• Example: MinSales = MIN(Sales[SalesAmount])

MAX

• Syntax: MAX(<column>)
• Example: MaxSales = MAX(Sales[SalesAmount])

DISTINCTCOUNT

• Syntax: DISTINCTCOUNT(<column>)
• Example:DAXCopy codeUniqueCustomers = DISTINCTCOUNT(Sales[CustomerID])

COUNTROWS

• Syntax: COUNTROWS(<table>)
• Example: TotalTransactions = COUNTROWS(Sales)

2. Logical Functions

These functions evaluate conditions and return Boolean values.

IF

• Syntax: IF(<logical_test>, <value_if_true>, <value_if_false>)
• Example: SalesCategory = IF(Sales[SalesAmount] > 1000, "High", "Low")

SWITCH

• Syntax: SWITCH(<expression>, <value>, <result>[, <value>, <result>]...)
• Example: dax RatingCategory = SWITCH(Products[Rating], 1, "Poor", 2, "Average", 3, "Good", "Unknown")

AND

• Syntax: AND(<logical1>, <logical2>)
• Example: IsHighSalesAndProfitable = AND(Sales[SalesAmount] > 1000, Sales[Profit] > 500)

OR

• Syntax: OR(<logical1>, <logical2>)
• Example: IsLargeOrder = OR(Sales[Quantity] > 50, Sales[SalesAmount] > 5000)

NOT

• Syntax: NOT(<logical>)
• Example: IsNotHighProfit = NOT(Sales[Profit] > 1000)

3. Filter Functions

These functions manipulate context to filter data.

CALCULATE

• Syntax: CALCULATE(<expression>, <filter1>, <filter2>, ...)
• Example: SalesIn2023 = CALCULATE(SUM(Sales[SalesAmount]), Sales[Year] = 2023)

FILTER

• Syntax: FILTER(<table>, <condition>)
• Example: HighValueSales = FILTER(Sales, Sales[SalesAmount] > 1000)

ALL

• Syntax: ALL(<table>[, <column>]...)
• Example: TotalSalesAllYears = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Year]))

REMOVEFILTERS

• Syntax: REMOVEFILTERS([<table>[, <column>]...])
• Example: SalesWithoutFilters = CALCULATE(SUM(Sales[SalesAmount]), REMOVEFILTERS(Sales[Region]))

KEEPFILTERS

• Syntax: KEEPFILTERS(<filter>)
• Example: SalesWithSpecificFilter = CALCULATE(SUM(Sales[SalesAmount]), KEEPFILTERS(Sales[Region] = "East"))

4. Time Intelligence Functions

These functions are used for date-based calculations.

TOTALYTD

• Syntax: TOTALYTD(<expression>, <dates>[, <filter>])
• Example: TotalSalesYTD = TOTALYTD(SUM(Sales[SalesAmount]), Dates[Date])

DATEADD

• Syntax: DATEADD(<dates>, <number_of_intervals>, <interval>)
• Example:
  dax SalesLastMonth = CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Dates[Date], -1, MONTH))

SAMEPERIODLASTYEAR

• Syntax: SAMEPERIODLASTYEAR(<dates>)
• Example: SalesLastYear = CALCULATE(SUM(Sales[SalesAmount]), SAMEPERIODLASTYEAR(Dates[Date]))

PREVIOUSMONTH

• Syntax: PREVIOUSMONTH(<dates>)
• Example: SalesPreviousMonth = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSMONTH(Dates[Date]))

PARALLELPERIOD

• Syntax: PARALLELPERIOD(<dates>, <number_of_intervals>, <interval>)
• Example: SalesParallelPeriod = CALCULATE(SUM(Sales[SalesAmount]), PARALLELPERIOD(Dates[Date], -1, YEAR))

5. Text Functions

These functions manipulate text strings.

CONCATENATE

• Syntax: CONCATENATE(<text1>, <text2>)
• Example: FullName = CONCATENATE(Employees[FirstName], Employees[LastName])

FORMAT

• Syntax: FORMAT(<value>, <format_string>)
• Example: dax FormattedSales = FORMAT(Sales[SalesAmount], "Currency")

LEFT

• Syntax: LEFT(<text>, <num_chars>)
• Example: FirstName = LEFT(Employees[FullName], 5)

RIGHT

• Syntax: RIGHT(<text>, <num_chars>)
• Example: LastThreeChars = RIGHT(Employees[FullName], 3)

LEN

• Syntax: LEN(<text>)
• Example: NameLength = LEN(Employees[FullName])

6. Statistical Functions

These functions perform statistical calculations.

AVERAGEX

• Syntax: AVERAGEX(<table>, <expression>)
• Example: AverageProfit = AVERAGEX(Sales, Sales[Profit])

RANKX

• Syntax: RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
• Example: dax ProductRank = RANKX(ALL(Products), Products[SalesAmount], , DESC)

MEDIAN

• Syntax: MEDIAN(<column>)
• Example: MedianSales = MEDIAN(Sales[SalesAmount])

MEDIANX

• Syntax: MEDIANX(<table>, <expression>)
• Example: MedianProfit = MEDIANX(Sales, Sales[Profit])

STDEV.P

• Syntax: STDEV.P(<column>)
• Example: SalesStdDev = STDEV.P(Sales[SalesAmount])

7. Mathematical Functions

These functions perform mathematical operations.

SUMX

• Syntax: SUMX(<table>, <expression>)
• Example: TotalRevenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

DIVIDE

• Syntax: DIVIDE(<numerator>, <denominator>[, <alternate_result>])
• Example: dax SalesPerUnit = DIVIDE(SUM(Sales[SalesAmount]), SUM(Sales[UnitsSold]), 0)

ROUND

• Syntax: ROUND(<number>, <num_digits>)
• Example: RoundedSales = ROUND(Sales[SalesAmount], 2)

POWER

• Syntax: POWER(<base>, <exponent>)
• Example: SquaredValue = POWER(Sales[Quantity], 2)

MOD

• Syntax: MOD(<number>, <divisor>)
• Example: Remainder = MOD(Sales[SalesAmount], 3)

Conclusion

DAX provides a robust set of functions that empower users to perform complex calculations and data manipulations in Power BI. The examples provided illustrate how these functions can be applied to real-world scenarios to derive meaningful insights from data. For a complete list of DAX functions and their detailed documentation, refer to the official Microsoft documentation.