Category: excel Dax

Conditional Values and Testing for Errors ”; Previous Next You can use DAX functions to test the values in the data that result in different values based on a condition. For e.g., you can test the yearly sales amount and based on the result, label resellers either as Preferred or Value. You can also use DAX functions for checking the range or the type of values, to prevent unexpected data errors from breaking calculations. Creating a Value Based on a Condition You can use nested IF conditions to test values and generate new values conditionally. Following DAX functions are useful for conditional processing and conditional values − IF (<logical_test>,<value_if_true>, [<value_if_false>]) − Checks if a condition is met. Returns one value if the condition is TRUE and returns another value if the condition is FALSE. Value_if_false is optional, and if omitted and the condition is FALSE, the function returns BLANK (). OR (<logical1>,<logical2>) − Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE. CONCATENATE (<text1>, <text2>) − Joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference, if the column contains appropriate values. Testing for Errors within a DAX Formula In DAX, you cannot have valid values in one row of a calculated column and invalid values in another row. That is, if there is an error in any part of a calculated column, the entire column is flagged with an error and you must correct the DAX formula to remove the errors that result in invalid values. Some common errors in DAX formulas are − Division by zero. Argument to a function is blank while the expected argument is numeric value. You can use a combination of logical and information functions to test for errors and always return valid values to avoid returning errors in a calculated column. Following DAX functions help you in this. ISBLANK (<value>) − Checks whether a value is blank and returns TRUE or FALSE. IFERROR (value, value_if_error) − Returns value_if_error if the expression in the first argument results in an error. Otherwise, returns the value of the expression itself. Both the return value of the expression and value_if_error must be of same data type. Otherwise, you will get an error. Print Page Previous Next Advertisements ”;

Excel DAX – Performing Complex Calculations ”; Previous Next DAX formulas can perform complex calculations that involve custom aggregations, filtering, and the use of conditional values. You can do the following with DAX − Create custom calculations for a PivotTable. Apply a filter to a DAX formula. Remove filters selectively to create a dynamic ratio. Use a value from an outer loop. Creating Custom Calculations for a PivotTable DAX functions CALCULATE and CALCULATETABLE are powerful and flexible. They are useful for defining calculated fields. These DAX functions enable you to change the context in which the calculation will be performed. You can also customize the type of aggregation or mathematical operation to perform. CALCULATE Function CALCULATE (<expression>, [<filter1>], [<filter2>]…) CALCULATE function evaluates the given expression in a context that is modified by zero or more specified filters. If your data has been filtered, the CALCULATE function changes the context in which the data is filtered and evaluates the expression in the new context that you specify by the filters. That means, any existing filters on the specified column are removed and the filter used in the filter argument is applied instead. Example Suppose you want to display the percentage of medals sport-wise filtered by Country names. Your calculation should get the percentage value overriding the filter that you apply on Country in the PivotTable. Define a calculated field – Percentage of Medal Count as shown in the following screenshot. With this DAX formula, all the rows in the Results table are taken into account in the CALCULATE function with the filter containing the ALL function. This way, you have the total count in the denominator. Your PivotTable will be as shown in the following screenshot. In the above screenshot, Country is filtered to USA and Top 18 values are displayed in the PivotTable. Next, you can dynamically filter values in the PivotTable. However, the calculations will be correct by the custom DAX formula that you used. The CALCULATETABLE function takes a table of values and performs the same action as that of CALCULATE function. Filtering Data in Formulas You can create filters within DAX formulas, to select the values from the source data for use in calculations. You can do this by defining a filter expression and using it along with the table that is an input to the DAX formula. The filter expression enables you to obtain a subset of the source data. The filter is applied dynamically each time that you update the results of the DAX formula, depending on the current context of your data and you can be assured of accurate and expected results. The filter expression typically contains a DAX filter function that returns only selected rows of the table, which then can be used as an argument for another DAX function that you are using for data aggregation. Example The following screenshot shows the definition of a calculated field that gives medal count only for summer sports. With this calculated field, the PivotTable looks as shown in the following screenshot. As you can observe, the values in the PivotTable on the right side with the new calculated field match with those that are in the PivotTable on the left side with the filter on Season field applied explicitly. Note − DAX filter and value functions return a table, but never return the table or rows directly to the Data Model and hence are always embedded in another DAX function. For details on these DAX functions, refer to the chapter – DAX Filter Functions. Adding and Removing Filters Dynamically DAX Formulas that you use in a PivotTable can be affected by the PivotTable context. However, you can selectively change the context by adding or removing filters. You can use the DAX functions ALL and ALLEXCEPT to dynamically select the rows irrespective of the PivotTable context. Additionally, you can use the DAX functions DISTINCT and VALUES for returning distinct values. Using a Value from an Outer Loop You can use a value from a previous loop in creating a set of related calculations with DAX EARLIER function. This DAX function supports up to two levels of nested loops. Print Page Previous Next Advertisements ”;

