Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. DAX language is very useful as it allows Data Analysts to perform advanced calculations and discover a hidden pattern in an unstructured dataset. The complete code of an expression is always a function or nested function with conditional statements, value references, formulas, loops, etc.

DAX formulas are made up of 3 core components:

**Syntax** – Proper DAX syntax is made up of a variety of elements, some of which are common to all formulas. When trying to understand a DAX formula, it is often helpful to break down each of the elements into a language you think and speak every day. So, this formula includes the following syntax elements:

Total Profit = SUM(Orders[Profit])

**Total Profit**is the measure name.**equals sign operator (=)**indicates the beginning of the formula.**SUM**adds up all of the numbers in the profit column.**Parentheses ()**surround an expression containing one or more arguments. All functions require at least one argument.**Orders**is the table referenced.**Profit**is a value passed to a function. The referenced column**[Profit]**is an argument with which, the SUM function knows the column on which it has to aggregate a SUM.

Simply put, you can read it as, **“***For the measure named Total Profit, calculate (=) the SUM of values in the [Profit] column in the Orders table.”*

**Functions** – DAX functions are predefined formulas that take some parameters. They perform calculations using arguments passed on to them. These arguments can be numbers, text, logical values, or other functions.

**Context** – DAX uses context to determine which rows should be used to perform a calculation. Context enables you to perform dynamic analysis, in which the results of a formula can change to reflect the current row or cell selection and also any related data. When we speak of context, this may refer to one of the two types; ** Row context** and

**.**

*Filter context***Row Context**: Row context does the calculation for each row and the values within that row. The row context tells DAX which row to use when it needs to obtain the value of a column. We can think of the row context as the “current row” in a table.**Filter Context**: The filter context is the set of filters applied to the data model before the evaluation of a DAX expression starts. It is the set of filters that are applied before that table arrives for use. Some initial level filter context is applied on the Power BI report page in the form of Slicer.

## Where are DAX Formulas Used in Power BI?

**Calculated Columns**: When we create a data model on the Power BI Desktop, we can extend a table by creating new columns. The content of the columns is defined by a DAX expression, evaluated row by row or in the context of the current row across that table. In data models for DAX, however, all calculated columns occupy space in memory and are computed during table processing. for example, we have created a new column for**Profit**in our**Orders**table using the DAX formula**Profit = Orders[Sales] - Orders[Cost]**. A new column named Profit is created as shown below.

**Measures**: Measures enable the user to create fields with aggregate values like sum, count, average, ratio, percentage, etc. The measures are created from the modeling tab of Power BI Desktop. When you define a formula for a measure in the formula bar, a Tooltip feature shows a preview of what the results would be for the total in the current context. For instance, we have created a measure**Total Profit**using the DAX formula**Total Profit =****SUM****(Orders[Profit]).**As we can see a new measure named**Total Profit**is created under the Orders table.

**Calculated Table**: A calculated table is a computed object, based on a formula expression, derived from all or part of other tables in the same model. Instead of querying and loading values into your new table's columns from a data source, a DAX formula defines the table's values.

## DAX Functions

In Power BI, we can use different function types to analyze data and create new columns and measures. It includes functions from different categories such as −

Aggregate

Text

Date

Logical

Mathematical

Information

**1. Aggregate Functions**: These functions calculate a (scalar) value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression.

**MIN****:**This DAX function returns the minimum numeric value in a column, or between two scalar expressions.

**Syntax**

MIN(<column>)

**Example**

Min_Profit = MIN(Orders[Profit])

**MAX****:**This DAX function returns the maximum value in a column, including any logical values and numbers represented as text.

**Syntax**

MAX(<column>)

**Example**

Max_Profit = MAX(Orders[Profit])

**SUM****:**This DAX function adds all the numbers in a column.

**Syntax**

SUM(<column>)

**Example**

Total_Sales = SUM(Orders[Sales])

