Most Power BI users start the same way \u2014 dragging fields onto a canvas, picking chart types, and feeling pretty good about the result. Then someone asks for year-over-year growth, or a running total, or a percentage of total that doesn’t break when a slicer is applied. Suddenly, the drag-and-drop approach hits a wall.<\/p>\n\n\n\n
That wall is where DAX begins.<\/p>\n\n\n\n
DAX \u2014 Data Analysis Expressions \u2014 is the formula language that powers real analysis in Power BI. It’s what separates a static report from a genuinely intelligent dashboard. Once you understand how it works, you can build calculations that respond to filters, compare time periods, rank performance dynamically, and answer business questions that basic visuals simply cannot.<\/p>\n\n\n\n
This guide covers the top DAX functions in Power BI that every analyst should know, from the essential building blocks to the advanced functions that experienced BI developers rely on daily. Whether you’re brand new to Power BI or looking to sharpen what you already know, this is the place to start.<\/p>\n\n\n\n
DAX stands for Data Analysis Expressions. It’s a collection of functions, operators, and constants that you can combine into formulas to calculate and return one or more values from the data in your Power BI model.<\/p>\n\n\n\n
The language was originally built for Power Pivot in Excel, and it carries the same core logic into Power BI, SQL Server Analysis Services, and Azure Analysis Services. If you learn DAX in Power BI, that knowledge transfers across the entire Microsoft analytics ecosystem.<\/p>\n\n\n\n
DAX works differently from what most people expect at first. Instead of writing a formula that calculates a value for a specific cell, you write a formula that defines how a value should be calculated based on whatever context surrounds it \u2014 what filters are active, what rows are in scope, what relationships exist between tables. This makes DAX extraordinarily flexible, but it also means the same formula can return different results depending on where and how it’s used.<\/p>\n\n\n\n
There are three types of DAX calculations you’ll work with in Power BI.<\/p>\n\n\n\n
Measures<\/strong> are dynamic calculations that evaluate based on the filter context of a visual. When you drag a measure into a chart, Power BI recalculates it for every combination of filters, slicers, and groupings in view. Measures are stored in the data model but don’t add columns to your tables. They’re the workhorses of DAX and the type you’ll write most often.<\/p>\n\n\n\n Calculated columns<\/strong> are formulas that run row by row across a table and store the result as a new column in the data model. They’re evaluated when the data loads or refreshes, not in real time. Use them when you need to add a fixed value to each row \u2014 like combining first and last name into a full name, or categorizing a numeric value into a label.<\/p>\n\n\n\n Calculated tables<\/strong> create entirely new tables using DAX. They’re used less frequently but are powerful for specific scenarios like creating date tables, bridging tables, or deriving summary tables from raw data.<\/p>\n\n\n\n Real-world DAX use cases span almost every industry: calculating monthly recurring revenue for SaaS companies, tracking budget vs. actuals in finance, analyzing patient outcomes in healthcare, or monitoring campaign performance in marketing. Wherever Power BI is used for reporting, DAX is what makes the reporting meaningful.<\/p>\n\n\n\n DAX functions fall into several categories, each serving a different analytical purpose.<\/p>\n\n\n\n Aggregation functions like SUM, AVERAGE, MIN, and MAX perform calculations across a column or table. Logical functions like IF and SWITCH evaluate conditions and return different results based on what’s true. Text functions handle string manipulation \u2014 concatenation, extraction, formatting. Date and time functions work with date values for calculations like age, tenure, or deadline tracking. Filter functions like CALCULATE and FILTER are the most powerful category, giving you control over which data is included in a calculation. Time intelligence functions are specialized filter functions designed specifically for period-over-period comparisons \u2014 year-to-date, month-over-month, same period last year. Statistical functions include things like RANKX, PERCENTILE, and standard deviation calculations.<\/p>\n\n\n\n Understanding which category a function belongs to helps you know when to reach for it. When you need to compare this month to last month, you’re looking for time intelligence. When you need to calculate something for a specific subset of data, you’re in filter function territory.<\/p>\n\n\n\n SUM is the first DAX function most people learn, and it does exactly what you’d expect \u2014 it adds up all the values in a column.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This measure adds up every value in the SalesAmount column of the Sales table. When placed in a visual with a region filter active, it automatically sums only the sales for that region. That context-awareness is what makes even simple DAX measures more powerful than a hardcoded calculation.<\/p>\n\n\n\n The most common mistake with SUM is trying to use it on a column that contains text or mixed data types \u2014 SUM only works on numeric columns. Another pitfall is using SUM in a calculated column when you actually need a measure. If you write SUM in a calculated column, it will sum the entire column for every row, which is almost never what you want.<\/p>\n\n\n\n CALCULATE is the most important function in all of DAX. If you only master one function deeply, make it this one.<\/p>\n\n\n\n What CALCULATE does is evaluate an expression \u2014 usually a measure \u2014 within a modified filter context. You can use it to add filters, remove filters, or override existing filters on any calculation. This is the mechanism behind almost every interesting DAX formula: comparing a region to company-wide totals, calculating what a metric would be if a filter weren’t applied, or scoping a calculation to a specific time period.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This measure calculates Total Sales but overrides the filter context so that only rows where Region equals “West” are included \u2014 regardless of what’s selected in a slicer. You could place this measure in a table alongside a Region slicer and the value would always reflect West, no matter what the slicer is set to.<\/p>\n\n\n\n CALCULATE is the backbone of percentage-of-total calculations, time intelligence, and almost all advanced analytical measures. The filter context concept it relies on is the single most important idea in DAX to truly internalize.<\/p>\n\n\n\n FILTER returns a table \u2014 specifically, a filtered version of a table that meets a condition you define. It’s rarely used on its own, but it becomes essential when paired with CALCULATE for complex filtering scenarios that go beyond simple column equals value logic.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This returns the total sales amount but only for transactions where the individual sale exceeded $10,000. The FILTER function here generates a subset of the Sales table that meets the condition, and CALCULATE applies that subset as the filter context for the SUM.<\/p>\n\n\n\n FILTER is powerful in dashboards where you need to create dynamic segments \u2014 top performers, outliers, high-risk accounts \u2014 that can’t be expressed with a simple column filter in CALCULATE.<\/p>\n\n\n\n These three functions all count things, but they count different things, and using the wrong one is a common source of confusion.<\/p>\n\n\n\n COUNT<\/strong> counts the number of non-blank numeric values in a column. It ignores blank cells and non-numeric values.<\/p>\n\n\n\n COUNTA<\/strong> counts non-blank values of any type \u2014 numbers, text, dates, logical values. It’s more permissive than COUNT.<\/p>\n\n\n\n COUNTROWS<\/strong> counts the number of rows in a table (or a filtered table). It doesn’t look at column values at all.<\/p>\n\n\n\n In practice, COUNTROWS is the most useful of the three in most analytical scenarios. Rather than counting a column, you’re counting records, and COUNTROWS(FILTER(Table, Condition)) gives you a flexible, reliable way to count rows that meet any criteria.<\/p>\n\n\n\n Use COUNT when you specifically need to count numeric entries. Use COUNTA when you’re working with mixed data types. Use COUNTROWS when you need to count transactions, customers, events, or any other record-level count.<\/p>\n\n\n\n IF evaluates a logical condition and returns one value if the condition is true and another if it’s false. It’s the foundation of conditional logic in Power BI.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This measure checks whether the Profit measure is positive. If it is, it returns “Profit.” If not, it returns “Loss.” This kind of conditional labeling is useful for KPI indicators, conditional formatting triggers, and traffic-light style dashboards.<\/p>\n\n\n\n IF can be nested \u2014 an IF inside an IF inside an IF \u2014 but nested IFs become difficult to read quickly. If you find yourself nesting more than two levels deep, SWITCH is almost always the cleaner solution.<\/p>\n\n\n\n SWITCH evaluates an expression against a list of possible values and returns a result for the first match. It’s the cleaner, more readable alternative to deeply nested IF statements, especially when you’re assigning labels to categories or creating dynamic calculation selectors.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n The DISTINCTCOUNT counts the number of unique values in a column. It’s distinct from COUNT, which can count the same value multiple times if it appears in multiple rows.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This is invaluable in customer analytics \u2014 counting how many distinct customers placed orders in a period, how many unique products were sold, or how many individual regions were active in a campaign. Without DISTINCTCOUNT, any standard COUNT would give you transaction volume, not unique entity count.<\/p>\n\n\n\n RELATED retrieves a value from a related table \u2014 specifically, from the “one” side of a one-to-many relationship. Think of it like a VLOOKUP in Excel, except it uses the established relationships in your Power BI data model rather than requiring you to specify a lookup range.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n If you’re writing a calculated column in a Sales table and you want to pull the product category from a related Products table, RELATED fetches it using the relationship already defined in the model. It only works in calculated columns (not measures) and only when a valid relationship exists between the tables.<\/p>\n\n\n\n ALL removes filters from a column or an entire table, causing a calculation to ignore whatever filter context is currently in place. It’s a critical building block for percentage-of-total calculations.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This measure divides the sales in the current context (filtered by whatever slicer or row header is active) by the grand total sales across all rows, since ALL(Sales) strips away every filter on the Sales table. The result is a percentage that works correctly regardless of how the report is filtered.<\/p>\n\n\n\n DATEADD is a time intelligence function that shifts a date column forward or backward by a specified interval \u2014 days, months, quarters, or years. It’s the foundation of period-over-period comparisons.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This measure calculates total sales for the month immediately before the current filter context. If your report is filtered to March 2024, this measure returns February 2024 sales. Pair it with your current period measure in a line chart and you have month-over-month trend analysis with a few lines of DAX.<\/p>\n\n\n\n DATEADD requires a proper date table in your model \u2014 a table with a continuous date column, no gaps, marked as a date table in Power BI. This is a prerequisite for all time intelligence functions, not just DATEADD.<\/p>\n\n\n\n TOTALYTD calculates a year-to-date cumulative value. It’s one of the most commonly requested metrics in financial and operational reporting.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This accumulates sales from the beginning of the calendar year through whatever date is currently in context. For fiscal year reporting, you can specify a year-end date \u2014 for example, “06\/30” for a June 30 fiscal year end.<\/p>\n\n\n\n TOTALYTD is equivalent to writing CALCULATE with DATESYTD, but it’s more concise and readable, which makes it the preferred choice for straightforward YTD calculations.<\/p>\n\n\n\n RANKX assigns a rank to each row in a table based on an expression. It’s how you build dynamic leaderboards \u2014 top 10 salespeople, highest-performing products, best-converting campaigns \u2014 that update automatically when filters change.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This ranks each salesperson by their total sales, with the highest sales receiving rank 1. The ALL() wrapper ensures the ranking is based on all salespeople, not just those in the current filter context, so the ranks remain meaningful when slicers are applied.<\/p>\n\n\n\n DIVIDE performs division but handles the divide-by-zero error gracefully, returning a default value (usually blank or zero) instead of crashing the calculation.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n In real-world data, denominators frequently hit zero \u2014 a product with no sales, a region with no activity, a month with no orders. Using the CONCATENATEX combines values from a column across multiple rows into a single text string, with a delimiter of your choice. It’s the DAX equivalent of a string join operation.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This creates a comma-separated list of all product names. In dynamic reporting, CONCATENATEX is useful for tooltips that list the items contributing to a value, for generating summary text in cards, or for creating dynamic labels in custom visuals.<\/p>\n\n\n\n SELECTEDVALUE returns the value of a column when there’s exactly one distinct value in the current filter context. When a slicer has multiple values selected \u2014 or no value selected \u2014 it returns an alternate result instead.<\/p>\n\n\n\n Syntax:<\/strong> Example:<\/strong><\/p>\n\n\n\n This creates a dynamic report title that updates based on what’s selected in a Region slicer. If one region is selected, the title shows that region’s name. If multiple regions or no region is selected, it defaults to “All Regions.” It’s a small detail that makes reports feel polished and intentional.<\/p>\n\n\n\n If you’re just getting started, don’t try to learn everything at once. Focus on these six functions first, build proficiency with each one, and the rest of DAX will make much more sense once you have this foundation.<\/p>\n\n\n\n SUM is your starting point for any numeric aggregation. CALCULATE is the key to unlocking everything else \u2014 master its filter context logic as early as you can. IF handles the conditional logic you’ll need for KPI flags and status labels. COUNTROWS gives you flexible, reliable row counting for any dataset. FILTER pairs with CALCULATE to give you precise control over what’s included in a calculation. DISTINCTCOUNT answers one of the most common questions in analytics: how many unique customers, products, or events are there?<\/p>\n\n\n\n Build three or four practice dashboards using only these functions. By the time you’ve used each one across multiple real scenarios, the advanced functions will feel like natural extensions rather than new concepts.<\/p>\n\n\n\n Once you’re comfortable with the fundamentals, these functions open up significantly more sophisticated analysis.<\/p>\n\n\n\n RANKX<\/strong> enables dynamic ranking that responds to report filters, essential for leaderboards and performance dashboards. USERELATIONSHIP<\/strong> activates an inactive relationship between tables, allowing you to build calculations that use alternate join paths \u2014 for example, analyzing sales by ship date and order date using the same date table. SAMEPERIODLASTYEAR<\/strong> returns the same date range from the prior year, making year-over-year comparison straightforward and readable. PARALLELPERIOD<\/strong> is more flexible than SAMEPERIODLASTYEAR \u2014 it shifts the entire date range by a specified interval, allowing you to compare any period against the same-length period before it. GENERATE<\/strong> creates row-by-row table combinations, used in advanced data modeling scenarios. SUMX<\/strong> is an iterator function that evaluates an expression row by row across a table and then sums the results \u2014 essential when you need to calculate something at the row level before aggregating, like multiplying unit price by quantity for each transaction and then summing the totals.<\/p>\n\n\n\n Keeping formulas readable is the first and most important discipline. Format your DAX with line breaks and indentation, especially for CALCULATE formulas with multiple filter arguments. Power BI Desktop’s format button in the formula bar does this automatically \u2014 use it every time.<\/p>\n\n\n\n Use variables with the VAR keyword to store intermediate values and make calculations easier to follow. Instead of writing the same sub-expression twice inside a formula, store it once in a variable and reference it by name. Variables also improve performance by preventing Power BI from evaluating the same expression multiple times.<\/p>\n\n\n\n Meaningful measure names prevent a great deal of confusion when you or someone else returns to a report six months later. “Total Sales” is clear. “Calc1” is not. Name measures as if you’re explaining them to a colleague.<\/p>\n\n\n\n Test calculations step by step rather than writing the entire formula at once. If a complex CALCULATE formula isn’t working, strip it back to just the expression argument, confirm that works, then add one filter argument at a time. Identifying which piece is broken is much easier when you’re adding to a working formula than debugging everything at once.<\/p>\n\n\n\n The best starting point for structured DAX learning is Microsoft Learn<\/strong>, which offers free, official learning paths specifically for Power BI and DAX. The content is well-organized and regularly updated.<\/p>\n\n\n\n DAX Guide<\/strong> at dax.guide is an invaluable reference \u2014 every DAX function documented clearly with examples, syntax, and notes on behavior. Bookmark it and use it constantly.<\/p>\n\n\n\n SQLBI<\/strong> at sqlbi.com, run by Marco Russo and Alberto Ferrari, is the most authoritative source of advanced DAX knowledge on the internet. Their articles, videos, and the book “The Definitive Guide to DAX” are essential reading for anyone who wants to go beyond the basics.<\/p>\n\n\n\n YouTube has excellent free tutorials from channels like Guy in a Cube, Avi Singh, and Pragmatic Works. These are particularly useful for visual learners and for seeing DAX applied to real dashboard scenarios.<\/p>\n\n\n\n For practice, Kaggle datasets and the Microsoft sample datasets (Contoso, AdventureWorks) give you clean, well-structured data to build against. The fastest way to learn DAX is to write it against real data with a specific business question in mind \u2014 not to read about it in the abstract.<\/p>\n\n\n\n CALCULATE is almost universally considered the most important DAX function. It’s the foundation of nearly every advanced calculation \u2014 time intelligence, percentage of total, conditional aggregation, and dynamic filtering all depend on it. If you only invest deeply in understanding one function, make it CALCULATE.<\/p>\n\n\n\n DAX has a learning curve, but it’s not as steep as it initially appears. The syntax itself is manageable \u2014 what trips most people up is the concept of filter context, which behaves differently from anything in Excel or SQL. Once that concept clicks, the rest of DAX tends to fall into place relatively quickly. Most analysts reach functional proficiency within a few months of consistent practice.<\/p>\n\n\n\n SQL is a query language for retrieving and manipulating data stored in relational databases. DAX is a formula language for defining calculations within a data model. SQL runs before the data reaches your report \u2014 it shapes what data is available. DAX runs during report rendering \u2014 it defines how that data is calculated and displayed. Both skills are valuable for data analysts, and they complement each other well. Many DAX concepts \u2014 filtering, aggregation, relationships \u2014 will feel familiar to anyone who already knows SQL.<\/p>\n\n\n\n Power Query first. Power Query (M language) handles data transformation \u2014 connecting to sources, cleaning data, reshaping tables. It operates before the data model is built. DAX operates on the data model after it’s built. Learning Power Query first means you arrive at DAX with clean, well-structured data, which makes learning DAX significantly easier. That said, you don’t need to master Power Query before touching DAX \u2014 learn the basics of both in parallel, and deepen each as you encounter real problems.<\/p>\n\n\n\n You can become functional in DAX \u2014 able to write most common measures confidently \u2014 within two to three months of regular practice, assuming you’re working with Power BI consistently. Reaching advanced proficiency, where you’re comfortable with time intelligence, iterator functions, and complex filter manipulation, typically takes six months to a year of hands-on work. There’s no shortcut that replaces writing real formulas against real data for real reporting problems.<\/p>\n\n\n\n DAX is what turns Power BI from a visualization tool into a genuine analytical platform. The functions covered in this guide \u2014 from the fundamental SUM and IF to the powerful CALCULATE and RANKX \u2014 represent the core of what working analysts use every day to build dashboards that actually answer business questions.<\/p>\n\n\n\n Mastering DAX is one of the highest-leverage skills you can develop if your work involves data analysis or business intelligence. It’s in demand, it’s transferable across the Microsoft ecosystem, and it’s the kind of skill that compounds \u2014 every formula you write teaches you something that makes the next one easier.<\/p>\n","protected":false},"excerpt":{"rendered":" Most Power BI users start the same way \u2014 dragging fields onto a canvas, picking chart types, and feeling pretty […]<\/p>\n","protected":false},"author":1,"featured_media":21,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-19","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/statstable.com\/index.php?rest_route=\/wp\/v2\/posts\/19","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/statstable.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/statstable.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/statstable.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/statstable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=19"}],"version-history":[{"count":2,"href":"https:\/\/statstable.com\/index.php?rest_route=\/wp\/v2\/posts\/19\/revisions"}],"predecessor-version":[{"id":22,"href":"https:\/\/statstable.com\/index.php?rest_route=\/wp\/v2\/posts\/19\/revisions\/22"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/statstable.com\/index.php?rest_route=\/wp\/v2\/media\/21"}],"wp:attachment":[{"href":"https:\/\/statstable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=19"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statstable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=19"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statstable.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=19"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Categories of DAX Functions in Power BI<\/h2>\n\n\n\n
Top DAX Functions in Power BI<\/h2>\n\n\n\n
SUM()<\/h3>\n\n\n\n
SUM(ColumnName)<\/code><\/p>\n\n\n\nTotal Sales = SUM(Sales[SalesAmount])\n<\/code><\/pre>\n\n\n\nCALCULATE()<\/h3>\n\n\n\n
CALCULATE(Expression, Filter1, Filter2, ...)<\/code><\/p>\n\n\n\nTotal Sales West =\nCALCULATE([Total Sales], Region[Region] = \"West\")\n<\/code><\/pre>\n\n\n\nFILTER()<\/h3>\n\n\n\n
FILTER(Table, Condition)<\/code><\/p>\n\n\n\nHigh Value Sales =\nCALCULATE(\n SUM(Sales[SalesAmount]),\n FILTER(Sales, Sales[SalesAmount] > 10000)\n)\n<\/code><\/pre>\n\n\n\nCOUNT() vs COUNTA() vs COUNTROWS()<\/h3>\n\n\n\n
IF()<\/h3>\n\n\n\n
IF(LogicalTest, ValueIfTrue, ValueIfFalse)<\/code><\/p>\n\n\n\nProfit Status =\nIF([Profit] > 0, \"Profit\", \"Loss\")\n<\/code><\/pre>\n\n\n\nSWITCH()<\/h3>\n\n\n\n
SWITCH(Expression, Value1, Result1, Value2, Result2, ..., ElseResult)<\/code><\/p>\n\n\n\nSales Category =\nSWITCH(\n TRUE(),\n [Total Sales] >= 100000, \"High\",\n [Total Sales] >= 50000, \"Medium\",\n [Total Sales] >= 10000, \"Low\",\n \"Very Low\"\n)\n<\/code><\/pre>\n\n\n\nSWITCH(TRUE(), ...)<\/code> pattern is a common technique in DAX. It evaluates each condition in order and returns the result for the first one that’s true \u2014 effectively giving you an if\/else if\/else structure that’s far easier to read than equivalent nested IFs.<\/p>\n\n\n\nDISTINCTCOUNT()<\/h3>\n\n\n\n
DISTINCTCOUNT(ColumnName)<\/code><\/p>\n\n\n\nUnique Customers = DISTINCTCOUNT(Sales[CustomerID])\n<\/code><\/pre>\n\n\n\nRELATED()<\/h3>\n\n\n\n
RELATED(RelatedTableColumn)<\/code><\/p>\n\n\n\nProduct Category =\nRELATED(Products[Category])\n<\/code><\/pre>\n\n\n\nALL()<\/h3>\n\n\n\n
ALL(TableOrColumn)<\/code><\/p>\n\n\n\n% of Total Sales =\nDIVIDE(\n [Total Sales],\n CALCULATE([Total Sales], ALL(Sales))\n)\n<\/code><\/pre>\n\n\n\nDATEADD()<\/h3>\n\n\n\n
DATEADD(DateColumn, NumberOfIntervals, Interval)<\/code><\/p>\n\n\n\nPrevious Month Sales =\nCALCULATE([Total Sales], DATEADD(Date[Date], -1, MONTH))\n<\/code><\/pre>\n\n\n\nTOTALYTD()<\/h3>\n\n\n\n
TOTALYTD(Expression, DateColumn, [Filter], [YearEndDate])<\/code><\/p>\n\n\n\nYTD Sales =\nTOTALYTD([Total Sales], Date[Date])\n<\/code><\/pre>\n\n\n\nRANKX()<\/h3>\n\n\n\n
RANKX(Table, Expression, [Value], [Order], [Ties])<\/code><\/p>\n\n\n\nSales Rank =\nRANKX(ALL(Sales[Salesperson]), [Total Sales])\n<\/code><\/pre>\n\n\n\nDIVIDE()<\/h3>\n\n\n\n
DIVIDE(Numerator, Denominator, [AlternateResult])<\/code><\/p>\n\n\n\nProfit Margin =\nDIVIDE([Total Profit], [Total Sales], 0)\n<\/code><\/pre>\n\n\n\n\/<\/code> operator in those cases returns an error that breaks your visual. DIVIDE returns 0 (or blank) instead, keeping your dashboard clean. It’s best practice to use DIVIDE for any division in DAX rather than the \/<\/code> operator.<\/p>\n\n\n\nCONCATENATEX()<\/h3>\n\n\n\n
CONCATENATEX(Table, Expression, [Delimiter])<\/code><\/p>\n\n\n\nProduct List =\nCONCATENATEX(Products, Products[ProductName], \", \")\n<\/code><\/pre>\n\n\n\nSELECTEDVALUE()<\/h3>\n\n\n\n
SELECTEDVALUE(ColumnName, [AlternateResult])<\/code><\/p>\n\n\n\nReport Title =\n\"Sales Report \u2013 \" & SELECTEDVALUE(Region[Region], \"All Regions\")\n<\/code><\/pre>\n\n\n\nMost Important DAX Functions for Beginners<\/h2>\n\n\n\n
Advanced DAX Functions for Power BI Experts<\/h2>\n\n\n\n
Best Practices for Writing DAX Functions<\/h2>\n\n\n\n
Profit Margin =\nVAR TotalSales = [Total Sales]\nVAR TotalProfit = [Total Profit]\nRETURN\n DIVIDE(TotalProfit, TotalSales, 0)\n<\/code><\/pre>\n\n\n\nResources to Learn DAX Faster<\/h2>\n\n\n\n
Frequently Asked Questions<\/h2>\n\n\n\n
What is the most important DAX function in Power BI?<\/h3>\n\n\n\n
Is DAX difficult to learn?<\/h3>\n\n\n\n
What is the difference between DAX and SQL?<\/h3>\n\n\n\n
Should beginners learn DAX or Power Query first?<\/h3>\n\n\n\n
How long does it take to learn DAX?<\/h3>\n\n\n\n
Conclusion<\/h2>\n\n\n\n