Updating the Results of DAX Formulas ”; Previous Next DAX formulas are used in calculations involving large data, including data from external data sources. The data can be subjected to changes from time to time as the DAX calculations are meant for live data as well. The results of DAX formulas need to get updated on two occasions − Data Refresh − When the data is refreshed. Recalculation − When there are changes in the DAX formula. Understanding Data Refresh vs. Recalculation Data refresh and recalculation are two separate but related operations. Data refresh is the process of updating the data in the Data Model in your workbook obtaining up-to-date data from external data sources. Recalculation is the process of updating all the columns, tables, and PivotTables in your workbook that contain DAX formulas, to reflect the changes in the underlying data that result from the changes to DAX formulas themselves. You should not save or publish the workbook until the DAX formulas in it have been recalculated. Different Ways to Update Data in Data Model Power Pivot does not automatically detect changes in external data sources. You can refresh data manually from the Power Pivot window at intervals that you can specify. You can schedule an automatic data refresh from external sources, if you have published the workbook to a SharePoint site. For details on these, refer to the chapter – Updating Data in Data Model. Recalculation of DAX Formulas Recalculation of a DAX formula is an important task, because during recalculation, column dependencies are checked and you will be notified if a column has changed, if the data is invalid, or if an error has appeared in a DAX formula that used to work. Recalculation can affect performance in the following ways − For a calculated column, the result of DAX formula should always be recalculated for the entire column, whenever you change the DAX formula. For a calculated field, the result of DAX formula is not calculated until the calculated field is placed in the context of a PivotTable or a PivotChart. The DAX formula will be recalculated when you change any row or column heading that affects the filters on the data or when you manually refresh the PivotTable. In DAX, recalculating formulas can be done automatically or manually. To learn more about recalculation, refer to the chapter – Recalculating DAX Formulas. Print Page Previous Next Advertisements ”;

