What the DAX?

The RECEIVED function in DAX returns the total amount received at maturity for a fully invested security, accounting for the initial investment and the discount rate. It’s commonly used in financial analysis to determine the payout of securities like zero-coupon bonds, where no periodic interest is paid, and the return is realized at maturity. Syntax dax RECEIVED(<settlement>, <maturity>, <investment>, <discount>[, <basis>])     settlement: The date the security is purchased (after the issue date). Must be a valid date.     maturity: The date the security expires and the amount is received. Must be a valid date and later than the settlement date.     investment: The amount invested in the security (must be greater than 0).     discount: The security’s discount rate (must be greater than 0), expressed as a percentage or decimal (e.g., 4.5% or 0.045).     basis (optional): The day count basis for calculating t...

 The RANK.EQ function in Power BI's DAX (Data Analysis Expressions) language is used to determine the rank of a value in a column relative to other values in that column. It assigns the same rank to duplicate values (ties) and skips ranks for subsequent values, which is known as "dense ranking." Syntax RANK.EQ(<value>, <column>, [<order>])     value: The value to rank (can be a column reference, measure, or expression).     column: The column containing the values to rank against.     order (optional): Specifies the sort order. Use:         "ASC" (ascending, default) for ranking smallest to largest.         "DESC" (descending) for ranking largest to smallest. Return Value     Returns an integer representing the rank of the specified value in the column.     If the value doesn't exist in the column, it returns BLANK(). Key Characteristics     Ties: Duplicate values r...

 The TANH function in Power BI DAX (Data Analysis Expressions) calculates the hyperbolic tangent of a given number. It returns a value between -1 and 1, representing the ratio of the hyperbolic sine to the hyperbolic cosine of the input value. The hyperbolic tangent function is commonly used in mathematical and statistical computations, particularly in scenarios involving exponential growth, normalization, or certain types of data transformations. Syntax dax TANH(number)     number: The input value (a numeric expression) for which the hyperbolic tangent is calculated. This can be a column, constant, or expression that evaluates to a number. Return Value     A number between -1 and 1, representing the hyperbolic tangent of the input. Mathematical Background The hyperbolic tangent function is defined as: \text{tanh}(x) = \frac{\sinh(x)}{\cosh(x)} = \frac{e^x - e^{-x}}{e^x + e^{-x}} Where:     \sinh(x)      is the hyperbolic sine.   ...

 The DAX function INFO.MEASURES in Power BI is used to retrieve metadata about measures defined in a model. It returns a table containing information about all measures in the current context, such as their names, expressions, and other properties. This function is part of the DAX Information functions family and is primarily used for debugging, documentation, or dynamic analysis of a model's measures. Syntax dax INFO.MEASURES()     No parameters: The function does not take any arguments and returns metadata for all measures in the model. Return Value INFO.MEASURES returns a table with the following columns:     MeasureName: The name of the measure.     Expression: The DAX expression defining the measure.     TableName: The name of the table the measure is associated with.     Hidden: A boolean indicating whether the measure is hidden (TRUE) or visible (FALSE).     FormatString: The format string applied to the measure (e....

 The CONVERT function in Power BI DAX is used to convert an expression from one data type to another. It’s a straightforward way to ensure data is in the desired format for calculations, visualizations, or comparisons. Syntax dax CONVERT(<expression>, <datatype>)     expression: The value or column you want to convert.     datatype: The target data type (e.g., INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, DATETIME). Supported Data Types     INTEGER: Whole numbers.     DOUBLE: Floating-point numbers (decimal values).     STRING: Text values.     BOOLEAN: True/False values.     CURRENCY: Monetary values.     DATETIME: Date and time values. Use Cases     Data Type Consistency:         Ensures calculations or comparisons work correctly by converting mismatched data types. For example, converting a string representation of a number (e.g., "123") to an integer for arith...

 The SELECTCOLUMNS function in Power BI's DAX (Data Analysis Expressions) language is used to create a new table by selecting specific columns from an existing table or expression and optionally renaming them or applying transformations. It’s particularly useful for shaping data to fit specific reporting or analysis needs without modifying the original table. Syntax dax SELECTCOLUMNS(     Table,     "NewColumnName1", Expression1,     "NewColumnName2", Expression2,     ... )     Table: The source table or a DAX expression that returns a table (e.g., a filtered table).     NewColumnName: The name you assign to the new column in the resulting table.     Expression: The DAX expression that defines the values for the new column, which can reference columns from the source table or perform calculations. How It Works     SELECTCOLUMNS creates a new table with only the columns you specify.     Each co...

 The KEYWORDMATCH function in Power BI's DAX (Data Analysis Expressions) language is used to identify whether a specified keyword appears within a text string and returns a score based on the match. It is particularly useful for text analysis, enabling users to detect the presence of specific words or phrases in a column of text data, such as customer feedback, survey responses, or product descriptions. Syntax KEYWORDMATCH(<text>, <keyword>, [<score_if_match>], [<score_if_no_match>])     text: The text string or column containing the text to search.     keyword: The word or phrase to search for within the text.     score_if_match (optional): The value returned if the keyword is found. Defaults to 1.     score_if_no_match (optional): The value returned if the keyword is not found. Defaults to 0. How It Works     KEYWORDMATCH performs a case-insensitive search for the specified keyword within the text.   ...

