Summary of DAX categories of functions
Introduction to DAX
When you create a Pivot Table report using PowerPivot, you are usually joining several tables together to analyze the data in all the tables and generate results from all the information. When you link tables together in Pivots, you create what is called a Data Model and when you are working in PowerPivot you are working directly in this Data Model. Creating formulas in the Data Model, requires you to use a specialized language and syntax. The language you use to create formulas in a Data Model Is DAX. A significant difference between a DAX Function and an Excel function is that DAX Functions will always refer to a complete row, column, or table. On the other hand, Excel Functions refer to a single cell or range of cells. This allows for DAX Functions to return an entire table of results. In Pivots, you can use advanced formulas called Measures. They can only be created after a PivotTable or PivotChart has been added to a workbook. They will provide you with results dynamically and are dependent upon any filters that have been applied to the PivotTable or PivotChart. For example, if you wanted to find the number of products that a single warehouse has that are worth over $500, you could use a measure to find that information. Measures are often created using DAX. Variables can also be used in a formula created with DAX which increases the flexibility of the formula. By declaring a Variable first, the Variable name can be inserted in more complex expressions to make them more readable. When a Variable is being defined, the definition begins with an equals sign followed by “var”. The Variable name is then inserted followed by an expression. The syntax is as follows: VAR <variable name> = <expression>. The results of the expression are stored as a Named Variable. Once the Named Variable is declared, it can be used in many other expressions. While there are lots of DAX Functions that you can choose from, each function will be classified under one of the following categories:
Data and Time functions: Functions of this type are used to manipulate date and time values. As such, they are like the data and time functions that can be used in Excel.
Filter functions: These functions are used to manipulate data and filter it dynamically.
Information functions: This type of function is used to scan the values inside a cell range and match them against an expected data type.
Logical functions: Typically, these functions are used to validate expressions and values, and then work with other data that is based upon the evaluation.
Math and Trigonometric functions: Functions of this type are used to perform mathematical calculations.
Statistical functions: These functions are used to generate statistical data such as minimum and maximum values, as well as averages.
Time Intelligence functions: This type of function is used to manipulate data using time periods. It can be used to compare data of one time-period against another.
Aggregate Functions are also more powerful in DAX. The standard Aggregate Functions in Excel can be used in Excel spreadsheets, Pivot Tables, and PowerPivot Tables. DAX Aggregate Functions address many of the shortcomings of standard functions by providing a means to aggregate data across columns and tables instead of just cells and groups of cells. Below you will see the types of Aggregate Functions that are available and what they do:
AVERAGE | This function returns an average of all the numerical data in a column |
AVERAGEA | This function returns an average of all the numerical data in a column, but it can also work with non-numerical data as well. |
COUNT | This function returns a count of the number of cells in a column that contain numerical data. |
COUNTA | This function returns a count of the number of cells in a column that contain any data (numerical or otherwise). |
MIN | This function returns the smallest number value that was found in a column. |
MINX | Returns the smallest value from a set of expressions evaluated over a table. |
MAX | This function returns the largest number value that was found in a column. |
MAXX | Returns the largest value from a set of expressions evaluated over a table. |
SUM | This function will add and display the total of all numbers in a column. |
In addition to these functions, there are additional functions that are available in the DAX language only. These functions include the following. AVERAGEX Averages a set of expressions evaluated over a table. COUNTAX Counts a set of expressions evaluated over a table. COUNTX Counts the total number of rows in a table. COUNTROWS Counts the number of rows returned from a nested table function, such as filter function. SUMX Returns the sum of a set of expressions evaluated over a table.
Creating PivotTables
You have been given the raw transactional data for the daily sales numbers of your sales staff. To determine the total sales numbers for each sales associate, you would like to create a PivotTable using this data.
1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.
https://github.com/alcadelina/Excel-
2. Use your cursor to select cells A4:E40:
3. Next, click Insert → PivotTable:
4. The Create PivotTable dialog box is now displayed. The data range that you previously selected is shown within the Table/Range text box:
5. You want this new PivotTable to be inserted into the current worksheet, so click the Existing Worksheet radio button:
6. Inside the Location text box, click the range picker button:
7. Use your cursor to select cell H4:
8. Press Enter to apply the new location. Back at the Create PivotTable dialog box, click OK to apply the new settings:
9. The PivotTable is now added to the current worksheet in the location that you previously set:
10. In the Pivot Table Fields task pane, click the Last Name, Daily Sales and Bonus field checkboxes:
11. You will see that the daily sales and bonuses have been summarized by Sales Representative:
12. To improve the number formatting, click the Sum of Daily Sales drop-down arrow in the Values area and select Value Field Settings:
13. Now click Number Format:
14. In the Format Cells dialog box, click Currency, then click OK:
15. Click OK in the Value Field Settings dialog box:
16. Repeat the steps for the Bonus column. Once complete, the daily sales and bonus values should be formatted correctly:
17. Save the current workbook as Activity 5-1 Complete and then close Microsoft 365 Excel to complete this exercise.
18. Now, you can check out an example of a completed document in the link below:
No comments:
Post a Comment