Excel DAX – Formula Errors ”; Previous Next You can get errors when you write DAX formulas with wrong syntax. Calculated fields and calculated columns can contain DAX functions that require a specific type of arguments. Arguments of DAX functions can be tables, columns, or other DAX functions (nested DAX functions). As DAX functions can return tables and columns, care should be taken to check that the right type of arguments are passed to the DAX functions. DAX formula errors can be either syntax errors or semantic errors. The errors can occur either at design time or at run time. In this chapter, you will learn about some common DAX errors, their causes, and how to fix those errors. DAX Error: Calculation Aborted The following error can occur when attempting to create (design-time) or use (run-time) a calculated field with a DAX time-intelligence function. In each case, a noncontiguous date range is being passed to the time intelligence function. “DAX Error: CALCULATION ABORTED: MdxScript (instance) (00, 0) Function ‘DATEADD’ only works with contiguous date selections.” Cause at Run-time This error can be displayed when a calculated field with a DAX time intelligence function is placed in the VALUES area of a PivotTable and date fields such as the month or the quarter are selected as slicers or filters before selecting a year. For example, if you have data of three years – 2014, 2015, and 2016 and you try to use only the month March without selecting the Year field, then the values are not contiguous data values and you will get an error. How to Fix the Error at Run-time? In the above example, First add Year as a slicer or a filter and select a year. Then, add Month or Quarter as a slicer or a filter. Then, select one or more months or quarters to slice or filter on for the year selected. Cause at Design-time DAX time intelligence functions require a date column specified for the date argument. The date column must have a contiguous range of dates. This error can be returned, if there is a date value in one or more rows in the date column that is not contiguous with the data values in the previous and successive rows. If you imported your table containing dates from a data source, remember that many organizations run special processes that scan tables in databases for invalid values and replace those with a particular value. That is, if an invalid date is found, it is assigned a particular date value that may not be contiguous with other data values in the column. How to Fix This Error at Design-time? Do the following to fix the error at design time − If your date table is imported from a data source, use Refresh in Power Pivot window to reimport any changes found at the source. Check the values in your date column to make sure they are in a contiguous order. If any value is found not to be in place, it will have to be corrected at the source and the date table will have to be refreshed. Create a separate date table and date column in your Data Model. Specify the new date column as the date argument in the formula causing the error. Date tables are easy to create and add to a Data Model. DAX Semantic Error – An Example The following DAX error is a semantic error − “A function ‘CALCULATE’ has been used in a true-false expression that is used as a table filter expression. This is not allowed.” Cause This error can appear when one or more filter expressions cannot be used in context of the calculated field or calculated column expression. In most of the cases, this error is caused by a filter expression specified as an argument to the DAX CALCULATE function. The CALCULATE function requires filters defined as a Boolean expression or a table expression. How to Fix Such Errors? You can fix such errors by using the DAX FILTER function to define filters as a table expression, which can then be used as an argument to the DAX CALCULATE function. Print Page Previous Next Advertisements ”;

Excel DAX – Operators ”; Previous Next DAX is a formula language comprising of functions, operators, and values that can be used in a formula or expression, to calculate and return one or more values. You can use DAX operators to compare values, perform arithmetic calculations, and concatenate strings. In this chapter, you will learn about DAX operators and how to use them. Types of DAX Operators DAX supports the following types of operators − DAX Arithmetic Operators DAX Comparison Operators DAX Text Concatenation Operator DAX Logical Operators DAX Operator Precedence Order You can have a DAX formula with many DAX operators combining several values or expressions. In such a case, the final result will depend on the order in which the operations are performed. DAX provides you with the default operator precedence order and also ways of overriding the default precedence order. DAX default operator precedence is listed in the following table. Precedence Order Operator(s) Operation 1 ^ Exponentiation 2 – Sign 3 * and / Multiplication and Division 4 ! NOT 5 + and – Addition and Subtraction 6 & Concatenation 7 =, <, >, <=, >= and <> Equal to, Less than, Greater than, Less than or equal to, Greater than or equal to and Not equal to DAX Expression Syntax You need to first understand the DAX expression syntax and how the expression evaluation is done with the operands and operators. All expressions always begin with an equal sign (=). The equal sign indicates that the succeeding characters constitute an expression. To the right of the equal sign, you will have the operands connected by the DAX operators. For example, = 5 + 4 > 5. = 5 * 6 – 3. Expressions are always read from left to right, and the calculations are done in that sequence, based on the DAX operator precedence given in the previous section. If the DAX operators have equal precedence value, they are evaluated from the left to right. For example, =5*6/10. Both * and / have same the precedent order. Hence, the expression is evaluated as 30/10 = 3. If the DAX operators in the expression have different precedence values, then they are evaluated in the precedence order from the left to right. = 5 + 4 > 7. Default precedence is + first and > next. Hence, the expression is calculated from the left to right. – 5 + 4 is calculated first resulting in 9 and then 9 > 5 is evaluated that results in TRUE. = 5 * 6 – 3. Default precedence is * first and – next. Hence, the expression is calculated from the left to right. – 5 * 6 is calculated first resulting in 30 and then 30 – 3 is calculated that results in 27. = 2 * 5 – 6 * 3. Default precedence is * first, * next and then -. Hence, the expression evaluates as 10 – 18 and then as -8. Note, that it is not 10 – 6 resulting in 4 and then 4*3 that is 12. Using Parentheses to Control DAX Calculation Order You can change the DAX default operator precedence order by using parentheses, grouping the operands and the operators to control the calculation sequence. For example, = 5 * 6 – 3 evaluates to 27 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 5 * (6 – 3), then 6 – 3 is calculated first resulting in 3 and then 5 * 3 is calculated which results in 15. = 2 * 5 – 6 * 3 evaluates to -8 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 2 * (5 – 6) * 3, then 5 – 6 is calculated first resulting in -1 and then 2 * (-1) * 3 is calculated which results in -6. As you can see, with the same operands and operators, different results are possible by the way you group them. Hence, when you use the DAX operators in the DAX formulas, you should pay attention to how the computation sequence is to be. Differences Between Excel and DAX Though DAX has similarities with Excel formulas, there are certain significant differences between the two. DAX is more powerful than Excel because of its underlying memory resident computation engine. DAX supports more data types than Excel. DAX provides additional advanced features of a relational database, Data Model, including richer support for date and time types. In some cases, the results of calculations or the behavior of functions in DAX may not be the same as in Excel. This is due to the differences in the following − Data type casting Data types Difference in Data Type Casting In DAX, when you have an expression =value1 operator value2, the two operands value1 and value2 should be of the same data type. If the data types are different, DAX will convert them first to a common data type implicitly. Refer to the chapter – DAX Syntax for details. For example, you have to compare two operands of different data types, say a number resulting from a formula, such as =[Amount] * 0.08 and an integer. The first number can be a decimal number with many decimal places, whereas the second number is an integer. Then DAX handles it as follows − First, DAX will convert both the operands to real numbers using the largest numeric format that can store both kinds of numbers. Next, DAX will compare the two real numbers. In contrast, Excel tries to compare values of different data types without first coercing them to a common data type. For this reason, you might find different results in DAX and in Excel for the same comparison expression. Difference in Data Types The operator precedence order in DAX and Excel is the same. However, the operator percent (%) and data ranges that

