Excel DAX - Scenarios

You have learnt DAX syntax, the usage of DAX operators and DAX functions in the previous chapters. As you are aware, DAX is a formula language used for data modeling and data analysis.

DAX can be used in various scenarios. Based on the DAX scenarios, DAX optimizes the performance and produces accurate and effective results. In this chapter, you will get to know some of the DAX scenarios.

Performing Complex Calculations

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 formula.
• Remove filters selectively to create a dynamic ratio.
• Use a value from an outer loop.

For details, refer to the chapter Scenarios - Performing Complex Calculations.

Working with Text and Dates

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 by concatenation.
• Compose a date based on date parts extracted from a text date.
• Define a custom date.
• 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.

For details, refer to the chapter Scenarios - Working with Text and Dates.

Conditional Values and Testing for Errors

DAX functions enable to test values in the data and return a different value based on a condition. DAX functions that test values are also useful for checking the range or type of values, to prevent unexpected data errors from breaking calculations. You can do the following with DAX −

• Create a value based on a condition.
• Test for errors within a formula.

For details, refer to the chapter Scenarios - Conditional Values and Testing for Errors.

Using Time Intelligence

You have learnt about DAX time intelligence functions in the chapter – Understanding DAX time intelligence.

DAX time intelligence functions include functions to help you retrieve dates or date ranges from your data. You can then use those dates or date ranges to calculate values across similar periods. The time intelligence functions also include functions that work with standard date intervals, to allow you to compare values across months, years, or quarters. You could also create a DAX formula that compares values for the first and the last date of a specified period.

You can learn more about DAX intelligence functions and what they can do for the following −

• Calculate Cumulative Sales.
• Compare Values over Time.
• Calculate a Value over a Custom Date Range.

For details, refer to the chapter Scenarios - Using Time Intelligence.

Ranking and Comparing Values

If you want to show only the top n number of items in a column or a PivotTable, you have the following options −

• Apply a filter to show only the top or bottom few items.
• Create a DAX formula that dynamically ranks values and apply a filter.

Each of these options have pros and cons.

For details, refer to the chapter Scenarios - Ranking and Comparing Values.