What the DAX?

Explanation of the Power BI DAX Function TOPNPERLEVEL (and why to avoid it) The DAX function TOPNPERLEVEL is an undocumented and deprecated extension function in Power BI. This means it's not officially supported, its behavior might be inconsistent, and it's no longer recommended for use. It's highly advised to avoid using TOPNPERLEVEL in your Power BI models. Instead, you should use the standard and well-documented TOPN function, often in conjunction with other DAX functions like CALCULATE , SUMMARIZE , ALL , ALLSELECTED , and RANKX , to achieve similar "top N per level" or "top N by group" results. Why was TOPNPERLEVEL problematic/deprecated? The dax.guide website, a reliable resource for DAX functions, lists TOPNPERLEVEL with the following important remarks: Undocumented: It's not part of the official DAX specification or documentation from Microsoft. Not recommended: Its use is discouraged. Deprecated: It...

 The **ROLLUPISSUBTOTAL** function in Power BI DAX is a specialized function used in conjunction with the **ROLLUPGROUP** function to identify whether a specific column in a calculation is being subtotaled or totaled in a summarized table, such as in a matrix or pivot table. It helps in scenarios where you need to apply different logic for subtotals or grand totals compared to regular row-level calculations. ### Syntax ```dax ROLLUPISSUBTOTAL ( <ColumnName> ) ``` - **ColumnName**: The name of the column to check if it is being subtotaled or totaled. ### Return Value - Returns **TRUE** if the specified column is being subtotaled or totaled in the current calculation context. - Returns **FALSE** if the specified column is not being subtotaled (i.e., it’s part of a regular row-level calculation). ### How It Works The **ROLLUPISSUBTOTAL** function is typically used within a **CALCULATE** or **SUMMARIZE** expression alongside **ROLLUPGROUP** to modify behavior for subtotals or gra...

 In Power BI's DAX (Data Analysis Expressions) language, the `RANK` function is a relatively newer addition that provides enhanced capabilities for ranking data, especially within the context of window functions. It's often compared to the older `RANKX` function, but they serve slightly different purposes and have distinct strengths. ### The RANK Function The `RANK` function is part of the DAX window functions introduced to provide more flexible and powerful ways to perform calculations over specified partitions and orderings of data, similar to SQL window functions. **Syntax:** ```dax RANK ( [<ties>][, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] ) ``` **Key Parameters and What They Do:**   * **`<ties>` (Optional):** Defines how to handle rows with identical values for ranking.       * `DENSE`: Assigns consecutive ranks without gaps (e.g., 1, 2, 2, 3).    ...

The **QUARTER** function in Power BI's DAX (Data Analysis Expressions) language is used to extract the quarter number (1 through 4) from a given date. It’s a simple yet powerful function for time-based analysis, enabling users to aggregate or filter data by quarters. ### Syntax ```dax QUARTER(<date>) ``` - **<date>**: A date expression or a column containing date values. The date must be in a valid datetime format. ### Return Value An integer between 1 and 4, representing the quarter of the year: - 1: January–March - 2: April–June - 3: July–September - 4: October–December ### How It Works The **QUARTER** function evaluates the month of the provided date and maps it to the corresponding quarter. For example: - January 15, 2025 → Returns 1 (Q1) - April 30, 2025 → Returns 2 (Q2) - September 1, 2025 → Returns 3 (Q3) - December 25, 2025 → Returns 4 (Q4) If the input date is invalid or blank, the function returns a blank value. ### Use Cases The **QUARTER** function is common...