Excel DAX – Working with Text and Dates ”; Previous Next DAX can be used in the scenarios involving working along with text, extracting and composing date and time values or creating values based on a condition. You can do the following with DAX − Create a key column in a table by concatenation. Compose a date based on date parts extracted from a text date. Define a custom date format. Change data types using a formula. Convert real numbers to integers. Convert real numbers, integers, or dates to strings. Convert strings to real numbers or dates. Creating a Key Column by Concatenation Data Model in PowerPivot allows only a single key column. It does not support composite keys that you might find in the external data sources. Hence, if any composite keys exist in a table in the data source, you need to combine them into a single key column for the table in the Data Model. You can use the DAX function CONCATENATE to combine two columns into a single column in a table in the Data Model. DAX function CONCATENATE joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference if the column contains appropriate values. = CONCATENATE ([Column1], [Column2]) DAX CONCATENATE function accepts only two arguments. If any of the arguments is not of text data type, it will be converted to text. DAX CONCATENATE function returns the concatenated string. Date Based on Date Parts Extracted from a Text Date Data Model in Power Pivot supports a data type datetime for date and time values. The DAX functions that work on date and/or time values require the datetime data type for the arguments. If your data source contains dates of a different format, you need to first extract the date parts using a DAX formula and combine those parts to constitute a valid DAX datetime data type. You can use the following DAX functions to extract and compose dates − DATE − Returns the specified date in datetime format. DATEVALUE − Converts a date in the form of text to a date in datetime format. TIMEVALUE − Converts a time in text format to a time in datetime format. Defining a Custom Date Format Suppose the dates in your data source are not represented by a standard format. You can define a custom date format to ensure that the values are handled correctly. DAX FORMAT function enables you to convert a value to text according to the specified format. FORMAT (<value>, <format_string>) FORMAT function returns a string containing value formatted as defined by format_string. You can either use pre-defined Date and Time formats or you can create user-defined Date and Time formats for the argument format_string of the FORMAT function. Following are the predefined date and time format names. If you use strings other than these predefined strings, they will be interpreted as a custom date and time format. S. No. Format_String & Description 1 “General Date” Displays a date and/or time. For example, 2/10/2015 10:10:32 AM 2 “Long Date” or “Medium Date” Displays a date according to long date format. For example, Wednesday, March 07, 2016 3 “Short Date” Displays a date using short date format. For example, 2/03/2016 4 “Long Time” Displays a time using long time format. Typically includes hours, minutes and seconds. For example, 10:10:32 AM 5 “Medium Time” Displays a time in 12-hour format. For example, 09:30 PM 6 “Short Time” Displays a time in 24-hour format. For example, 14:15 Alternatively, you can use the characters in the following table to create user-defined date/time formats. S. No. Character & Description 1 : Time separator. Time separator. Separates hours, minutes, and seconds when time values are formatted. 2 / Date separator. Separates the day, month, and year when date values are formatted. 3 % Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a userdefined format. Following are the details of the various characters. %d − Displays the day as a number without a leading zero (e.g. 5). %dd − Displays the day as a number with a leading zero (e.g. 05). %ddd − Displays the day as an abbreviation (e.g. Sun). %dddd − Displays the day as a full name (e.g. Sunday). %M − Displays the month as a number without a leading zero (e.g. January is represented as 1). %MM − Displays the month as a number with a leading zero (e.g. January is represented as 01). %MMM − Displays the month as an abbreviation (e.g. January is represented as Jan). %MMMM − Displays the month as a full month name (e.g. January). %gg − Displays the period/era string (e.g. A.D.). %h − Displays the hour as a number without leading zeros using the 12-hour clock (e.g. 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format. %hh − Displays the hour as a number with leading zeros using the 12-hour clock (e.g. 01:15:15 PM). %H − Displays the hour as a number without leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15). Use %H if this is the only character in your user-defined numeric format. %HH − Displays the hour as a number with leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15). %m − Displays the minute as a number without leading zeros (e.g. 2:1:15). Use %m if this is the only character in your user-defined numeric format. %mm − Displays the minute as a number with leading zeros (e.g. 2:01:15). %s − Displays the second as a number without leading zeros (e.g. 2:15:5). Use %s if this is the only character in your user-defined numeric format. %ss − Displays the second as a number with leading zeros (e.g. 2:15:05). %f − Displays