**AVERAGE****:**This DAX function returns the arithmetic mean of the values in a column.

**Syntax**

AVERAGE(<column>)

**Example**

Average_Sales = AVERAGE(Orders[Sales])

**2. Text Functions**: With these functions, we can return part of a string, search for text within a string, or concatenate string values.

**CONCATENATE**: This DAX function joins two text strings into one.

**Syntax**

CONCATENATE(<text1>, <text2>)

**Example**

=CONCATENATE("First_Name ", "Last_Name")

**CONCATENATEX:**This DAX function joins the result of an expression evaluated for each row in a table.

**Syntax**

CONCATENATEX(<table>, <expression>, [delimiter])

**Example**

=CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)

**FIXED:**This DAX function rounds a number to the specified number of decimals and returns the result as text.

**Syntax**

FIXED(<number>, <decimals>, <no_commas>)

**Example**

=FIXED([PctCost],3,1)

**REPLACE:**This DAX function replaces part of a text string, based on the number of characters you specify, with a different text string.

**Syntax**

REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)

**Example**

=REPLACE('New Products'[Product Code],1,2,"OB")

**SEARCH:**This DAX function returns the number of characters at which a specific text string is first found.

**Syntax**

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

**Example**

=SEARCH("n", "printer")

The formula returns 4 because "n" is the fourth character in the word "printer."

**UPPER:**This DAX function returns a text string in all uppercase letters.

**Syntax**

UPPER (<text>)

**Example**

