DAX is another name for Power BI’s Data Analysis Expressions. It is simply a toolkit with options to shape, filter, and analyze data. It makes data analysis with Power BI more insightful. DAX lets users go beyond simple data displays and dig deeper to find patterns, trends, and relationships.
DAX offers you cool functions like adding custom calculations to your report. Knowing some insider tips will give you an edge to make your reports truly stand out. Whether you are just starting or already a seasoned user, this DAX function list in Power BI will help you polish your reports and make them more insightful.
With the right strategies up your sleeve, you will be able to make better business decisions through a deeper understanding of your data. In this post, we will explore DAX techniques in Power BI to take your reports to the next level.
Calculate Custom Totals with SUMX
This is the first one in our Power BI DAX cheat sheet and one of the most commonly used ones. Sometimes a basic SUM does not cover complex totals that require row-by-row calculations. Using SUMX, you can iterate through each row in a table and apply calculations to each, which is ideal for weighted averages or conditional totals.
TotalSales = SUMX(Sales, Sales[Quantity] * Sales[Price])
Create Conditional Calculations with SWITCH
The SWITCH function allows you to apply multiple conditions in a single formula. It is especially useful when dealing with specific categories or tiers, making your formulas cleaner and more readable.
SalesCategory = SWITCH(TRUE(), Sales[Total] > 100000, “High”, Sales[Total] > 50000, “Medium”, “Low”)
Enhance Time Intelligence with DATESYTD and DATESQTD
Time-based analysis is crucial in Power BI reports. Using functions like DATESYTD (Year-to-Date) and DATESQTD (Quarter-to-Date) lets you compare periods and see trends over time. This is a top Power BI DAX function that lets you automatically adjust your reports to date changes.
YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD(Calendar[Date]))
Implement Dynamic Totals with HASONEVALUE
HASONEVALUE is used for creating totals that adjust based on user selections in slicers. This is particularly useful when you want certain calculations or visuals to respond only when a single value is selected.
DynamicTotal = IF(HASONEVALUE(Product[Category]), SUM(Sales[Amount]), 0)
Calculate the Percent of the Total with DIVIDE
DIVIDE is a DAX function for percentages that calculates ratios and percentages safely by avoiding division by zero errors. This is perfect for calculating the contributions of individual items to a total. This particular one allows for error-free percentage reporting and comparison across categories.
SalesPercent = DIVIDE(Sales[Amount], CALCULATE(SUM(Sales[Amount]), ALL(Sales)), 0)
Simplify Relationships with RELATED
When working across tables, RELATED pulls in data from a related table without requiring explicit joins. This function is especially useful in calculated columns and makes creating relationships much easier and faster, allowing you to access fields from other tables directly.
RelatedProductName = RELATED(Product[Name])
If you want to train your team of expert data analysts in advanced data analysis, look no further than EducationNest’s perfectly curated courses. They offer expert-designed Power BI Training for corporate teams to pick up the latest skills in the market to boost their skills.
Calculate Moving Averages with AVERAGEX
Moving averages smooth out data for trend analysis, which is particularly useful in time series. By using AVERAGEX, you can calculate a moving average over a specific period, which provides more accurate insights into data patterns.
MovingAvg = AVERAGEX(DATESINPERIOD(Sales[Date], LASTDATE(Sales[Date]), -3, MONTH), Sales[Amount])
Read More
Top 10 Advantages of Cloud Computing for Companies
Ultimate Guide on Using AI for Perfect PowerPoint Presentations
Filter Data Dynamically with CALCULATE and FILTER
CALCULATE combined with FILTER allows for highly customized calculations by applying targeted filters. This is ideal for refining data analysis, making it easy to compare specific subsets or criteria. Their flexibility makes CALCULATE and FILTER important Power BI tools for advanced data analysis.
HighValueSales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 50000))
Get Top N Results with RANKX
The RANKX function allows you to dynamically rank items like top-selling products or best-performing regions. This is essential when you want to identify leaders or high performers within your data. By using RANKX, you can automatically update rankings based on data changes, making it ideal for performance dashboards.
ProductRank = RANKX(ALL(Product), Sales[TotalSales], , DESC, Dense)
Use ALLSELECTED for Context-Sensitive Calculations
This is a top DAX function in Power BI which comes in handy when you need calculations to respect filters within a specific visual or context. This function allows for context-aware calculations that respond to user-selected filters, making it great for interactive dashboards.
TotalSalesInContext = CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales))
Apply Conditional Formatting with IF and SWITCH
Using IF and SWITCH for conditional formatting helps visually highlight data, making insights easier to interpret. For example, you could change colors based on performance thresholds, providing immediate visual feedback. Conditional formatting improves user experience by offering visual cues on data performance.
SalesPerformance = SWITCH(TRUE(), Sales[Amount] >= 100000, “Green”, Sales[Amount] >= 50000, “Yellow”, “Red”)
Create Rolling Totals with DATESINPERIOD
Rolling totals are useful for analyzing trends over time. By setting up rolling totals using this simple Power BI DAX trick, you can show cumulative performance over user-defined periods, such as 12-month or 30-day rolling windows. Rolling totals are especially powerful for time series analysis, as they help uncover underlying trends by smoothing out short-term fluctuations.
Rolling12MonthSales = CALCULATE(SUM(Sales[Amount]), DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -12, MONTH))
Conclusion
If you explore Power BI in-depth, you will find many more useful functions like CALENDAR, EARLIER, USERELATIONSHIP, and many more. Although there are a ton of other useful functions you will find in Power BI, these are the most common ones in use by skilled data analysts.
This is the DAX function cheat sheet of the most commonly used ones that you should absolutely know no matter if you are just starting out or a seasoned analyst.
If your company is looking for big data analytic courses for your corporate employees, EducationNest has the best expert-led training programs for you. Built by experts from the industry, they come in affordable pricing and are fully customizable to meet your team’s needs.