Excel DAX – Syntax ”; Previous Next As discussed earlier, DAX is a formula language comprising of operators, values, functions, and formulas. In this chapter, you will learn about DAX Syntax. DAX Syntax can be categorized as − Syntax for DAX Formulas DAX Naming Requirements DAX Special Values DAX Functions DAX Operators DAX Data Types Before you proceed to learning DAX Syntax, you have to understand the difference between Excel formulas and DAX formulas. Differences between Excel Formulas and DAX Formulas DAX formulas are similar to the Excel formulas and you can type them in the formula bar. However, there are some vital differences between the two. Excel Formula DAX Formula Excel formulas are typed in the formula bar in the Excel window. DAX formulas are typed in the formula bar in the Power Pivot window. In Excel formulas, you can reference individual cells or arrays for data. In DAX formulas, you can reference only complete tables or columns of data, i.e. references can be only to tables and fields in the tables. However, if at all you have to perform a calculation only on a part of the column data, you can do so with the DAX functions that filter and provide the required unique data values for calculation. Excel formulas support certain data types. DAX provides more data types than Excel does. Hence, DAX formulas can use the additional data types also. Excel does not support any implicit data conversions. DAX performs implicit data type conversions during calculations. Print Page Previous Next Advertisements ”;

Excel DAX – Calculated Fields / Measures ”; Previous Next A calculated field in a table in a Data Model is the field obtained by a DAX formula. In earlier versions of Power Pivot, the calculated field was termed as a measure. In Excel 2013, it was renamed as a calculated field. However, it is renamed back to measure in Excel 2016. If you refer to any documentation, you can observe a mix up of these two terms. Note that the terms calculated field and measure are synonymous. In this tutorial, we use the term calculated field. Understanding Calculated Fields A calculated field is a formula that is created specifically for use in a PivotTable (or PivotChart). You can create a calculated field based on standard aggregation functions, such as COUNT or SUM, or by defining your own DAX formula. Following is the difference between the calculated field and the calculated column − A calculated field can be used only in the VALUES area of a PivotTable. A calculated column with the calculated results can be used in ROWS, COLUMNS and FILTERS areas also. Saving Calculated Field The calculated field will be saved with its source table in the Data Model. It appears in the Power PivotTable or Power PivotChart Fields list as a field in the table. Using Calculated Field To use a calculated field, you have to select it from the Power PivotTable Fields list. The calculated field will get added to the VALUES area and the formula used for the calculated field will be evaluated. A result is created for each combination of row and column fields. Calculated Field – An Example Consider the following Data Model for Olympics data − As seen in the above screenshot, the Results table has a field Medal that contains the values – Gold, Silver, or Bronze for each of the rows containing Sport – Event – Country – Date combination. Suppose you want medal count for each country, then you can create a calculated field Medal Count with the following DAX formula − Medal Count := COUNTA([Medal]) Creating a Calculated Field in a Table To create the calculated field Medal Count in the Results table, do the following − Click the cell in the calculation area below the Medal column in the Results table. The cell will be highlighted. Type Medal Count:=COUNTA([Medal]) in the formula bar. Press Enter. As seen in the above screenshot, the calculated field appears in the selected cell, showing the value as 34,094. This number is the total number of rows in the Results table. Hence, it does not make much sense at the first look. As discussed earlier, the real use of a calculated field can be seen only by adding it to a Power PivotTable or a Power PivotChart. Using the Calculated Field in a Power PivotTable To use the calculated field to count the number of medals for each country, do the following − Click the PivotTable on the Ribbon in the Power Pivot window. Click the PivotTable in the dropdown list. Create PivotTable dialog box appears. Click the Existing Worksheet. Select where you want to place the PivotTable. An empty PivotTable will get created. Click the Results table in the PivotTable Fields list. Click the fields – Country and Medal count. As you can observe, Medal Count is added to VALUES area and Country is added to ROWS area. The PivotTable is created with the field Country values appearing in the rows. And for each row, the Medal Count value is calculated and displayed. That is the way, the calculated field evaluates the DAX formula used and displays the values. Add the field Sport from the Results table to ROWS area. As you can see in the above screenshot, Medal Count is calculated for each Country – Sport-wise and a Subtotal for the Country itself. This is how DAX supplements the Power features. Types of Calculated Fields There are two types of Calculated Fields – Implicit and Explicit. An implicit calculated field is created in the Power PivotTable Fields list pane. An explicit calculated field is created either in the table in the Power Pivot window, or from the PowerPivot Ribbon in the Excel window. Creating an Implicit Calculated Field An implicit calculated field can be created in two ways, both in the Power PivotTable Fields pane. Creating an Implicit Calculated Field in the PivotTable Fields List You can create the Count of Medal Field from the Medal field in the PivotTable Fields list as follows − Deselect the field Medal Count. Right-click on the field Medal. Click Add to Values in the dropdown list. Count of Medal appears in the Values area. Count of Medal column will be added to the PivotTable. Creating an Implicit Calculated Field in the VALUES Area You can create an implicit calculated field – % of Parent Row in the Values area to express the Medal count of each sport that a country has won as a percentage of the total number of Medals won by that Country. Click the down arrow in the Count of Medal box in VALUES area. Click the Value Field Settings in the dropdown list. Value Field Settings dialog box appears. Type % Medals in the Custom Name box. Click the Show Values As tab. Click the box under Show values as. Click the % of Parent Row Total. Click the Number Format button. Format Cells dialog box appears. Click Percentage. Type 0 in decimal places. Click OK. Click OK in the Value Field Settings dialog box. Select Do Not Show Subtotals. You created another implicit calculated field % Medals and as you can observe, for each Country, the percentage of Medals Sport-wise are displayed. Drawbacks of an Implicit Calculated Field Implicit calculated fields are easy to create. In fact, you have been creating them even in Excel PivotTables and Pivot Charts. But, they have the following drawbacks − They are volatile. That means, if you deselect