=UPPER(['New Products'[Product Code])

**3. DATE TIME FUNCTIONS: **These functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server.

**DATE:**This DAX function returns the specified date in Date-Time format.

**Syntax**

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

**Example**

=DATE(2019,12,17)

**HOUR:**This DAX function returns the specified hour as a number from 0 to 23 (12:00 A.M. to 11:00 P.M.).

**Syntax**

HOUR(<datetime>)

**Example**

=HOUR('Orders'[TransactionTime])

**TODAY****:**This DAX function returns the current date.

**Syntax**

TODAY()

**NOW:**This DAX function returns the current date and time in Date-Time format.

**Syntax**

NOW()

**EOMONTH:**This DAX function returns the date in Date-Time format of the last day of the month, before or after a specified number of months.

**Syntax**

EOMONTH(<start_date>, <months>)

**Example**

=EOMONTH("March 3, 2008",1.5)

**4. Logical Functions: **These functions return information about values in an expression. For example, the TRUE function lets us know whether an expression that we are evaluating returns a TRUE value.

**AND:**This DAX function performs logical AND(conjunction) on two expressions. For AND to return true, both conditions specified have to be fulfilled.

**Syntax**

AND(<logical argument1>,<logical argument2>)

**Example**

=IF(AND(10 > 9, -10 < -1), "All true", "One or more false"

Because both conditions, passed as arguments, to the AND function are true, the formula returns "All True".

**OR:**This DAX function performs logical OR(disjunction) on two expressions. For OR to return true, either of the two conditions specified has to be fulfilled.

**Syntax**

OR(<logical argument1>,<logical argument2>)

**Example**

=IF(OR(10 > 9, -10 >-1), "True", "False"

Because one of the conditions, passed as arguments, to the OR function is true, the formula returns "True".

**NOT:**This DAX function performs logical NOT (negation) on a given expression.

**Syntax**

NOT(<logical argument>)

**Example**

=NOT([CalculatedColumn1])

For each row in Calculated Column1, the NOT function returns the logical opposite of the given value.

**IF:**This DAX function tests a series of inputs for the one that fulfills the condition specified in the argument.

**Syntax**

IF(logical_test>,<value_if_true>, value_if_false)

**Example**

=IF([Calls]<200, "low", IF([Calls]<300, "medium", "high"))

**IFERROR:**This DAX function evaluates an expression and returns a specified value if the expression returns an error.

**Syntax**

IFERROR(value, value_if_error)

**Example**

=IFERROR(25/0,9999)

**4. Mathematical Functions: **Mathematical functions in DAX are similar to Excel's mathematical and trigonometric functions. However, there are some differences in the numeric data types used by DAX functions.

**ABS:**This DAX function returns the absolute value of the number given.

**Syntax**

ABS(<number>)

**Example**

=ABS([DealerPrice]-[ListPrice])

**EXP:**This DAX function returns the value of e raised to the power of the given number.

**Syntax**

EXP(<number>)

**Example**

=EXP([Power])

**FACT:**This DAX function returns the factorial of a number.

**Syntax**

FACT(<number>)

**Example**

=FACT([Values])

**LN:**This DAX function returns the natural log of the given number.

**Syntax**

LN(<number>)

**Example**

=LN([Values])

**LOG:**This DAX function returns the log with the base of the given number.

**Syntax**

LOG(<number>,<base>)

**Example**

All of the following return the same result, 2.

=LOG(100,10)

=LOG(100)

=LOG10(100)

**PI:**This DAX function returns the value of Pi.

**Syntax**

PI()

**POWER:**This DAX function returns the value of the first argument raised to the power of the second argument.

**Syntax**

POWER(<number>, <power>)

**Example**

=POWER(5,2)

**QUOTIENT:**This DAX function performs division and returns the integer part of the quotient.

**Syntax**

QUOTIENT(<dividend>, <divisor>)

**Example**

=QUOTIENT(5,2)

**SIGN:**This DAX function returns the sign of a given number.

**Syntax**

SIGN(<number>)

**Example**

=SIGN( ([Sale Price] - [Cost Price]) )

**SQRT:**This DAX function returns the square root of the given number.

**Syntax**

SQRT(<number>)

**Example**

=SQRT(25)

**6. INFORMATION FUNCTIONS**: These functions look at a table or column provided as an argument to another function and return whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value you reference contains an error.

**ISBLANK:**This DAX function returns TRUE or FALSE after checking whether a value is blank.

**Syntax**

ISBLANK(<value>)

**Example**

=IF( ISBLANK('CalculatedMeasures'[PreviousYearTotalSales]) , BLANK() , ( 'CalculatedMeasures'[Total Sales]-'CalculatedMeasures'[PreviousYearTotalSales] ) /'CalculatedMeasures'[PreviousYearTotalSales])

**ISNUMBER:**This DAX function returns TRUE or FALSE after checking whether a value is numeric.

**Syntax**

ISNUMBER(<value>)

**Example**

=IF(ISNUMBER(0), "Is number", "Is Not number")

**ISTEXT:**This DAX function returns TRUE or FALSE after checking whether a value is a text.

**Syntax**

ISTEXT(<value>)

**Example**

=IF(ISTEXT("text"), "Is Text", "Is Non-Text")

**ISNONTEXT:**This DAX function returns TRUE or FALSE after checking whether a value is non-text.

**Syntax**

ISNONTEXT(<value>)

**Example**

=IF(ISNONTEXT("text"), "Is Non-Text", "Is Text")

**ISERROR:**This DAX function returns TRUE or FALSE after checking whether a value is an error.

**Syntax**

ISERROE(<value>)

**Example**

=IF( ISERROR( SUM('ResellerSales_USD'[SalesAmount_USD]) /SUM('InternetSales_USD'[SalesAmount_USD]) ) , BLANK() , SUM('ResellerSales_USD'[SalesAmount_USD]) /SUM('InternetSales_USD'[SalesAmount_USD]) )

**Conclusion**

DAX in Power BI allows the designer to create complex measures and improve the visualizations. The Applied DAX with Power BI has a variety of functions that can be used to execute DAX queries on the Power BI Desktop. DAX library enables you to use many advanced Power BI features. For instance, with DAX, you can manipulate your data dynamically by using select, join, filter, and many more commands.

## コメント