DAX Tips and Tricks for Advanced Power BI Reporting

By - Abhishek Tyagi

Power BI is a powerful tool for analysing and visualizing data, but DAX (Data Analysis Expressions) takes it to the next level. Here are some easy-to-follow tips and tricks to help you create better reports in Power BI. 

1. Start with the End in Mind 

Before starting to write DAX formulas, it's a good idea to think about what you want to get as a result. For example, if you're trying to calculate total sales for the year, plan out how that should look in your report. Also, try to use measures instead of calculated columns whenever possible they are more efficient and keep your reports running smoothly. Measures update based on what the user selects, so they are more flexible too. Taking a moment to picture the final output can save you from overcomplicating things later. 

2. Use Time Intelligence for Date Calculations  Functions: DATESBETWEEN and TOTALYTD 

Example: Time intelligence functions make it easy to calculate Year-to-Date (YTD) sales or compare data over months. These functions simplify operations like comparing current month sales to last month or identifying trends over quarters. 

Example: Create a measure for Year-to-Date (YTD) Sales: 

 Here, "03/31" specifies the fiscal year-end.  

Create a measure for Sales Last Year (LY) for comparison:  

If your company follows a non-standard fiscal year, you can customize your date table and use functions like DATESBETWEEN to get accurate results. Make sure your date table is marked as a "Date Table" in Power BI to avoid calculation errors.   To calculate sales for a custom period (e.g., fiscal Q1 2024):   

3. Rolling Totals and Averages 

A rolling total (or moving total) is a cumulative sum of values over a defined period, such as the last 3 months, last 7 days, or last 5 years. It dynamically shifts the calculation window as the context changes, which makes it ideal for analysing trends and smoothing fluctuations over time. 

In Power BI, rolling totals can be calculated using the DATESINPERIOD function to define the dynamic date range and the CALCULATE function to sum or aggregate the values within that range. 

Example:  We calculate the rolling 3-month sales using this formula. 

We have two tables: 

1. Sales Table  

2. Calander Table 

   
   

   

   
   

Output:  

This formula calculates sales for the last three months. Make sure your date table is properly set up for these calculations. Rolling totals help businesses understand cumulative performance and identify periods of consistent growth or decline. 

4. Use Variables for Cleaner Code. 

Variables make your formulas easier to read and faster to process. They allow you to store intermediate results and reuse them, reducing the need for repeated calculations. 

Example: Here’s an example comparing current month vs. previous month sales using variables:  

Breaking down calculations into smaller steps helps avoid mistakes. Variables also improve debugging, as you can validate individual components of your formula more easily. 

5. Connect Data with Relationship Functions. 

When working with multiple tables, use relationship functions like RELATED or USERELATIONSHIP. These let you calculate values across tables, which is essential in complex data models. 

Example:  

You can use the RELATEDTABLE only when the function is used in the "one" side of a one-to-many relationship to return all matching rows from the "many" side. 

6. Make Reports Dynamic with Measures. 

Add dynamic elements like titles or labels using DAX. This makes your reports interactive and tailored to the user’s selections. 

This updates the title based on the year selected in a slicer. Dynamic measures can also create conditional formatting or highlight important trends, making your reports more engaging and insightful.  

7. Dynamic Measures Using SWITCH 

Dynamic measures allow you to create flexible reports that adapt based on user interaction. This technique is particularly useful when you want users to be able to switch between different metrics (e.g., Sales, Profit, or Units Sold) without needing separate visuals for each metric. By combining slicers with the SWITCH function, you can dynamically update the displayed measure based on user input. 

Steps to Implement: 

1. Create a Table for Metrics: Start by creating a new table (manually or using DAX) that lists the metrics you want to toggle between. For example:  Metric Selector: "Sales", "Profit", "Units Sold". 

2. Add a Slicer: Add a slicer to your report, bound to the Metric Selector column from the table you created. 

3. Define the Dynamic Measure: Use the SWITCH function to create a measure that dynamically switches between the selected metrics. 
   

Example: 

 Output: 

Explanation of the Code: 

• SELECTEDVALUE: Captures the current selection from the slicer. 

• SWITCH: Evaluates the value selected in the slicer and returns the corresponding measure: 

• If "Sales" is selected, it returns SUM(Sales[Sales Amount]). 

• If "Profit" is selected, it returns SUM(Sales[Profit]). 

• If "Units Sold" is selected, it returns SUM(Sales[Units Sold]). 

• BLANK: Ensures no value is displayed if no metric is selected. 

CONCLUSION:    

DAX can be challenging at first, but with practice, it becomes easier and more useful. These tips will help you build better, faster, and smarter Power BI reports. Keep learning and trying new things; every formula you write makes you better. 

Good luck and happy reporting