DAX Functions for Power BI

Functions in DAX perform various tasks such as data manipulation, aggregation, filtering, time intelligence, and custom calculations. They allow users to transform, analyze, and derive insights from data within Microsoft Power BI, Power Pivot, and SQL Server Analysis Services. There are various functions, some are given below:

COUNT function in Power BI

• COUNT:  It returns the total number of items in the column.

Syntax: COUNT(<column name>)

• COUNTROWS: It returns the number of rows in the table.

Syntax: COUNTROWS([<table name>])

• DISTINCTCOUNT:  It returns the number of distinct values in the values column.

Syntax: DISTINCTCOUNT(<column name>)

• COUNTA: It counts the number of items in a column, that is not empty i.e. occupied.

Syntax: COUNTA(<column name>)

• COUNTBLANK: It returns the number of columns that are blank

Syntax: COUNTBLANK(<column name>)

DATETIME function in Power Bi

• DATE: Gets the date in the Date-Time format.

Syntax: DATE(<year>, <month>, <day>)

• HOUR: Displays hours in the AM or PM format.

Syntax: HOUR(<datetime>)

• TODAY: It returns today’s date.

Syntax: TODAY() 

• WEEKDAY: It returns values between 1-7 where according to the day number in a week where 1 refers to Sunday and 7 is Saturday.

Syntax: WEEKDAY(<date>, <return_type>)

• NOW: It returns the current date and time in datetime format.

Syntax: NOW()

AGGREGATE Functions in Power BI

• MIN: Finds the minimum value in a given column or between two expressions.

Syntax: MIN(<column>) or MIN(<exp1>,<exp2>)

• MAX: Returns the maximum value in a given column or between two expressions.

Syntax: MAX(<column>) or MAX(<exp1>,<exp2>)

• SUM: The formula adds the values in a column to produce a total.

Syntax: SUM([column]) or SUM(<num1>,<num2>,<num3>,..)

• AVERAGE: It takes columns of data and returns the average.

Syntax: AVERAGE([column]) or AVERAGE(<num1>,<num2>,<num3>,..)

• MINX: Calculates the minimum value after evaluating each row expression in a table.

Syntax: MINX([number_1], [number_2] )

LOGICAL Functions in Power BI

• AND: Combines 2 expressions logically.

Syntax: AND(<logical1>,<logical2>)

• OR: This function performs the logical disjunction on 2 expressions.

Syntax: OR(<logical1>,<logical2>)

• NOT: Negates the given expression logically.

Syntax: NOT(<logic>)

• IF: It checks IF a criterion is true and returns one value if it is, and another value if it is not.

Syntax: IF(<logical_test>,<value_if_true>, value_if_false)

MATH Functions in Power BI

• ABS: Returns the absolute value.

Syntax: ABS([number])

• FACT: Returns the factorial of the number.

Syntax: FACT([number])

• EXP: Calculates the exponential value of a number.

Syntax: EXP([Number])

• ROUND: Rounds a number to a specified number of decimal places.

Syntax: ROUND([Number], [NumDigitsAfterDecimal])

• POWER: Raises a number to a specified power.

Syntax: POWER([Number], [Power])

• LOG: Calculates the logarithm of a number to a specified base.

Syntax: LOG([Number], [Base])

• SQRT: Calculates the square root of a number.

Syntax: SQRT([Number])

• MOD: Returns the remainder of a division operation.

Syntax: MOD([Dividend], [Divisor])

• SIN, COS, TAN: Calculates the trigonometric sine, cosine, and tangent of an angle, respectively.

Syntax: SIN([Angle]), COS([Angle]), TAN([Angle])

TEXT Functions in Power BI

• CONCATENATE: Joins two strings together.

Syntax : CONCATENATE(<text_1>,<text_2>,...)

• FIXED: Rounds off numbers to a given decimal.

Syntax: FIXED([Number], [NumDigitsAfterDecimal], [IncludeLeadingZeroes])

• REPLACE: Replace the characters with part of a string.

Syntax: REPLACE(<Text>, <Old_Text>, <NewText>)

• UPPER: Converts a text string to all uppercase letters.

Syntax: UPPER(<text>) 

• LOWER: Converts a text string to all lowercase letters.

Syntax: LOWER(<text>)

• SEARCH: Returns the number of the character at which a searched character or text string is first appeared.

Syntax: SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])

Statistical Functions in Power Bi

• MEDIAN: Returns the median value in a column.

Syntax: MEDIAN([Column])

• MODE: Returns the mode (most frequently occurring value) in a column.

Syntax: MODE([Column])

• AVERAGE: Calculates the average of a column of numeric values.

Syntax: AVERAGE([Column])

• STDEV.P: Calculates the population standard deviation of a column.

Syntax: STDEV.P([Column])

• STDEV.S: Calculates the sample standard deviation of a column.

Syntax: STDEV.S([Column])

• VAR.P: Calculates the population variance of a column. Example:

Syntax: VAR.P([Column])

Data Analysis Expressions (DAX) is a powerful language that empowers Power BI users to perform advanced calculations, create custom metrics, and gain deeper insights from their data. By leveraging DAX, analysts and business users can unlock the full potential of Power BI and make data-driven decisions. This article has provided a comprehensive overview of DAX concepts, illustrated their application through examples and screenshots, and highlighted optimization techniques to maximize performance. With this knowledge, readers can confidently explore and harness the capabilities of DAX in their Power BI projects.

Data analysis plays a crucial role in deriving insights and making informed decisions in today’s data-driven world. One of the key challenges in data analysis is performing complex calculations and aggregations efficiently. This is where Data Analysis Expressions (DAX) come into the picture.