Explain what is DAX?
DAX stands for Data Analysis Expressions, and it is the formula language Simply put, it is a collection of functions, operators, and constants that can be used in a formula or expression in Microsoft SQL Server Analysis Services, Power Pivot in Excel, and Power BI Desktop. To state it more simply, DAX helps you create new information from data already in your model expressions.
Microsoft Power BI — DAX Functions – Best Practices with one sample dataset
DataSet:
Power BI is widely used by many corporations to create interactive dashboards, track company performance and so much more. Power BI is loaded with so many features and makes the job easy for data analysts. In this article, I will show you how to use DAX Functions in Power BI.
To better understand DAX functions, you should have knowledge of Excel, and if you are familiar with the formulas of Excel, then you can grasp this concept very easily. If not, then don’t worry about it; I will explain everything in this tutorial. So let’s get started.
DAX (Data Analysis Expressions) is a data functional language which has several prebuilt functions, operators and methods which allow us to do calculations on data as well as transformations on it. As we spoke earlier regarding Excel, DAX is used in Power BI, Excel, Power Pivot and also in Analysis Services.
Measures:
Measures are useful when you do not want to compute values for each row, but, rather you want to aggregate values from many rows in a table. These calculations are measures.
We can create a measure in two ways. Either by clicking on New Measure in the Calculations group in Power BI Desktop Home Page or by clicking on 3 dots beside the table name in the Fields section and selecting New Measure.
We can write the DAX query inside the measure in the formula bar that appears after we click on New Measure. Syntax to write any measure is: Measure_name = DAX query.
Calculated Columns:
A calculated column is a column that you add to an existing table (in the model designer) and then create a DAX formula that defines the column’s values. Unlike Measures, calculated columns don’t compute the results of the logic dynamically. The calculated column executes the logic as soon as it is created and stores the result data in a separate column. Calculated columns you create appear in the Fields list just like any other field, but they’ll have a special icon showing their values are the result of a formula.
The calculated columns can be created in two ways::. Click on the table name in which we want to create a calculated column from the Fields tab tab and then select New Column from the Table Tools tab or click on the 3 dots beside the table name in the Fields section and select New Column from it.
DAX Aggregation Functions:
These functions are used to calculate a value (scalar) by aggregating the data, such as counting the number of records, the maximum value, or the minimum value. the sum of the values, an average of values, etc. Let’s deep dive into some of the aggregation functions in Power BI DAX.
1. COUNT:
The count function computes the number of cells in the corresponding column of the data that have non-blank values.
Syntax: COUNT(<Column Name>).
For eg:
2. COUNTBLANK:
CountBlank function computes the number of cells in the corresponding column of the data which have blank values.
Syntax: COUNTBLANK(<Column Name>)
For eg:
3. COUNTROWS:
The The CountRows function computes the number of rows in the corresponding table specified or the table returned as the output of some expression.
Syntax: COUNTROWS([<Table Name>])
For eg:
4. DISTINCTCOUNT:
DistinctCount function computes the number of distinct values in the corresponding column specified. If any blank values are present in the column DISTINCTCOUNT function includes the blank value as one distinct value. This function will not exclude blanks. There is a different function for this.
Syntax: DISTINCTCOUNT(<Column Name>)
5. AVERAGE:
The average function in DAX takes in any column that has numerical data, computes the average of the values present in the column, and returns it. If any blank value is present in the column, then the value is neither counted in the number of rows nor in the sum of values. If non-numerical data is present and the average can’t be computed, it returns the blank value.
Syntax: AVERAGE(<Column Name>)
For eg:
6. SUM:
The sum function in DAX takes in any column that has numerical data, computes the sum of the values present in the column, and returns it.
Syntax: SUM(<Column Name>)
For eg:
7. SUMX:
The Sumx function’s basic functionality is to compute the sum like sum function, but the SUMX function takes in two arguments. The first argument specifies the table or expression that returns the table, and the second argument is a column name that contains numerical data for calculating the sum. Blanks, logical values, and text are ignored.
Syntax: SUMX(<Table>,<Expression>)
8. MAX:
The Max function takes in either a column or two scalar expressions, computes the maximum,, and returns it. Here, true or false values are not supported.
Syntax: MAX(<Column Name>) or MAX(<Expression1>,<Expression2>)
For eg:
9. MINA:
MINA function takes in a column that has numerical data and computes the minimum values. If the column contains no values, MINA returns 0 (zero). Rows in the column that evaluate logical values, such as TRUE and FALSE,, are treated as 1 if TRUE and 0 (zero) if FALSE. Empty cells are ignored.
Syntax: MINA(<Column Name>)
For eg:
10. MIN:
The MIN function takes in a column that has not only numerical data but also text and date data types and computes the minimum of values. True and false values are not supported.
Syntax: MIN(<Column Name>) or MIN(<Expression1>,<Expression2>).
For eg:
DAX Filter Functions
Filter functions in DAX are one of the most complex and powerful functions in DAX. They are extensively used in PowerBI. The filtering functions let you manipulate data context to create dynamic calculations and data masks.
1. CALCULATE:
This function evaluates the expression in the applied filter context. Suppose you want to calculate the sum of the values of the corresponding table where the data belongs to the country of of Canada. We use the calculate function to calculate the sum of the values of the data filtered by the filter condition applied.
Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
For eg:
2. FILTER:
This function takes in the table, and the filter condition or expression by which the specified table is filtered and returns a table filtered by the specified condition.
Syntax: FILTER(<table>,<filter>)
For eg:
3. ALLEXCEPT:
This function Removes all context filters in the table except filters that have been applied to the specified columns.
Syntax: ALLEXCEPT(<table>,<column>[,<column>[,…]]).
I know this is a little complicated, but I will explain it in more depth. For example, if you have a table with some categories like first class, second class,, etc. in the Category column and you want to create a filter to show the sum of each category, you can use ALLEXCEPT. It will create a filter for the user,, and in the output, it will only show the final result for the selected category by the user.
For eg:
As you can see, there are filters available,, and the output will change as the user selects different options.
This was all about the Filter functions. In the next section, we will discuss and use Time Intelligence functions.
DAX Time Intelligence Functions
Time Intelligence functions in DAX are different from Time and Date functions. Time Intelligence functions enable you to manipulate data based on the time or date periods, such as including days, excluding days, adding days, getting time periods,, etc. and then build calculations or comparisons on top of it. Let’s have a look at some of the most used Time intelligence functions,, along with some of the best use cases for those functions.
1. DATEADD:
This function takes in three arguments, the the first being the column that has dates; the second argument represents the number of intervals; and the third argument specifies the type of interval (day, month, year, quarter). It returns a table of one column that contains dates which are either shifted forward or backward as specified by time interval.
Syntax: DATEADD(<dates>,<number_of_intervals>,<interval>)
For eg:
Date_ADD = DATEADD(‘Global-Superstore’[Order Date].[Date],-1,DAY)
In this example, ‘‘Global-Superstore [order date]. [Date]” is replacing <dates> in syntax, -1 is replacing <number of intervals>, and <interval> is being replaced by DAY. The following image shows the original date column and Date_ADD that we created in this section and their outputs.
2. DATESBETWEEN:
This function returns a table with one column that contains all the dates between the specified start date and end date range. If BLANK() is passed as the value for the start date, then the start date will be the earliest date in the dates column,, and if the last date is BLANK(),, then it will be the latest value.
Syntax: DATESBETWEEN(<dates>,<start_date>,<end_date>)
For eg:
Output:
3. NEXTDAY:
This function takes in a dates column as input and returns a table that contains a column of all dates from the next day based on the first date specified in the dates column.
Syntax: NEXTDAY(<dates>)
For eg:
Output:
4. PREVIOUSDAY:
This function takes in a dates column as input and returns a table that contains a column of all dates from the previous day based on the first date specified in the dates column.
Syntax: PREVIOUSDAY(<dates>)
For eg:
Output:
5. DATESYTD:
This function takes in a mandatory argument and an optional argument. The mandatory argument is the column that contains dates and the Optional column is a string literal that specifies the year-end date. Its default value is 31st December.
This function returns a table that contains a column of the dates for the year to date.
Syntax: DATESYTD(<dates> [,<year_end_date>])
For eg:
Output:
6. DATESMTD:
This function takes in an argument, which is the column that contains dates, and returns a table that contains a column of the dates for the month to date.
Syntax: DATESMTD(<dates>)
For eg:
Output:
DAX Date and Time Functions
Date and Time functions in DAX are different from Time Intelligence functions. We have seen that using the time intelligence functions,, we can manipulate data based on the date columns using time intelligence functions and manipulations. Whereas date and time functions are used to manipulate the data present in the date columns in Power BI,,. These functions use the Date Time data type, and the output is always returned in Date Time format. However, we can explicitly change the data format of a column in Power BI.
1. CALENDAR:
This function takes in start date and end date values as arguments and returns a table with a single column that contains the contiguous set of dates specified by the start date and end date range.
Syntax: CALENDAR(<start_date>, <end_date>)
For eg:
2. MONTH:
This function takes a date in date time or text format as an argument and returns month numbers 1 (January) to 12 (December).
Syntax: MONTH(<datetime>)
For eg:
3. DATEDIFF:
This function takes in three arguments: start date, end date, and interval (day, month, quarter, year, week, second, minute, and hour). The function computes the difference between the start date and the end date according to the specified interval.
Syntax: DATEDIFF(<start_date>,<end_date>,<interval>)
For eg:
Output:
As you can see, in the Date_Diff column, it’s showing the difference between the order date and the ship date.
4. WEEKDAY:
This function takes in a mandatory argument and an optional argument. The mandatory argument is the date in date time format and the optional argument is the return type. WeekDay returns a number from 1 to 7, identifying the day of the week of a date. By default, the day ranges from 1 (Sunday) to 7 (Saturday).
Syntax: WEEKDAY(<date>, <return_type>)
For eg:
5. WEEKNUM:
We all know that there are 52 weeks in a year. WEEKNUM function Returns the week number for the given date according to the return_type value. The week number indicates where the week falls numerically within a year. return type indicates when the week begins.
Syntax: WEEKNUM(<date>[, <return_type>])
For eg:
DAX Logical Functions
Logical functions are used in order to test whether a situation is true or false. Depending on the result of that test, you can then elect to do one thing or another. These decisions can be used to display information, perform different calculations, or perform further tests. Logical functions act upon an expression to return information about the values or sets in the expression. For example, you can use the IF function to check the result of an expression and create conditional results.
1. IF:
This function is used to check for a condition and if the condition is satisfied, return something else. This function takes two mandatory arguments: A conditional expression that returns the value if true, and an optional argument that returns the value if false.
Syntax: IF(<logical_test>, <value_if_true>[, <value_if_false>])
For eg:
Output:
2. AND:
This function represents the same functionality as the AND operator, but as a function. Checks whether both arguments are true and returns TRUE if both arguments are true. Otherwise, it returns false.
Syntax: AND(<logical1>,<logical2>)
For eg:
Output:
3. OR:
This function represents the same functionality as the OR operator, but as a function. Checks whether one of the arguments is true to return TRUE. The function returns false if both arguments are false.
Syntax:OR(<logical1>,<logical2>)
For eg:
Output:
4. COALESCE:
This function returns the first expression that does not evaluate to blank. If all expressions evaluate as blank, BLANK is returned.
Syntax: COALESCE(<expression>, <expression>[, <expression>]…)
For eg:
Output:
5. SWITCH:
This function is like Switch case statements in a programming language. Switch Evaluates an expression against a list of values and returns one of the multiple possible result expressions.
Syntax: SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
For eg:
Output:
DAX Text Functions
Text functions are those which are used to manipulate textual data, such as trimming, searching or substituting strings, converting to upper and lower case, concatenating strings, etc. DAX includes a set of text functions based on the library of string functions in Excel, but which have been modified to work with tables and columns in tabular models.
1. CONCATENATE:
This function takes in two arguments, which are textual data and concatenated data. It adds the contents of the second argument to the first and returns it.
Syntax: CONCATENATE(<text1>, <text2>)
For eg:
Output:
2. UPPER, LOWER:
The UPPER function takes in text or any textual column and converts the text into upper case.
The LOWER function takes in text or any textual column and converts the text into lowercase.
Syntax: UPPER (<text>) , LOWER (<text>)
For eg:
Output:
3. LEN:
This function takes in any text or text column and returns the number of characters in it.
Syntax: LEN(<text>)
For eg:
Output:
4. TRIM:
The trim function basically removes the trailing white spaces. It doesn’t remove the white spaces present between the words, but if any white spaces are found at the beginning of the string or end, they are removed.
Syntax: TRIM(<text>)
For eg:
Output:
The above are useful and regular DAX functions with examples. Practice the above-mentioned functions,, and do share your comments and feedback if you find them useful.
Thanks for reading!!
Data Analytic Group 