Explanation of the NEXT Function in DAX Visual Calculations     Purpose: The NEXT function retrieves the value of a specified field or expression from the next row in the data grid of a visual, based on the axis defined (e.g., rows or columns). It’s part of the Visual Calculations feature, which allows calculations to operate directly on the data as it’s structured in a visual (like a table or matrix) rather than the underlying data model.     Syntax:     dax     NEXT(<field or expression>[, <axis>][, <default>])         <field or expression>: The column, measure, or DAX expression whose value you want to retrieve from the next row.         <axis> (optional): Specifies the axis along which to find the next row. Options are ROWS (default) or COLUMNS. If omitted, ROWS is assumed.         <default> (optional): The value to return if there is no next row (e...

 The PRODUCTX function in Power BI's DAX (Data Analysis Expressions) language calculates the product of an expression evaluated for each row in a table. It’s an iterator function, meaning it processes each row of a table individually and multiplies the results of the specified expression to produce a final value. Syntax DAX PRODUCTX(Table, Expression)     Table: The table over which the function iterates.     Expression: The expression to evaluate for each row in the table, with the results multiplied together. How It Works     PRODUCTX iterates over each row in the specified table.     For each row, it evaluates the provided expression.     The results of the expression for all rows are multiplied together to produce a single scalar value.     It’s similar to the PRODUCT function but allows for dynamic calculations based on an expression, whereas PRODUCT directly multiplies values in a column. Key Points     Non...

 The PREVIOUSDAY function in Power BI DAX (Data Analysis Expressions) returns a table containing a single column of date values representing the previous day relative to a specified date. It’s commonly used in time intelligence calculations to compare data from the current day with the previous day. Syntax DAX PREVIOUSDAY(<date_column>)     <date_column>: A column containing date values or an expression that returns a date. Return Value A table with a single column of dates, containing only the date immediately preceding the specified date in the context. How It Works     PREVIOUSDAY identifies the date in the current filter context and returns the date just before it.     It works within a date table or a column of dates and requires a proper date table with continuous dates for accurate results.     The function is often used in measures to calculate metrics like day-over-day changes, such as sales, revenue, or other KPIs. Use C...

 The LOOKUPVALUE function in Power BI DAX (Data Analysis Expressions) is used to retrieve a value from a table based on specified search criteria. It’s similar to Excel’s VLOOKUP but designed for Power BI’s tabular data model, allowing you to fetch data from another table or column based on matching conditions. Syntax dax LOOKUPVALUE(     <Result_ColumnName>,     <Search_ColumnName>,     <Search_Value>,     [<Search_ColumnName2>, <Search_Value2>, ...],     [<Alternate_Result>] )     Result_ColumnName: The column containing the value you want to retrieve.     Search_ColumnName: The column to search for the specified value.     Search_Value: The value to search for in the Search_ColumnName.     Search_ColumnName2, Search_Value2 (optional): Additional search column-value pairs for more complex lookups.     Alternate_Result (optional): A value to retur...

 The INFO.DETAILROWSDEFINITIONS function in Power BI's Data Analysis Expressions (DAX) is part of the INFO functions family, introduced to provide metadata about a semantic model. Specifically, INFO.DETAILROWSDEFINITIONS returns a table containing information about the Detail Rows Expression property defined for measures in a Power BI semantic model. This function is particularly useful for model documentation and understanding the structure of measures within a dataset. Syntax INFO.DETAILROWSDEFINITIONS()     No arguments: This function does not take any parameters.     Return value: A table with the following columns:         MeasureName: The name of the measure.         TableName: The name of the table to which the measure belongs.         TableID: The internal ID of the table.         Expression: The DAX expression defined in the Detail Rows Expression property of the measure. Pu...

 The CEILING function in Power BI DAX rounds a number up to the nearest multiple of a specified value (significance). It’s useful for scenarios where you need to align numbers to specific increments, such as pricing, inventory, or time intervals. Syntax dax CEILING(<number>, <significance>)     number: The value to round up.     significance: The multiple to which the number is rounded up. Must be a positive number, and its sign must match the number’s sign. How It Works     CEILING rounds up (away from zero) to the nearest multiple of the significance.     If the number is already a multiple of significance, no rounding occurs.     If significance is omitted or zero, an error occurs. Example dax CEILING(3.7, 1)  // Returns 4 (rounds 3.7 up to the nearest multiple of 1) CEILING(3.7, 0.5)  // Returns 4 (rounds 3.7 up to the nearest multiple of 0.5) CEILING(-3.7, -1)  // Returns -4 (rounds -3.7 up to the nea...

 The INFO.COLUMNPARTITIONSTORAGES DAX function in Power BI is part of the INFO family of functions, which provide metadata about a semantic model. Specifically, INFO.COLUMNPARTITIONSTORAGES returns detailed information about the storage characteristics of column partitions in a Power BI semantic model. This function is primarily used for advanced model analysis, optimization, and documentation purposes, especially by administrators or developers working with large or complex datasets. Syntax INFO.COLUMNPARTITIONSTORAGES()     No parameters: This function does not take any arguments and returns a table with metadata about column partition storage.     Output: A table containing columns that describe storage-related properties for each column partition in the model. Returned Columns The table returned by INFO.COLUMNPARTITIONSTORAGES typically includes columns such as:     TableID: The unique identifier for the table in the model.     ColumnID: ...

Explanation of the ISEVEN DAX Function The ISEVEN function in DAX is a logical function that determines whether a given number is even or odd. According to the documentation on DAX Guide, it has the following characteristics:     Syntax:     dax     ISEVEN(<Number>)         <Number>: The input value to evaluate, which must be a numeric expression (e.g., an integer, decimal, or a column reference containing numbers).     Return Value:         Scalar Boolean: Returns TRUE if the number is even, or FALSE if the number is odd.         If the input is non-numeric, ISEVEN returns a #VALUE! error.     Behavior:         A number is considered even if it is divisible by 2 with no remainder (e.g., -4, 0, 2, 4).         A number is considered odd if it leaves a remainder of 1 or -1 when divided by 2 (e.g., -3, 1, 3).       ...