So far, you have been able to read several “recipes” on this blog which were about Data Validation and lists. Office365 (Microsoft365) subscription users have access to dynamic array functions that easily handle variable-length lists. But what should users with an older version of Excel do? In the text that follows, you will learn how to solve this problem with the help of the named ranges, OFFSET and COUNTA functions.
Traditional Excel has only two data types: text and numbers. All others (date, time, percentage, etc.) are specially formatted numbers. In the second half of 2019, Microsoft added new data types to Excel: Geography and Stocks. First, for a given term, offers an abundance of geographical determinants while the second provides data on stock market. A lot of new types have been announced, and since recently users of Microsoft 365 subscriptions can create custom data types.
In one of the previous “recipes” you could get acquainted with the EARLIER function. Here you could learn how to use this function to calculate the rank based on highest price of the product within a table. This can be done much more easily, by applying the RANKX function. Let’s see how it is used and what other options are also available when ranking data through DAX.
If we want to check, within the DAX statement, whether the element belongs to the given set we will use the operator IN and then, in the extension, in curly brackets, list the elements that belong to the set. In this way we can associate a filter within the CALCULATE function that specifies more than one value. A similar effect is achieved with the CONTAINSROW function.
When we use the DAX function CALCULATE and give it the filter values as arguments as a result we will get a Power Pivot report filled with rows containing the same values. Although these values correspond to the given filter this way of displaying data is not the best solution. To make report neat, we can use the FILTER function or, even better, the KEEPFILTERS function about which you will learn more if you read the text that follows …