DAX
A language for formula expressions is called DAX (Data Analysis Expressions). It is applicable to various BI and visualization tools. The complete code is maintained inside a function in DAX, which is also known as a functional language. There are two data types, such as numeric (includes integers, currency, decimals, etc) and other (includes string and binary objects), in DAX programming formulas.
Analysts can come up with new insights and find new ways to calculate data values with the aid of the DAX language.
Take a look at a few crucial DAX points that can aid in your comprehension of the idea.
- Because DAX is a functional language, every line of code is a function. A DAX expression that can be executed may include value references, nested functions, conditional statements, etc.
- The two main data types used in DAX formulas are numeric and non-numeric or others. Integers, decimals, currencies, and other data types fall under the numeric data type. While binary objects and strings make up the non-numeric data.
- The innermost function is evaluated first, followed by the outermost function, and so on. Because of this, creating a DAX formula is crucial.
DAX Importance in Power BI
It is only logical to wonder why learning DAX is essential for effectively using Power BI. As we've seen in previous articles, creating reports utilizing Power BI's data import, transformation, and visualization features is a simple process. To build a good report with all the access data, a user must have a working knowledge of Power BI Desktop. But you need DAX if you wish to advance and add sophisticated calculations to your Power BI reports.
Let's imagine you need to examine year-over-year growth or sales or you want to create a visual to assess growth percentages across several states in a nation. In most cases, the data fields that you import in a data table are insufficient for such uses. You must use the DAX language to create new measures for this. By doing this, you can develop new metrics, employ them in the development of innovative data visualizations, and gain novel insights into the data. You can have appropriate solutions for business challenges that you might overlook using the standard method of analysis when you have such unique insights into the data. As a result, DAX makes utilizing Power BI for data analysis a clever and clever move.
Working of DAX
DAZ mainly works in three fundamental concepts;
- Syntax
- Context
- Functions
Syntax
The first and most important step in learning any language is to divide it into clear components and comprehend those components. And for this reason, we research a language's syntax. basically, the syntax makes up the formula.
Example:
Total Profit = SUM (Sales[Profit])
- In the above example, Total Profit is the measure name.
- The equal sign '=' operator indicates the beginning of the formula.
- The SUM function in DAX adds up all the numerical values in the Sales[Profit] column.
- Parentheses () surround an expression containing one or more arguments. And all function requires at least one argument. An argument passes a value to a function.
- The reference table Sales.
- The referenced column [Profit] is in the Sales table. With this argument, the SUM function knows on which column to apply a SUM.
Context
One of the fundamental DAX ideas in context. Row context and Filter context are the two categories that it falls under. The present row is best thought of as the Row-Context. It is applicable whenever a formula has a function that identifies a certain row in a table by using the filters. It's a little trickier to comprehend the Filter context than the Row context. The Filter-Context is best understood as a set of filters used in a calculation. Instead of the Row-Context, there is a Filter-Context. Instead, it makes use of the former in addition.
Functions
A DAX function is a preset formula that computes using parameters that contain values. A function's arguments, which can include a column reference, numbers, text, constants, another formula or function, or a logical value like TRUE or FALSE, must be given in a specific order. Each function applies a certain operation on the values contained within an argument. A DAX formula can contain several arguments.
Types of the DAX Functions
- Date and Time Functions
The Date and Time Functions carry out calculations on date and time values.
- CALENDAR
- CALENDARAUTO
- DATE
- DATEDIFF
- DATEVALUE
- DAY
- EOMONTH
- HOUR
- MINUTE
- MONTH
- NOW
- SECOND
- TIME
- TIMEVALUE
- TODAY
- WEEKDAY
- WEEKNUM
- YEAR
- YEARFRAC
DATE(<year>, <month>, <day>)
2. Aggregate Functions
- MIN
This function returns the minimum numeric value in a column.
MIN(<column>)
- MAX
This function returns the maximum value in a column.
MAX(<column>)
- AVERAGE
This function returns the arithmetic mean of the values in a column.
AVERAGE(<column>)
- SUM
This function adds all the numbers in a column.
SUM(<column>)
3. Statistical Functions
These functions carry out statistical and aggregation functions on data values in a DAX expression in Power BI. The list of available statistical functions is given below.
- ADDCOLUMNS
- APPROXIMATEDISTINCTCOUNT
- AVERAGE
- AVERAGEA
- AVERAGEX
- BETA.DIST
- BETA.INV
- CHISQ.INV
- CHISQ.INV.RT
- CONFIDENCE.NORM
- CONFIDENCE.T
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- CROSSJOIN
- MAX
- MAXA
- MAXX
- MEDIAN
- MEDIANX
- MIN
- MINA
- MINX
4. Logical Functions
The logical functions are used to evaluate an expression or argument logically and return TRUE or FALSE if the condition is met or not.
- AND
- FALSE
- IF
- IFERROR
- IN
- NOT
- OR
- SWITCH
- TRUE
5. Text Functions
The text functions in Power BI are very similar to the string functions of Excel. These functions evaluate string values.
- BLANK
- CODE
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- EXACT
- FIND
- FIXED
- FORMAT
- LEFT
- LEN
- LOWER
- MID
- REPLACE
- REPT
- RIGHT
- SEARCH
- SUBSTITUTE
- TRIM
- UNICHAR
- UPPER
- VALUE