Excel DAX – Understanding DAX Functions ”; Previous Next In Excel 2013, DAX has 246 functions. You have already learnt about the different types of DAX functions in the chapter – DAX Functions. However, if you have to use a DAX function in a DAX formula, you need to understand the function in detail. You should know the syntax of the function, the parameter types, what the function returns, etc. If you are writing a DAX formula, it is suggested to use the DAX functions where applicable. For this, you can refer to the tutorial – DAX Functions in this tutorials library for an indepth understanding of where and how to use each of the 246 DAX functions. You can always go back and forth between this tutorial and DAX Functions tutorial to obtain mastery on DAX. In this chapter, you will understand how to read and interpret the DAX functions in the DAX Functions tutorial. DAX Function – Explanation Structure In the DAX functions tutorial, each DAX function is explained in a standard structure, comprising of the following sections − Description Syntax Parameters Return Value Remarks Example You will learn about each of these in the following sections. Description In the Description section, you will learn what the DAX function is about and where it can be used. Syntax In the Syntax section, you will learn the exact function name and the respective parameters. Parameters In the Parameters section, you will learn about each of the parameters of the specific DAX function, whether a parameter is an input or an output and if there are any options. As seen in the chapter – DAX Standard Parameters, only the standard parameter names will be used. Return Value In the Return Value section, you will learn about what value the DAX function will return and its data type. Remarks In the Remarks section, you will learn about any extra information that you need to know about the usage of the DAX function. Example The DAX function description will be ended with an example of the usage of the function. Print Page Previous Next Advertisements ”;

