💡 100+ Essential DAX Functions for Power BI: The Ultimate Cheat Sheet
🔹 1. Core Aggregation & Calculation
1. SUM(Sales[Amount])
2. AVERAGE(Sales[Amount])
3. MAX(Sales[Amount])
4. MIN(Sales[Amount])
5. COUNT(Sales[OrderID])
6. DISTINCTCOUNT(Sales[CustomerID])
7. COUNTROWS(FILTER(Sales, Sales[Region] = “East”))
8. DIVIDE([Total Sales], [Total Orders], 0)
9. CALCULATE(SUM(Sales[Amount]), Sales[Region] = “South”)
10. (FILTER(Sales, Sales[Region] = “West”), Sales[Amount])
🔹 2. Time Intelligence
11. TOTALYTD(SUM(Sales[Amount]), ‘Date'[Date])
12. TOTALMTD(SUM(Sales[Amount]), ‘Date'[Date])
13. TOTALQTD(SUM(Sales[Amount]), ‘Date'[Date])
14. SAMEPERIODLASTYEAR(‘Date'[Date])
15. DATEADD(‘Date'[Date], -1, YEAR)
16. PARALLELPERIOD(‘Date'[Date], -1, MONTH)
17. PREVIOUSYEAR(‘Date'[Date])
18. NEXTYEAR(‘Date'[Date])
19. DATESYTD(‘Date'[Date])
20. CALCULATE(SUM(Sales[Amount]), DATESYTD(‘Date'[Date]))
🔹 3. Filter Manipulation
21. FILTER(Sales, Sales[Amount] > 1000)
22. ALL(Sales)
23. ALLEXCEPT(Sales, Sales[Region])
24. REMOVEFILTERS(Sales[Region])
25. KEEPFILTERS(Sales[Category] = “Books”)
26. VALUES(Sales[Product])
27. SELECTEDVALUE(Sales[Category], “All”)
28. ISFILTERED(Sales[Category])
29. HASONEVALUE(Sales[Region])
30. CALCULATETABLE(Sales, Sales[Category] = “Furniture”)
31. RANKX(ALL(Sales[Product]), [Total Sales])
🔹 4. Ranking & Percentiles
32. PERCENTILEX.INC(Sales, Sales[Amount], 0.9)
33. PERCENTILEX.EXC(Sales, Sales[Amount], 0.95)
34. TOPN(5, Sales, Sales[Amount], DESC)
35. BOTTOMN(3, Sales, Sales[Amount], ASC)
36. NTILE(4)
37. QUARTILE.EXC()
38. QUARTILE.INC()
39. MEDIANX(Sales, Sales[Amount])
40. VARX.P(Sales, Sales[Amount])
🔹 5. Dynamic Titles and Labels
41. SELECTEDVALUE(‘Product'[Category])
42. “Sales for ” & SELECTEDVALUE(‘Product'[Category], “All Categories”)
43. IF(HASONEVALUE(Sales[Region]), VALUES(Sales[Region]), “Multiple Regions”)
44. FORMAT(MAX(‘Date'[Date]), “MMMM YYYY”)
45. “Current YTD Sales: ” & FORMAT([YTD Sales], “₹#,##0”)
🔹 6. Cumulative & Running Totals
46. CALCULATE([Total Sales], FILTER(ALL(‘Date’), ‘Date'[Date] <= MAX(‘Date'[Date])))
47. RUNNINGTOTAL := CALCULATE(SUM(Sales[Amount]), FILTER(ALLSELECTED(‘Date’), ‘Date'[Date] <= MAX(‘Date'[Date])))
48. CALCULATE([Total Sales], DATESBETWEEN(‘Date'[Date], DATE(2024,1,1), MAX(‘Date'[Date])))
49. CUMULATIVE SALES := CALCULATE([Sales], FILTER(ALL(‘Date’), ‘Date'[Date] <= EARLIER(‘Date'[Date])))
50. MOVINGAVERAGE := AVERAGEX(DATESINPERIOD(‘Date'[Date], LASTDATE(‘Date'[Date]), -3, MONTH), [Sales])
🔹 7. Logical & Conditional
51. IF([Profit] > 0, “Profit”, “Loss”)
52. SWITCH(TRUE(), [Score] >= 90, “A”, [Score] >= 80, “B”, “F”)
53. IFERROR([Amount] / [Count], 0)
54. OR([Condition1], [Condition2])
55. AND([A] > 100, [B] < 500)
56. NOT(ISBLANK([Sales]))
57. IF(ISFILTERED(Sales[Category]), “Filtered”, “Not Filtered”)
58. BLANK()
59. ISBLANK([Revenue])
60. ISINSCOPE(Sales[Product])
🔹 8. Text & String Operations
61. CONCATENATE(Sales[Region], Sales[City])
62. CONCATENATEX(VALUES(Sales[City]), Sales[City], “, “)
63. LEFT(Sales[Invoice], 5)
64. RIGHT(Sales[Invoice], 4)
65. MID(Sales[ProductCode], 3, 2)
66. SEARCH(“Pro”, Sales[ProductName], 1, -1)
67. SUBSTITUTE(Sales[Comment], “old”, “new”)
68. UPPER(Sales[City])
69. LOWER(Sales[City])
70. TRIM(Sales[CustomerName])
🔹 9. Mathematical & Financial
71. ROUND([Profit], 2)
72. ROUNDDOWN([Profit], 0)
73. ROUNDUP([Profit], 0)
74. INT(Sales[Amount])
75. MOD([Sales], 100)
76. ABS([Change])
77. POWER([Growth], 2)
78. SQRT([Variance])
79. LOG([Value])
80. EXP([Rate])
🔹 10. Advanced Relationships & Joins
81. RELATED(Product[Category])
82. RELATEDTABLE(Sales)
83. LOOKUPVALUE(Product[Category], Product[ProductID], Sales[ProductID])
84. USERELATIONSHIP(‘Sales'[OrderDate], ‘Date'[Date])
85. TREATAS(VALUES(‘Segment'[Segment]), ‘Customer'[Segment])
86. CROSSJOIN(Customer, Product)
87. UNION(Table1, Table2)
88. INTERSECT(Table1, Table2)
89. EXCEPT(Table1, Table2)
90. NATURALINNERJOIN(TableA, TableB)
🔹 11. Custom Tables & Modeling
91. GENERATESERIES(1, 12, 1)
92. ADDCOLUMNS(VALUES(Sales[Product]), “Total Sales”, [Sales])
93. SUMMARIZE(Sales, Sales[Region], “Sales”, [Total Sales])
94. GROUPBY(Sales, Sales[Category], “Total”, SUMX(CURRENTGROUP(), Sales[Amount]))
95. CALENDAR(DATE(2023,1,1), DATE(2023,12,31))
96. CALENDARAUTO()
97. SELECTCOLUMNS(Sales, “Product”, Sales[Product], “Amount”, Sales[Amount])
98. DATATABLE(…)
99. ADDCOLUMNS(GENERATESERIES(1, 5), “Square”, [Value] * [Value])
100. ISSELECTEDMEASURE([Sales]) (useful in calculation groups)
🔸 Performance & Model Optimization
100. ISINSCOPE() – Detects hierarchy levels in visuals; better than HASONEVALUE() for drill-down scenarios.
101. ROLLUPADDISSUBTOTAL() – Adds subtotal detection for row-level subtotals in a matrix visual.
102. ROLLUPISSUBTOTAL() – Checks if the current row is a subtotal in a matrix.
103. GROUPBY() – More performant version of SUMMARIZE() for complex grouping calculations.
104. ALLSELECTED() – Respects visual-level filters but ignores slicer filters; essential for dynamic totals.
105. REMOVEFILTERS() – More specific and readable alternative to ALL() in some filtering scenarios.
🔸 Dynamic Calculations
107. ISSELECTEDMEASURE() – Used in calculation groups to apply measure-specific logic dynamically.
108. SELECTEDMEASURENAME() – Returns the name of the selected measure in calculation groups.
109. SELECTEDMEASURE() – References the current measure dynamically within calculation groups.
🔸 Advanced Filter Logic
110. TREATAS() – Applies values as filters on unrelated tables; simulates relationships dynamically.
111. USERELATIONSHIP() – Activates an inactive relationship, useful for alternate lookups or time intelligence calculations.
🔸 Context Transition & Evaluation
112. EARLIER() – Retrieves a value from an earlier row context, typically used in calculated columns.
113. EARLIEST() – Similar to EARLIER(), but accesses the outermost row context for deeper nesting.
114. LOOKUPVALUE() – Retrieves a value from another table without needing an explicit relationship.
115. PATH() – Generates a delimited text path of parent-child hierarchy (e.g., org charts).
116. PATHITEM() – Extracts specific levels from the hierarchy path created by PATH().
🔸 Parent-Child Hierarchy
117. PATHLENGTH() – Returns the depth (length) of the hierarchy path.
118. PATHCONTAINS() – Checks if a given node exists in the parent path.
🔸 What-If & Simulation
119. GENERATESERIES() – Creates dynamic numeric series, ideal for what-if parameters like sliders.
120. DATATABLE() – Defines static tables with fixed values directly in DAX for lookups or scenarios.
🔸 Calendar & Date Workarounds
121. WEEKNUM() – Returns the week number (ISO or non-ISO).
122. STARTOFYEAR(), ENDOFYEAR() – Defines custom start or end of year dates for filtering.
123. NEXTQUARTER(), PREVIOUSQUARTER() – Useful for quarter-over-quarter comparisons.
🔸 Security & User Context
124. USERNAME() – Returns the current Power BI username, often used in row-level security.
125. USERPRINCIPALNAME() – Returns the logged-in user’s email or login ID for security filtering.
🔸 Debugging & Inspection
126. ADDCOLUMNS(…, “Debug”, [Measure]) – Add calculated columns to tables for debugging or inspection.
127. EXPLAIN() – (Limited availability) Used to analyze and explain DAX formula behavior and performance