DAX Functions Flashcards
1
Q
APPROXIMATEDISTINCTCOUNT(‘Sales’[Product])
A
This DAX code estimates the count of unique values in the ‘Product’ column of the ‘Sales’ table. The ‘APPROXIMATEDISTINCTCOUNT’ function provides a faster estimate of distinct values compared to ‘DISTINCTCOUNT’.
2
Q
AVERAGE(‘Sales’[SalesAmount])
A
Calculates the arithmetic mean of a column that contains numeric values.
3
Q
AVERAGEA(‘Sales’[SalesAmount])
A
The AVERAGEA function in DAX calculates the arithmetic mean (average) of a column, including text and logical values in the calculation. Text is considered as 0 and logical values are considered as 1 (TRUE) or 0 (FALSE).
- Treat all non-blank numeric values in SalesAmount as their numeric value.
- Treat all text values in SalesAmount as 0.
- Treat all TRUE logical values as 1, and FALSE as 0.
- Calculate the average of these values.
4
Q
AVERAGEX(‘Sales’, [SalesAmount] * [Quantity])
A
The AVERAGEX function in DAX is an iterator that calculates the average (arithmetic mean) of an expression evaluated over a table. It’s different from AVERAGE because it allows for a much more dynamic calculation that can include not just the contents of a column, but also the result of a calculation based on multiple columns for each row.
5
Q
COUNT(‘Sales’[CustomerID])
A
The COUNT function in DAX is used to count the number of rows in a column that contain non-blank values. It’s important to note that COUNT only works with numeric data and will not count text or other non-numeric values. If you need to count rows with non-numeric data, you would use COUNTA
6
Q
COUNTA(‘Sales’[CustomerName])
A
The COUNTA function in DAX counts the number of non-empty values in a column, including numbers, text, or any other type of data. It does not count blank or null values.
7
Q
COUNTAX(‘Sales’, [SalesAmount] > 100)
A
The COUNTAX function in DAX is used to count non-blank results when evaluating an expression over a table. Unlike COUNT and COUNTA, which operate directly on a single column, COUNTAX applies a given expression to each row of a table and then counts the number of times that expression evaluates to a non-blank result.
8
Q
COUNTBLANK(‘Sales’[DiscountAmount])
A
The COUNTBLANK function in DAX is used to count the number of blank or null values in a specified column. This function is particularly useful for identifying how many entries in a column are missing or have not been recorded.
9
Q
COUNTROWS(‘Sales’)
A
The COUNTROWS function in DAX is used to count the number of rows in a table or in a table expression. It’s a straightforward way to determine the total number of entries (or records) in a given table.
10
Q
COUNTX(‘Sales’, [SalesAmount] > 100)
A
The COUNTX function in DAX is an iterator function that counts the number of rows in a table where an expression evaluates to a non-blank result. It’s particularly useful when you want to apply a condition to each row of a table and count the number of rows that meet this condition.
11
Q
DISTINCTCOUNT(‘Sales’[Product])
A
The DISTINCTCOUNT function in DAX counts the number of unique values in a column. It’s commonly used to determine the cardinality of categorical data.
12
Q
DISTINCTCOUNTNOBLANK(‘Sales’[Product])
A
The DISTINCTCOUNTNOBLANK function in DAX is similar to DISTINCTCOUNT but it excludes blank values from the count of unique values in a column.
13
Q
MAX(‘Sales’[SalesAmount])
A
The MAX function in DAX identifies the largest numeric value in a column. It’s useful for finding the maximum value in a range of numbers.
14
Q
MAXA(‘Sales’[OrderDate])
A
The MAXA function in DAX evaluates both numeric and non-numeric values (including text represented as numbers and logical values) to find the largest value in a column.
15
Q
MAXX(‘Sales’, [SalesAmount] * [Quantity])
A
The MAXX function in DAX is an iterator that evaluates an expression for each row in a table and returns the largest result. It allows for complex calculations that can involve multiple columns.
16
Q
MIN(‘Sales’[SalesAmount])
A
The MIN function in DAX finds the smallest numeric value in a column. It is typically used to identify the minimum value in a dataset.
17
Q
MINA(‘Sales’[OrderDate])
A
The MINA function in DAX works like MAXA but returns the smallest value in a column, considering all types of values including text and logicals.
18
Q
MINX(‘Sales’, [SalesAmount] * [Quantity])
A
The MINX function in DAX evaluates an expression for each row in a table and returns the smallest result. It is an iterator that can handle complex row-by-row calculations.
19
Q
PRODUCT(‘Sales’[Quantity])
A
The PRODUCT function in DAX multiplies all the numbers in a column and returns the total product. It’s useful for calculating cumulative product values.
20
Q
PRODUCTX(‘Sales’, [SalesAmount] * [Quantity])
A
The PRODUCTX function in DAX is an iterator that calculates the product of an expression evaluated for each row in a table. This function can perform row-context-specific multiplications before aggregating the result.
21
Q
SUM(‘Sales’[SalesAmount])
A
The SUM function in DAX adds all the numbers in a column, providing a total sum. It’s one of the most commonly used functions for basic aggregation.
22
Q
SUMX(‘Sales’, [SalesAmount] * [Quantity])
A
The SUMX function in DAX sums up the results of an expression evaluated for each row in a table. It combines the flexibility of row-wise calculations with the simplicity of aggregation.
23
Q
CALENDAR(DATE(2022,1,1), DATE(2022,12,31))
A
The CALENDAR function generates a table with a single column “Date” containing a contiguous set of dates between the specified start and end dates. In this example, it creates a calendar table for the year 2022.
24
Q
CALENDARAUTO()
A
This function generates a calendar table with a single column “Date” containing a contiguous set of dates automatically determined by the data in your model. It doesn’t require explicit date range parameters.
25
DATE(2022, 2, 15)
The DATE function returns a datetime value for the specified year, month, and day. In this example, it creates a datetime value for February 15, 2022.
26
DATEDIFF('Sales'[OrderDate], 'Sales'[ShipDate], DAY)
This function calculates the number of days between two dates. In this example, it calculates the number of days between the 'OrderDate' and 'ShipDate' columns in the 'Sales' table. The third argument specifies the interval as "DAY".
27
DATEVALUE("2022-01-31")
The DATEVALUE function converts a date in text format to a datetime value. In this example, it converts the text "2022-01-31" to a datetime value.
28
DAY('Sales'[OrderDate])
The DAY function returns the day of the month (a number from 1 to 31) for a given date. In this example, it returns the day of the month for the 'OrderDate' column in the 'Sales' table.
29
EDATE('Sales'[StartDate], 3)
The EDATE function returns a date that is a specified number of months before or after a given start date. In this example, it calculates a date that is 3 months after the 'StartDate' column in the 'Sales' table.
30
EOMONTH(TODAY(), -1)
The EOMONTH function returns the last day of the month before or after a specified number of months from a given date. In this example, it calculates the last day of the month one month before today's date.
31
HOUR('Sales'[OrderTime])
The HOUR function returns the hour (as a number from 0 to 23) for a given datetime value. In this example, it returns the hour of the 'OrderTime' column in the 'Sales' table.
32
MINUTE('Sales'[StartTime])
The MINUTE function returns the minute (a number from 0 to 59) for a given datetime value. In this example, it returns the minute of the 'StartTime' column in the 'Sales' table.
33
MONTH('Sales'[InvoiceDate])
The MONTH function returns the month (a number from 1 to 12) for a given date. In this example, it returns the month of the 'InvoiceDate' column in the 'Sales' table.
34
NETWORKDAYS('Calendar'[Date], TODAY())
The NETWORKDAYS function calculates the number of whole workdays between two dates, considering weekends and optionally specified holidays. In this example, it calculates the network days between a date in the 'Calendar' table and today's date.
35
NOW()
The NOW function returns the current date and time in datetime format. In this example, it returns the current date and time.
36
QUARTER('Sales'[OrderDate])
The QUARTER function returns the quarter of the year (a number from 1 to 4) for a given date. In this example, it returns the quarter of the 'OrderDate' column in the 'Sales' table.
37
SECOND('Sales'[EndTime])
The SECOND function returns the seconds (a number from 0 to 59) of a given time value. In this example, it returns the seconds of the 'EndTime' column in the 'Sales' table.
38
TIME(15, 30, 0)
The TIME function converts hours, minutes, and seconds given as numbers into a time in datetime format. In this example, it creates a time value for 3:30 PM.
39
TIMEVALUE("09:45:00 AM")
The TIMEVALUE function converts a time in text format to a time in datetime format. In this example, it converts the text "09:45:00 AM" to a time value.
40
TODAY()
The TODAY function returns the current date. In this example, it returns today's date.
41
UTCNOW()
The UTCNOW function returns the current UTC (Coordinated Universal Time) date and time. In this example, it returns the current UTC date and time.
42
UTCTODAY()
The UTCTODAY function returns the current UTC date. In this example, it returns the current UTC date.
43
WEEKDAY('Calendar'[Date])
The WEEKDAY function returns a number from 1 to 7 identifying the day of the week for a given date. In this example, it returns the day of the week for a date in the 'Calendar' table.
44
WEEKNUM('Calendar'[Date], 2)
The WEEKNUM function returns the week number for a given date and year according to the specified return_type value. In this example, it calculates the week number for a date in the 'Calendar' table using return_type 2 (ISO week numbering).
45
YEAR('Sales'[OrderDate])
The YEAR function returns the year of a date as a four-digit integer in the range 1900-9999. In this example, it returns the year of the 'OrderDate' column in the 'Sales' table.
46
YEARFRAC('Sales'[StartDate], 'Sales'[EndDate])
The YEARFRAC function calculates the fraction of the year represented by the number of whole days between two dates. In this example, it calculates the year fraction between the 'StartDate' and 'EndDate' columns in the 'Sales' table.
47
ALL('Sales')
The ALL function returns all the rows in a table, ignoring any filters that might have been applied. In this example, it returns all rows in the 'Sales' table.
48
ALLCROSSFILTERED()
This function clears all filters that are applied to a table, removing any cross-filtering effects. It doesn't require any specific arguments.
49
ALLEXCEPT('Sales', 'Sales'[ProductCategory])
The ALLEXCEPT function removes all context filters in the 'Sales' table except for the filters applied to the 'ProductCategory' column. It retains filters on that specified column.
50
ALLNOBLANKROW('Product')
The ALLNOBLANKROW function returns all rows of a table except the blank row and disregards any context filters. In this example, it returns all distinct values of the 'Product' column except the blank row.
51
ALLSELECTED('Date'[Date])
The ALLSELECTED function removes context filters from columns and rows in the current query while retaining all other context filters or explicit filters. In this example, it removes filters from the 'Date' table's 'Date' column.
52
CALCULATE(SUM('Sales'[SalesAmount]), 'Product'[Category] = "Electronics")
The CALCULATE function evaluates an expression in a modified filter context. In this example, it calculates the sum of 'SalesAmount' in the 'Sales' table while filtering the 'Product' table by the category "Electronics".
53
CALCULATETABLE('Sales', 'Sales'[Quantity] > 5)
The CALCULATETABLE function evaluates a table expression in a modified filter context. In this example, it returns a new table consisting of rows from the 'Sales' table where the 'Quantity' is greater than 5.
54
EARLIER('Sales'[Product])
The EARLIER function returns the current value of the specified column in an outer evaluation pass of the mentioned column. In this example, it returns the 'Product' column value from the outer context.
55
EARLIEST('Sales'[OrderDate])
The EARLIEST function returns the current value of the specified column in an outer evaluation pass of the 'OrderDate' column. It is typically used in time intelligence calculations.
56
FILTER('Sales', 'Sales'[Quantity] > 10)
The FILTER function returns a table that represents a subset of another table or expression based on specified conditions. In this example, it filters the 'Sales' table to include only rows where the 'Quantity' is greater than 10.
57
INDEX('Products', 3)
The INDEX function returns a row at an absolute position specified by the position parameter within the specified table. In this example, it returns the third row from the 'Products' table.
58
KEEPFILTERS(CALCULATE(SUM('Sales'[SalesAmount]), 'Product'[Category] = "Electronics"))
The KEEPFILTERS function modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function. It retains the existing filters when calculating the sum of 'SalesAmount' while filtering the 'Product' table by the category "Electronics".
59
LOOKUPVALUE('Sales'[ProductName], 'Sales'[ProductID], 101)
The LOOKUPVALUE function returns the value for the row that meets all criteria specified by search conditions. In this example, it looks up the 'ProductName' where the 'ProductID' is 101 in the 'Sales' table.
60
RANKX('Sales', [SalesAmount], , DESC)
In window functions, the MATCHBY parameter defines the columns used to determine how to match data and identify the current row. This example uses 'RANKX' and defines the sorting and ranking order for 'SalesAmount'.
61
OFFSET('Sales', 1)
The OFFSET function returns a single row positioned either before or after the current row within the same table, by a given offset. In this example, it returns the row that is 1 position after the current row in the 'Sales' table.
62
ORDERBY('Sales', 'Sales'[OrderDate], ASC)
In window functions, the ORDERBY function defines the columns that determine the sort order within each partition. This example orders the 'Sales' table by 'OrderDate' in ascending order.
63
PARTITIONBY('Sales', 'Sales'[Category])
In window functions, the PARTITIONBY function defines the columns used to partition a WINDOW function's relation parameter. This example partitions the 'Sales' table by the 'Category' column.
64
RANKX('Sales', [SalesAmount], , DESC)
In window functions, the RANK function returns the ranking of a row within the given interval. This example calculates the rank of each row in the 'Sales' table based on 'SalesAmount' in descending order.
65
REMOVEFILTERS('Product', 'Category')
The REMOVEFILTERS function clears filters from the specified tables or columns. In this example, it removes filters from the 'Product' and 'Category' tables.
66
ROWNUMBER('Sales', [SalesAmount], , DESC)
In window functions, the ROWNUMBER function returns the unique ranking of a row within the given interval. This example calculates a unique sequential number for each row in the 'Sales' table based on 'SalesAmount' in descending order.
67
SELECTEDVALUE('Product'[Category], "Default")
The SELECTEDVALUE function returns the value when the context for the columnName has been filtered down to one distinct value only. Otherwise, it returns the alternateResult. In this example, it returns the selected 'Category' value from the 'Product' table or "Default" if multiple values exist.
68
WINDOW(MAX('Sales'[OrderDate]), -1, 1)
The WINDOW function returns multiple rows positioned within the given interval. In this example, it returns rows within a window defined by the maximum 'OrderDate' value, one row before, and one row after that maximum value from the 'Sales' table.
69
COLUMNSTATISTICS('Sales')
The COLUMNSTATISTICS function returns a table of statistics regarding every column in the 'Sales' table (or any specified table), providing insights into column characteristics like minimum, maximum, and average values.
70
CONTAINS('Products'[Category], "Electronics")
The CONTAINS function checks if the 'Category' column in the 'Products' table contains the value "Electronics" and returns TRUE if it does, or FALSE if it doesn't.
71
CONTAINSROW('Customers', 'Customers'[CustomerID], 101)
The CONTAINSROW function checks if a row of values exists in the 'Customers' table (based on the 'CustomerID' column) with a value of 101 and returns TRUE if it does, otherwise, it returns FALSE.
72
CONTAINSSTRING('TextData'[Description], "important")
The CONTAINSSTRING function checks if the 'Description' column in the 'TextData' table contains the string "important" and returns TRUE if it does, or FALSE if it doesn't.
73
CONTAINSSTRINGEXACT('TextData'[Title], "Exact Match")
The CONTAINSSTRINGEXACT function checks if the 'Title' column in the 'TextData' table contains the exact string "Exact Match" and returns TRUE if it's an exact match, otherwise, it returns FALSE.
74
CUSTOMDATA()
The CUSTOMDATA function returns the content of the CustomData property in the connection string, providing custom data or metadata related to the data source.
75
HASONEFILTER('Product'[Category])
The HASONEFILTER function returns TRUE when there is only one directly filtered value on the 'Category' column in the 'Product' table, indicating single-value filtering.
76
HASONEVALUE('Customer'[CustomerID])
The HASONEVALUE function returns TRUE when the context for 'CustomerID' has been filtered down to one distinct value only in the 'Customer' table. Otherwise, it returns FALSE.
77
ISAFTER('Date'[Date], DATE(2022, 1, 1))
The ISAFTER function emulates the behavior of a Start At clause and returns true for a row where the 'Date' column is on or after January 1, 2022.
78
ISBLANK('Sales'[Revenue])
The ISBLANK function checks whether the 'Revenue' column in the 'Sales' table is blank (no revenue recorded) and returns TRUE or FALSE accordingly.
79
ISCROSSFILTERED('Order'[ProductID])
The ISCROSSFILTERED function returns TRUE when the 'ProductID' column in the 'Order' table or another related table is being filtered directly, indicating cross-filtering.
80
ISEMPTY('EmptyTable')
The ISEMPTY function checks if the 'EmptyTable' is empty (contains no rows) and returns TRUE if it's empty, otherwise, it returns FALSE.
81
ISERROR([SalesAmount])
Checks if the value in the 'SalesAmount' column is an error. If it is, it returns TRUE; otherwise, it returns FALSE.
82
ISEVEN([OrderQuantity])
Determines if the 'OrderQuantity' is an even number and returns TRUE if it is, or FALSE if it's odd.
83
ISFILTERED('Date'[Year])
Verifies if the 'Year' column in the 'Date' table is currently filtered. Returns TRUE if filtered, FALSE if not.
84
ISINSCOPE('Product'[Category])
Checks if the 'Category' column in the 'Product' table is currently in scope. Returns TRUE if it is, FALSE if not.
85
ISLOGICAL([IsCompleted])
Determines if the value in the 'IsCompleted' column is a logical value (TRUE or FALSE). Returns TRUE for logical values, otherwise FALSE.
86
ISNONTEXT([ProductName])
Checks if the 'ProductName' column contains non-text values (blank cells are considered non-text). Returns TRUE if non-text, FALSE if text.
87
ISNUMBER([UnitPrice])
Determines if the values in the 'UnitPrice' column are numbers. Returns TRUE for numbers, FALSE for non-numeric values.
88
ISODD([InvoiceNumber])
Checks if the 'InvoiceNumber' is an odd number and returns TRUE if it is, or FALSE if it's even.
89
ISONORAFTER([TransactionDate], "2022-01-01")
Emulates the behavior of a Start At clause and returns TRUE for rows with 'TransactionDate' after January 1, 2022.
90
ISSELECTEDMEASURE([Total Sales], [Total Profit], [Revenue])
Checks if one of the specified measures is selected in the current context. Returns TRUE if selected, otherwise FALSE.
91
ISSUBTOTAL([IsSubtotal], [Category])
Creates a column in a SUMMARIZE expression that returns TRUE if the row contains subtotal values for the 'Category' column, otherwise FALSE.
92
ISTEXT([CustomerComments])
Determines if the values in the 'CustomerComments' column are text. Returns TRUE for text values, FALSE for non-text.
93
NONVISUAL('Region'[Region])
Marks a value filter in a SUMMARIZECOLUMNS expression as non-visual for the 'Region' column.
94
SELECTEDMEASURE()
Returns the name of the currently selected measure in the context.
95
SELECTEDMEASUREFORMATSTRING()
Returns the format string of the currently selected measure in the context.
96
SELECTEDMEASURENAME()
Returns the name of the currently selected measure in the context.
97
USERCULTURE()
Returns the locale for the current user.
98
USERNAME()
Returns the domain name and username from the credentials given to the system at connection time.
99
USEROBJECTID()
Returns the current user's Object ID or SID.
100
USERPRINCIPALNAME()
Returns the user principal name.
101
AND([IsCompleted], [IsApproved])
Checks whether both 'IsCompleted' and 'IsApproved' columns are TRUE, and returns TRUE if both are TRUE; otherwise, returns FALSE.
102
BITAND(5, 3)
Performs a bitwise 'AND' operation on the binary representations of 5 and 3 and returns the result.
103
BITLSHIFT(4, 2)
Shifts the binary representation of 4 two bits to the left and returns the result.
104
BITOR(2, 4)
Performs a bitwise 'OR' operation on the binary representations of 2 and 4 and returns the result.
105
BITRSHIFT(16, 2)
Shifts the binary representation of 16 two bits to the right and returns the result.
106
BITXOR(5, 3)
Performs a bitwise 'XOR' operation on the binary representations of 5 and 3 and returns the result.
107
COALESCE([Column1], [Column2], [Column3])
Returns the first non-blank value among 'Column1', 'Column2', and 'Column3'.
108
FALSE()
Returns the logical value FALSE.
109
IF([Sales] > 1000, "High", "Low")
Checks if 'Sales' is greater than 1000. If TRUE, returns "High"; otherwise, returns "Low".
110
IF.EAGER([Total Sales] > [Total Budget], "Over Budget", "On Budget")
Checks if 'Total Sales' is greater than 'Total Budget'. If TRUE, returns "Over Budget"; otherwise, returns "On Budget".
111
IFERROR([Dividend] / [Divisor], 0)
Divides 'Dividend' by 'Divisor' and returns the result. If there's an error, it returns 0.
112
NOT([IsApproved])
Negates the value in the 'IsApproved' column. Returns TRUE if 'IsApproved' is FALSE and vice versa.
113
OR([IsCompleted], [IsApproved])
Checks if either 'IsCompleted' or 'IsApproved' (or both) are TRUE and returns TRUE if at least one is TRUE.
114
SWITCH([Category], "A", "Category A", "B", "Category B", "Other")
Evaluates the 'Category' column and returns different values based on specified conditions.
115
TRUE()
Returns the logical value TRUE.
116
ABS([Profit])
Returns the absolute value of 'Profit', ensuring it's a positive number.
117
CEILING([Price], 10)
Rounds 'Price' up to the nearest multiple of 10.
118
CONVERT([Distance], "m", "km")
Converts the 'Distance' value from meters to kilometers.
119
CURRENCY([Amount])
Evaluates the 'Amount' and returns the result as currency data type.
120
DEGREES([Radians])
Converts the value in 'Radians' into degrees.
121
DIVIDE([Numerator], [Denominator])
Performs division of 'Numerator' by 'Denominator'. Returns a result or BLANK() if 'Denominator' is 0.
122
EVEN([Number])
Rounds 'Number' up to the nearest even integer.
123
EXP([Exponent])
Returns the value of e raised to the power of 'Exponent'.
124
FACT([Number])
Returns the factorial of a number, equal to the series 123*...* up to the given 'Number'.
125
FLOOR([Value], [Significance])
Rounds 'Value' down, toward zero, to the nearest multiple of 'Significance'.
126
GCD([Number1], [Number2])
Returns the greatest common divisor of 'Number1' and 'Number2'.
127
INT([Number])
Rounds 'Number' down to the nearest integer.
128
ISO.CEILING([Value], [Significance])
Rounds 'Value' up, to the nearest integer or to the nearest multiple of 'Significance'.
129
LCM([Number1], [Number2])
Returns the least common multiple of 'Number1' and 'Number2'.
130
LN([Number])
Returns the natural logarithm of 'Number'.
131
LOG([Number], [Base])
Returns the logarithm of 'Number' to the specified 'Base'.
132
LOG10([Number])
Returns the base-10 logarithm of 'Number'.
133
MOD([Dividend], [Divisor])
Returns the remainder after 'Dividend' is divided by 'Divisor'. The result has the same sign as the 'Divisor'.
134
MROUND([Value], [Multiple])
Returns 'Value' rounded to the desired 'Multiple'.
135
ODD([Number])
Rounds 'Number' up to the nearest odd integer.
136
PI()
Returns the value of Pi, approximately 3.14159265358979.
137
POWER([Base], [Exponent])
Returns the result of 'Base' raised to the power of 'Exponent'.
138
QUOTIENT([Numerator], [Denominator])
Performs division of 'Numerator' by 'Denominator' and returns only the integer portion of the division result.
139
RADIANS([Degrees])
Converts the value in 'Degrees' to radians.
140
RAND()
Returns a random number greater than or equal to 0 and less than 1, evenly distributed.
141
RANDBETWEEN([Min], [Max])
Returns a random number in the range between 'Min' and 'Max' (inclusive).
142
ROUND([Value], [DecimalPlaces])
Rounds 'Value' to the specified number of 'DecimalPlaces'.
143
ROUNDDOWN([Value], [DecimalPlaces])
Rounds 'Value' down, toward zero, to the specified number of 'DecimalPlaces'.
144
ROUNDUP([Value], [DecimalPlaces])
Rounds 'Value' up, away from zero, to the specified number of 'DecimalPlaces'.
145
SIGN([Number])
Determines the sign of 'Number'. Returns 1 if positive, -1 if negative, and 0 if zero.
146
SQRT([Number])
Returns the square root of 'Number'.
147
SQRTPI([Number])
Returns the square root of ('Number' * Pi).
148
TRUNC([Number])
Truncates 'Number' to an integer by removing the decimal or fractional part.
149
BLANK()
Returns a blank value.
150
ERROR("An error occurred.")
Raises an error with the specified error message.
151
EVALUATEANDLOG([Expression], "Log message")
Evaluates the provided 'Expression' and logs it in a DAX Evaluation Log profiler event with the specified message.
152
TOCSV([Table], "FileName.csv")
Converts the 'Table' into a string in CSV format and optionally saves it to a file named "FileName.csv".
153
TOJSON([Table], "FileName.json")
Converts the 'Table' into a string in JSON format and optionally saves it to a file named "FileName.json".
154
PATH("Category", "Subcategory", "Product")
Returns a delimited text string with the identifiers of all the parents of the current identifier.
155
PATHCONTAINS("Category/Subcategory/Product", "Subcategory")
Returns TRUE if the specified 'item' exists within the specified 'path'.
156
PATHITEM("Category/Subcategory/Product", 2)
Returns the item at the specified 'position' from a string resulting from the evaluation of a PATH function.
157
PATHITEMREVERSE("Category/Subcategory/Product", 1)
Returns the item at the specified 'position' from a string resulting from the evaluation of a PATH function, in reverse order.
158
PATHLENGTH("Category/Subcategory/Product")
Returns the number of parents to the specified item in a given PATH result, including itself.
159
CROSSFILTER('Sales'[ProductID], 'Products'[ProductID], Both)
Specifies the cross-filtering direction between the 'Sales' and 'Products' tables as 'Both'.
160
RELATED('Category'[CategoryName])
Returns a related value from the 'Category' table based on the relationship.
161
RELATEDTABLE('Sales')
Evaluates a table expression in a context modified by the filters applied to the 'Sales' table.
162
USERELATIONSHIP('Customers'[CustomerID], 'Orders'[CustomerID])
Specifies the relationship between 'Customers' and 'Orders' tables to be used in a specific calculation.
163
MEDIAN('Sales'[Quantity])
Calculates the median for a column of values.
164
MEDIANX('Sales', 'Sales'[Quantity])
Calculates the median for a column within a table.
165
RANK.EQ('Sales'[Sales], 500)
Returns the rank of a value in a column with ties assigned the average rank.
166
RANKX('Sales', 'Sales'[Profit], , ASC, DENSE)
Returns the rank of values within a table with optional parameters.
167
SAMPLE('Products', 5)
Returns a random sample of rows from a table.
168
ADDCOLUMNS('Sales', "Revenue", 'Sales'[Amount] * 'Sales'[Quantity])
Adds a calculated column to a table or table expression.
169
ADDMISSINGITEMS('Products', 'Products'[Category])
Adds missing items to a table based on a specified column.
170
CROSSJOIN('Customers', 'Products')
Creates a new table by performing a cross join between two tables.
171
CURRENTGROUP()
Returns a table representing the current group within an iteration.
172
DATATABLE("Category", STRING, "Amount", INTEGER, {{"A", 100}, {"B", 200}})
Creates a table in DAX with specified columns and values.
173
DETAILROWS('Sales')
Returns a table with all rows that contribute to a cell value in a pivot table.
174
DISTINCT('Products'[Category])
Returns a table with distinct values from a specific column.
175
DISTINCT('Products')
Returns a table with distinct rows from a table.
176
EXCEPT('Customers', FILTER('Customers', 'Customers'[Country] = "USA"))
Returns the set difference between two tables.
177
FILTERS('Customers')
Returns a table with all active filters on a table.
178
GENERATE('Dates', 'Products')
Creates a table by applying a function to each row from two input tables.
179
GENERATEALL('Dates', 'Products')
Creates a table by applying a function to each row from two input tables, preserving all rows.
180
GENERATESERIES(1, 10, 1)
Generates a table with a single column containing a series of numbers.
181
GROUPBY('Sales', 'Sales'[Category], "Total Sales", SUMX(CURRENTGROUP(), 'Sales'[Amount]))
Groups a table by specified columns and aggregates data.
182
IGNORE('Customers', 'Customers'[Age])
Returns a table with the same columns as the input table, ignoring the specified column.
183
INTERSECT('Customers', FILTER('Customers', 'Customers'[Country] = "USA"))
Returns the intersection of two tables.
184
NATURALINNERJOIN('Customers', 'Orders')
Performs a natural inner join between two tables using common column names.
185
NATURALLEFTOUTERJOIN('Customers', 'Orders')
Performs a natural left outer join between two tables using common column names.
186
ROLLUP('Date'[Year], 'Date'[Quarter], 'Date'[Month])
Creates a rollup table with aggregated values based on specified columns.
187
ROLLUPADDISSUBTOTAL('Date'[Year], 'Date'[Quarter], 'Date'[Month])
Adds subtotals to a rollup table based on specified columns.
188
ROLLUPISSUBTOTAL('Date'[Year], 'Date'[Quarter], 'Date'[Month])
Checks if a row in a rollup table is a subtotal row.
189
ROLLUPGROUP('Date'[Year], 'Date'[Quarter], 'Date'[Month])
Returns the group name for a row in a rollup table.
190
ROW("Product", "A", "Price", 10)
Creates a single-row table with specified column names and values.
191
SELECTCOLUMNS('Sales', "Product", 'Products'[Name])
Creates a new table by selecting columns from an existing table or table expression.
192
SUBSTITUTEWITHINDEX("Hello World", "o", 3)
Replaces a character in a text string at a specified position with another character.
193
SUMMARIZE('Sales', 'Sales'[Category], "Total Sales", SUM('Sales'[Amount]))
Aggregates data and groups rows based on specified columns.
194
SUMMARIZECOLUMNS('Sales', 'Sales'[Category], "Total Sales", SUM('Sales'[Amount]))
Aggregates data and groups rows based on specified columns, allowing additional calculations.
195
SUMMARIZECOLUMNS("Product", {"A", "B", "C"}, "Sales", {100, 200, 150})
Creates a table with specified column names and values.
196
TOPN(5, 'Sales', 'Sales'[Amount])
Returns the top N rows from a table based on a specified column's values.
197
TREATAS({'New York', 'California'}, 'Customers'[State])
Modifies filter context by treating specified values as if they were part of a column.
198
UNION('Table1', 'Table2')
Combines two or more tables into a single table, stacking rows on top of each other.
199
VALUES('Products'[Category])
Returns a table with distinct values from a specified column.
200
COMBINEVALUES(":", 'Products'[Category], 'Products'[Subcategory])
Combines multiple columns into a single text string, separated by a delimiter.
201
CONCATENATE("Hello", " ", "World")
Combines multiple text strings into a single text string.
202
CONCATENATEX('Sales', 'Sales'[Product], ", ")
Combines multiple text values from a column into a single text string, separated by a delimiter.
203
EXACT("Hello", "HELLO")
Checks if two text strings are exactly the same and returns TRUE or FALSE.
204
FIND("lo", "Hello")
Searches for a text string within another text string and returns the starting position.
205
FIXED(1234.567, 2)
Converts a number into a text string with a specified number of decimal places.
206
FORMAT('Dates'[Date], "MMM yyyy")
Formats a date or time value as a text string using a specified format.
207
LEFT("Hello", 3)
Returns a specified number of characters from the beginning of a text string.
208
LEN("Hello")
Returns the number of characters in a text string.
209
LOWER("Hello")
Converts all characters in a text string to lowercase.
210
MID("Hello", 2, 3)
Returns a specified number of characters from a text string, starting at a specified position.
211
REPLACE("Hello World", "World", "Universe")
Replaces occurrences of a substring within a text string with another substring.
212
REPT("*", 5)
Repeats a text string a specified number of times and returns the result.
213
RIGHT("Hello", 3)
Returns a specified number of characters from the end of a text string.
214
SEARCH("lo", "Hello")
Searches for a text string within another text string and returns the starting position.
215
SUBSTITUTE("Hello World", "World", "Universe")
Replaces occurrences of a substring within a text string with another substring.
216
TRIM(" Hello ")
Removes leading and trailing spaces from a text string.
217
UNICHAR(65)
Returns the Unicode character with the specified code point.
218
UNICODE("A")
Returns the Unicode code point for the first character in a text string.
219
UPPER("Hello")
Converts all characters in a text string to uppercase.
220
VALUE("123")
Converts a text string that represents a number into a numeric value.
221
CLOSINGBALANCEMONTH('Sales'[Amount], 'Date'[Date], 1)
Calculates the closing balance of a measure for the current month.
222
CLOSINGBALANCEQUARTER('Sales'[Amount], 'Date'[Date], 1)
Calculates the closing balance of a measure for the current quarter.
223
CLOSINGBALANCEYEAR('Sales'[Amount], 'Date'[Date], 1)
Calculates the closing balance of a measure for the current year.
224
DATEADD('Date'[Date], -1, MONTH)
Adds or subtracts a specified number of units (e.g., days, months) to a date.
225
DATESBETWEEN('Date'[Date], 'Date'[Start], 'Date'[End])
Returns a table of dates between two specified dates.
226
DATESINPERIOD('Date'[Date], TODAY(), -30, DAY)
Returns a table of dates that fall within a specified time period relative to a given date.
227
DATESMTD('Date'[Date])
Returns a table of dates for the current month-to-date based on a date column.
228
DATESQTD('Date'[Date])
Returns a table of dates for the current quarter-to-date based on a date column.
229
DATESYTD('Date'[Date])
Returns a table of dates for the current year-to-date based on a date column.
230
ENDOFMONTH('Date'[Date])
Returns the last day of the month for a given date.
231
ENDOFQUARTER('Date'[Date])
Returns the last day of the quarter for a given date.
232
ENDOFYEAR('Date'[Date])
Returns the last day of the year for a given date.
233
FIRSTDATE('Date'[Date])
Returns the first date in a table or table expression.
234
FIRSTNONBLANK('Products'[Name], 'Sales'[Amount])
Returns the first non-blank value in a column, considering another column as the filter context.
235
LASTDATE('Date'[Date])
Returns the last date in a table or table expression.
236
LASTNONBLANK('Products'[Name], 'Sales'[Amount])
Returns the last non-blank value in a column, considering another column as the filter context.
237
NEXTDAY('Date'[Date], 3)
Returns the date that is a specified number of days after a given date.
238
NEXTMONTH('Date'[Date])
Returns the first day of the next month after a given date.
239
NEXTQUARTER('Date'[Date])
Returns the first day of the next quarter after a given date.
240
NEXTYEAR('Date'[Date])
Returns the first day of the next year after a given date.
241
OPENINGBALANCEMONTH('Sales'[Amount], 'Date'[Date])
Calculates the opening balance of a measure for the current month.
242
OPENINGBALANCEQUARTER('Sales'[Amount], 'Date'[Date])
Calculates the opening balance of a measure for the current quarter.
243
OPENINGBALANCEYEAR('Sales'[Amount], 'Date'[Date])
Calculates the opening balance of a measure for the current year.
244
PARALLELPERIOD('Date'[Date], 1, MONTH)
Returns a date from a parallel period, such as the previous month or year, based on a given date.
245
PREVIOUSDAY('Date'[Date], 2)
Returns the date that is a specified number of days before a given date.
246
PREVIOUSMONTH('Date'[Date])
Returns the first day of the previous month before a given date.
247
PREVIOUSQUARTER('Date'[Date])
Returns the first day of the previous quarter before a given date.
248
PREVIOUSYEAR('Date'[Date])
Returns the first day of the previous year before a given date.
249
SAMEPERIODLASTYEAR('Date'[Date])
Returns a table of dates from the same period in the previous year based on a given date.
250
STARTOFMONTH('Date'[Date])
Returns the first day of the month for a given date.
251
STARTOFQUARTER('Date'[Date])
Returns the first day of the quarter for a given date.
252
STARTOFYEAR('Date'[Date])
Returns the first day of the year for a given date.
253
TOTALMTD(SUM('Sales'[Amount]), 'Date'[Date])
Calculates the total month-to-date for a measure, considering a date column.
254
TOTALQTD(SUM('Sales'[Amount]), 'Date'[Date])
Calculates the total quarter-to-date for a measure, considering a date column.
255
TOTALYTD(SUM('Sales'[Amount]), 'Date'[Date])
Calculates the total year-to-date for a measure, considering a date column.