Excel DAX – Ranking and Comparing Values ”; Previous Next If you want to show only the top n number of items in a column or PivotTable, you have the following two options − You can select n number of top values in the PivotTable. You can create a DAX formula that dynamically ranks values and then uses the ranking values in a Slicer. Applying a Filter to Show only the Top Few Items To select n number of top values for display in the PivotTable, do the following − Click the down arrow in the row labels heading in the PivotTable. Click the Value Filters in the dropdown list and then click Top 10. Top 10 Filter (<column name>) dialog box appears. Under Show, select the following in the boxes from left to right. Top 18 (The number of top values that you want to display. The default is 10.) Items. In the by box, select Medal Count. Click OK. The top 18 values will be displayed in the PivotTable. Advantages and Disadvantages of Applying Filter Advantages It is simple and easy to use. Suitable for tables with large number of rows. Disadvantages The filter is solely for display purposes. If the data underlying the PivotTable changes, you must manually refresh the PivotTable to see the changes. Creating a DAX Formula That Dynamically Ranks Values You can create a calculated column using a DAX formula that contains the ranked values. You can then use a slicer on the resulting calculated column to select the values to be displayed. You can obtain a rank value for a given value in a row by counting the number of rows in the same table having a value larger than the one that is being compared. This method returns the following − A zero value for the highest value in the table. Equal values will have the same rank value. If n number of values are equal, the next value after the equal values will have a nonconsecutive rank value adding up the number n. For example, if you have a table ‘Sales’ with sales data, you can create a calculated column with the ranks of the Sales Amount values as follows − = COUNTROWS (FILTER (Sales, EARLIER (Sales [Sales Amount]) < Sales [Sales Amount]) ) + 1 Next, you can insert a Slicer on the new calculated column and selectively display the values by ranks. Advantages and Disadvantages of Dynamic Ranks Advantages The ranking is done in the table and not on a PivotTable. Hence, can be used in any number of PivotTables. DAX formulas are calculated dynamically. Hence, you can always be sure that the ranking is correct even if the underlying data has changed. Since the DAX formula is used in a calculated column, you can use the ranking in a Slicer. Suitable for tables with large number of rows. Disadvantages Since the DAX calculations are computationally expensive, this method might not be suitable for tables with large number of rows. Print Page Previous Next Advertisements ”;