The **YIELD** function in Power BI DAX (Data Analysis Expressions) is a financial function that calculates the **yield** of a security, such as a bond, that pays periodic interest. It represents the annual return an investor would receive if the security is held until maturity, expressed as a percentage. This function is particularly useful in financial analysis for evaluating fixed-income securities like bonds. ### Syntax ```dax YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) ``` ### Parameters 1. **settlement**: The date when the buyer purchases the security (must be a valid date). 2. **maturity**: The date when the security expires (must be a valid date after the settlement date). 3. **rate**: The annual coupon rate of the security (as a decimal, e.g., 5% = 0.05). 4. **pr**: The price of the security per $100 face value. 5. **redemption**: The redemption value of the security per $100 face value (typically 100 for bonds redeemed at par). 6. **frequency**: The n...

 The **MEDIANX** function in Power BI's DAX (Data Analysis Expressions) language calculates the median value of an expression evaluated over a table. It’s particularly useful for analyzing numerical data in a dataset when you want to find the middle value of a set of numbers, considering the context of a table or a filtered subset of data. ### Syntax ``` MEDIANX(<table>, <expression>) ``` - **<table>**: The table or table expression over which the median is calculated. - **<expression>**: The expression (typically a column or calculation) whose median value you want to compute. ### How It Works - **MEDIANX** evaluates the `<expression>` for each row in the `<table>`. - It then sorts the resulting values and finds the median (the middle value in the sorted list). - If the number of values is odd, the median is the middle value. - If the number of values is even, the median is the average of the two middle values. - It respects the filter context, ...

 The **LASTNONBLANKVALUE** function in Power BI DAX (Data Analysis Expressions) is used to retrieve the last non-blank value in a column, based on a specified sort order defined by another column or expression. It’s particularly useful for scenarios where you need to find the most recent non-empty value in a dataset, such as the last recorded sale, inventory level, or status update. ### Syntax ``` LASTNONBLANKVALUE(<column>, <expression>) ``` - **<column>**: The column that defines the sort order (e.g., a date or time column). - **<expression>**: The expression or column whose last non-blank value you want to retrieve. ### How It Works 1. The function evaluates the `<column>` to determine the order (ascending or descending). 2. It then looks at the `<expression>` and returns the last value that is not blank, based on the sort order of `<column>`. ### Key Points - **Non-blank**: The function ignores blank or null values in the `<expressio...

 The `INFO.FUNCTIONS` DAX function in Power BI is a system function that returns a table containing metadata about all DAX functions available in the current environment. It provides details such as function names, descriptions, return types, and parameter information, making it a valuable tool for developers and analysts who need to explore or document the DAX function library programmatically. ### Syntax ```dax INFO.FUNCTIONS() ``` - **No parameters**: The function does not require any input arguments. - **Output**: Returns a table with the following columns:   - **FunctionName**: The name of the DAX function (e.g., `SUM`, `FILTER`).   - **Description**: A brief description of what the function does.   - **ReturnType**: The data type of the value returned by the function (e.g., Integer, Table, String).   - **ParameterInfo**: Details about the function’s parameters, including their names, types, and whether they are optional or required.   - **Category**: ...

 The **INFO.QUERYGROUPS** DAX function in Power BI is part of the INFO functions family, introduced to provide metadata about a semantic model. Specifically, **INFO.QUERYGROUPS** retrieves information about query groups defined within a Power BI semantic model. Query groups are used to organize and manage queries in a model, often for documentation or optimization purposes, especially in large or complex datasets. ### Syntax ``` INFO.QUERYGROUPS() ``` - **No arguments**: This function does not require any parameters. - **Output**: Returns a table with details about query groups in the semantic model. ### Return Value The function outputs a table with columns that describe the query groups, including: - **ID**: A unique identifier for the query group. - **Name**: The name of the query group, if defined. - **Description**: A description of the query group, if provided. - **Hidden**: Indicates whether the query group is hidden (TRUE/FALSE). - **Other metadata**: Additional properties,...

 The **INFO.PERSPECTIVECOLUMNS** function in Power BI's Data Analysis Expressions (DAX) is part of the suite of INFO functions introduced to provide metadata about a Power BI semantic model. Specifically, **INFO.PERSPECTIVECOLUMNS** retrieves information about the columns included in a perspective within the model. Perspectives in Power BI are subsets of a model’s objects (tables, columns, measures) designed to simplify the user experience by focusing on specific areas of the model for reporting or analysis. ### Syntax ``` INFO.PERSPECTIVECOLUMNS() ``` - **No arguments**: This function does not take any parameters and returns a table containing metadata about columns in perspectives defined in the model. ### Return Value The function returns a table with the following columns: - **PerspectiveID**: A unique identifier (GUID) for the perspective. - **PerspectiveName**: The name of the perspective. - **TableID**: The unique identifier for the table containing the column. - **ColumnID*...

 The DAX (Data Analysis Expressions) **ERROR** function in Power BI is used to explicitly raise an error with a custom error message. It’s primarily used for debugging, testing, or enforcing specific conditions within DAX calculations. When the ERROR function is executed, it stops the calculation and displays the specified error message, helping to identify issues or enforce business logic. ### Syntax ``` ERROR(message) ``` - **message**: A string that defines the custom error message to display when the error is triggered. ### How It Works - The ERROR function halts the execution of a DAX expression and outputs the specified message. - It’s typically used in conditional logic to flag invalid scenarios or to test specific conditions during development. - It does not return a value; it simply stops the calculation and shows the error. ### Use Cases 1. **Debugging and Testing**:    - Use ERROR to test assumptions in your DAX formulas. For example, if a certain condition sho...

 The **PMT** function in Power BI's DAX (Data Analysis Expressions) language calculates the payment amount for a loan or investment based on a constant interest rate, a fixed number of payments, and a present value. It’s commonly used in financial models to determine periodic payments for loans, mortgages, or annuities. ### Syntax ``` PMT(rate, nper, pv, [fv], [type]) ``` ### Parameters - **rate**: The interest rate per period (e.g., annual rate divided by 12 for monthly payments). - **nper**: The total number of payment periods (e.g., 360 for a 30-year loan with monthly payments). - **pv**: The present value, or the initial loan amount (e.g., the loan principal). - **[fv]** (optional): The future value, or the balance after the last payment (default is 0, meaning the loan is fully paid off). - **[type]** (optional): Specifies when payments are due. Use 0 (end of the period, default) or 1 (beginning of the period). ### Return Value The function returns the periodic payment amount a...

 The **REPT** function in Power BI DAX (Data Analysis Expressions) is used to repeat a specified text string a given number of times. It is a text function that helps in creating repetitive patterns or formatting text for display purposes. ### Syntax ``` REPT(text, number_times) ``` - **text**: The text string you want to repeat. - **number_times**: The number of times to repeat the text. Must be a non-negative integer. ### Return Value A text string containing the specified text repeated the specified number of times. ### Key Points - If `number_times` is 0, the function returns an empty string (`""`). - If `number_times` is not an integer, it is truncated to an integer. - If `number_times` is negative or the `text` is empty, the function may return an error or unexpected results. - The REPT function is commonly used for creating visual effects, such as text-based progress bars, or for formatting purposes in reports. ### Use Cases 1. **Creating Text-Based Visuals (e.g., Prog...

 The **CHISQ.DIST** function in Power BI's DAX (Data Analysis Expressions) calculates the probability associated with the chi-squared distribution, which is commonly used in statistical analysis to test the independence of two variables or the goodness of fit of an observed distribution to an expected one.  ### Syntax ``` CHISQ.DIST(x, deg_freedom, cumulative) ``` - **x**: The value at which to evaluate the chi-squared distribution (must be non-negative). - **deg_freedom**: The degrees of freedom (a positive integer). - **cumulative**: A logical value (`TRUE` or `FALSE`):   - `TRUE`: Returns the cumulative distribution function (CDF), giving the probability that a chi-squared random variable is less than or equal to `x`.   - `FALSE`: Returns the probability density function (PDF), giving the probability density at `x`. ### Return Value - A probability value between 0 and 1, representing either the cumulative probability (CDF) or the density (PDF) of the chi-squared d...

 The **NETWORKDAYS** function in Power BI DAX calculates the number of working days between two dates, excluding weekends (Saturday and Sunday) and optionally specified holidays. It’s useful for business scenarios where you need to measure time durations in terms of workdays, such as project timelines, employee attendance, or task durations. ### Syntax ```dax NETWORKDAYS ( <StartDate>, <EndDate> [, <Holidays>] ) ``` - **StartDate**: The start date of the period. - **EndDate**: The end date of the period. - **Holidays** (optional): A table or column containing dates to exclude as holidays. ### Return Value An integer representing the number of whole working days between `StartDate` and `EndDate`, excluding weekends and any specified holidays. ### Key Points - Weekends are assumed to be Saturday and Sunday. - If `StartDate` is after `EndDate`, the result is negative. - The function counts only complete days (e.g., it doesn’t account for partial days). - The `Holiday...

 The **REMOVEFILTERS** function in Power BI DAX (Data Analysis Expressions) is used to remove filters from a specified table or column(s) in a calculation, allowing you to perform calculations on unfiltered data while retaining the context of other filters in the query. It is particularly useful in scenarios where you want to ignore specific filters applied by slicers, visuals, or other parts of the report to compute a value based on the entire dataset or a subset of it. ### Syntax ```dax REMOVEFILTERS([<table> | <column>[, <column>[, ...]]]) ``` - **table**: The name of the table from which filters should be removed. - **column**: One or more columns from which filters should be removed. If no arguments are provided, it removes all filters from the current context. ### How It Works - **REMOVEFILTERS** clears filters on the specified table or columns in the filter context of a DAX calculation. - It is typically used within a **CALCULATE** or **CALCULATETABLE** fun...

 The **PERCENTILE.INC** function in Power BI DAX (Data Analysis Expressions) calculates the value at a specified percentile in a dataset, including the minimum and maximum values in the interpolation. It’s used to find a value below which a given percentage of data points fall, making it useful for statistical analysis, such as understanding data distribution or identifying thresholds. ### Syntax ``` PERCENTILE.INC(<column>, <k>) ``` - **`<column>`**: The column containing the numeric values to evaluate. - **`<k>`**: A number between 0 and 1 (inclusive) representing the percentile (e.g., 0.5 for the 50th percentile or median). - Returns a single value representing the specified percentile. ### How It Works - **PERCENTILE.INC** assumes a continuous distribution and interpolates between values if the exact percentile doesn’t correspond to a specific data point. - It includes both the minimum (0th percentile) and maximum (100th percentile) values in its calcula...

 The **ISTEXT** function in Power BI's Data Analysis Expressions (DAX) checks whether a given value is a text string. It returns **TRUE** if the value is text and **FALSE** if it is not. ### Syntax ```dax ISTEXT(value) ``` - **value**: The expression or column value to be evaluated. ### Return Value - **TRUE**: If the value is a text string. - **FALSE**: If the value is not a text string (e.g., number, date, blank, or other data types). ### How It Works The **ISTEXT** function is used to validate or filter data based on whether a value is text. It’s particularly useful in data cleansing, conditional logic, or calculated columns where you need to identify text values in a dataset. ### Use Cases 1. **Data Validation**:    - Check if a column contains text values before performing operations that require text, such as concatenation or string manipulation.    - Example: Ensure a column meant to store names or IDs is text-based. 2. **Conditional Formatting**:   ...

 The **COUPNCD** function in Power BI DAX (Data Analysis Expressions) is a financial function that calculates the **next coupon date** after a specified settlement date for a security with periodic interest payments, such as a bond. It is part of the suite of DAX functions designed for financial calculations, particularly useful in scenarios involving fixed-income securities. ### Syntax ``` COUPNCD(settlement, maturity, frequency, [basis]) ``` - **settlement**: The date when the security is purchased or settled (must be a valid date). - **maturity**: The date when the security matures (must be a valid date). - **frequency**: The number of coupon payments per year:   - 1 = Annual   - 2 = Semiannual   - 4 = Quarterly - **basis** (optional): The day count basis for calculating interest. If omitted, it defaults to 0. Options include:   - 0: US (NASD) 30/360   - 1: Actual/actual   - 2: Actual/360   - 3: Actual/365   - 4: European 30/360 ### Return...