What the DAX?

 The ACCRINT function in Power BI DAX calculates the accrued interest for a security that pays periodic interest, such as a bond. It’s primarily used in financial analysis to determine the interest earned on an investment between payment periods. Syntax ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method]) Parameters     issue: The date the security is issued (e.g., bond issuance date).     first_interest: The date of the first interest payment.     settlement: The date the security is purchased or settled.     rate: The annual coupon rate of the security (as a decimal, e.g., 5% = 0.05).     par: The par value of the security (e.g., $1,000 for a typical bond).     frequency: The number of interest payments per year (1 = annual, 2 = semi-annual, 4 = quarterly).     [basis] (optional): The day count basis for interest calculation (default is 0):         0: US (...

 The INFO.ALTERNATEOFDEFINITIONS function in Power BI's Data Analysis Expressions (DAX) is part of the INFO functions family, designed to retrieve metadata about a Power BI semantic model. Specifically, INFO.ALTERNATEOFDEFINITIONS returns a table containing information about alternate key relationships defined in the model, such as those used to specify unique identifiers or alternate keys for tables. This function is particularly useful for model administrators or developers who need to document, audit, or troubleshoot the structure of a Power BI semantic model. Syntax dax INFO.ALTERNATEOFDEFINITIONS()     No arguments: This function takes no input parameters.     Return value: A table with columns describing alternate key relationships in the model, including details like the table name, column name, and alternate key definitions. Columns in the Output Table The table returned by INFO.ALTERNATEOFDEFINITIONS typically includes columns such as:     Tab...

 The INFO.RELATIONSHIPSTORAGES DAX function in Power BI is part of the INFO family of functions, introduced to provide metadata about a Power BI semantic model. Specifically, INFO.RELATIONSHIPSTORAGES returns information about the storage properties of relationships in the model, such as how relationships are managed in terms of data storage and query performance. This function is particularly useful for advanced users, administrators, or developers who need to document, analyze, or optimize the structure of their Power BI data models. Syntax DAX INFO.RELATIONSHIPSTORAGES()     No parameters: The function takes no arguments and returns a table with details about the storage properties of relationships in the semantic model.     Return value: A table containing columns that describe the storage-related metadata for each relationship in the model. Columns Returned The table returned by INFO.RELATIONSHIPSTORAGES typically includes columns such as:     Rel...

 The NORM.INV function in Power BI DAX (Data Analysis Expressions) calculates the inverse of the cumulative distribution function (CDF) for a normal distribution. It returns the value ( x ) such that the probability of a normally distributed random variable being less than or equal to ( x ) is equal to a specified probability. Syntax dax NORM.INV(probability, mean, standard_dev)     probability: A value between 0 and 1 representing the probability associated with the normal distribution.     mean: The arithmetic mean (average) of the distribution.     standard_dev: The standard deviation of the distribution (must be positive). Return Value The function returns a value ( x ) (a number) from the normal distribution with the specified mean and standard deviation, where the cumulative probability  P(X \leq x)  equals the given probability. How It Works The NORM.INV function is based on the inverse of the cumulative normal distribution. It answers...

 The INFO.SEGMENTMAPSTORAGES DAX function in Power BI is part of the INFO family of functions, which provide metadata about the semantic model. Specifically, INFO.SEGMENTMAPSTORAGES returns a table containing detailed information about the segment map storages in the current Power BI model. It corresponds to the TMSCHEMA_SEGMENT_MAP_STORAGES Dynamic Management View (DMV) in Analysis Services, adapted as a DAX function for use in Power BI. This function is primarily used for advanced model introspection and optimization, requiring semantic model admin permissions to execute. Syntax DAX INFO.SEGMENTMAPSTORAGES ( [<RestrictionName> [, [<RestrictionValue>] [, <RestrictionName> [, [<RestrictionValue>] [, … ] ] ] ] )     RestrictionName: (Optional) The name of a column in the segment map storages schema to filter the results.     RestrictionValue: (Optional) The value to filter the specified column.     Multiple restriction pairs can ...

 The ISLOGICAL function in Power BI DAX (Data Analysis Expressions) checks whether a value is a logical value (TRUE or FALSE). It returns TRUE if the value is a logical value and FALSE otherwise. Syntax ISLOGICAL(value)     value: The expression or value to test. Return Value     TRUE: If the value is a logical value (TRUE or FALSE).     FALSE: If the value is not a logical value (e.g., text, number, date, etc.). Use Case ISLOGICAL is useful in scenarios where you need to validate or filter data based on whether a value is a boolean (logical) type. It’s often used in conditional logic, data cleansing, or debugging calculated columns/measures. Example Scenario Suppose you have a table with a column Flag that contains mixed data types (logical values like TRUE/FALSE, text, numbers, etc.), and you want to identify rows where the Flag column contains logical values. Sample Data: ID                Flag 1   ...

 The YEARFRAC function in Power BI DAX calculates the fraction of a year between two dates, based on a specified day count convention. It’s useful for determining the time duration in years, often for financial calculations, age calculations, or prorated metrics. Syntax DAX YEARFRAC(start_date, end_date, [basis])     start_date: The start date (a valid date expression).     end_date: The end date (a valid date expression).     basis (optional): An integer specifying the day count convention:         0 or omitted: US (NASD) 30/360 (default).         1: Actual/actual (uses the actual number of days in each month and year).         2: Actual/360 (assumes a 360-day year).         3: Actual/365 (assumes a 365-day year).         4: European 30/360. Return Value A decimal number representing the fraction of a year between the two dates. Use Cases     ...

 The COUNTBLANK function in Power BI DAX (Data Analysis Expressions) counts the number of blank cells in a specified column. It is useful for analyzing data quality, identifying missing values, or performing calculations based on the presence of empty cells. Syntax COUNTBLANK(<column>)     <column>: The column you want to evaluate for blank values. Return Value     Returns an integer representing the number of blank cells in the specified column.     A cell is considered blank if it contains BLANK(), NULL, or an empty string (""). Key Points     COUNTBLANK only counts truly blank cells. It does not count cells with zeros, spaces, or non-empty strings.     It is typically used in measures or calculated columns to assess data completeness.     It works with any data type (text, number, date, etc.), as long as the value is blank. Use Cases     Data Quality Checks:         Identify mis...

 The NONVISUAL DAX function in Power BI is used to mark a calculation or measure as non-visual, meaning it will not be displayed in visuals like tables, charts, or matrices, but can still be used in calculations or as part of other measures. It is primarily used in scenarios where you want to compute intermediate values or aggregations that support other calculations without cluttering the visual output. Syntax dax NONVISUAL(<expression>)     expression: The DAX expression or measure you want to mark as non-visual. How It Works     The NONVISUAL function wraps an expression or measure, ensuring it is excluded from being directly displayed in visuals.     The result of the expression is still calculated and can be referenced in other measures or calculations.     It is particularly useful in complex models where intermediate calculations are needed but should not appear in reports. Use Cases     Intermediate Calculations: ...

MROUND DAX Function Syntax: dax MROUND(<number>, <multiple>)     number: The value you want to round.     multiple: The multiple to which you want to round the number. Description: The MROUND function rounds the number to the nearest value that is a multiple of the specified multiple. If the number is exactly halfway between two multiples, it rounds away from zero (e.g., 2.5 to 3 for a multiple of 1). Return Value: A number rounded to the nearest multiple of the specified value. Examples     Basic Rounding:     dax     MROUND(23, 5)         Returns 25, as 25 is the nearest multiple of 5 to 23.     Rounding to Nearest 10:     dax     MROUND(17, 10)         Returns 20, as 20 is the nearest multiple of 10 to 17.     Negative Numbers:     dax     MROUND(-23, 5)         Returns -25, as -25 is the nearest multiple...

 The INFO.LINGUISTICMETADATA DAX function in Power BI is part of the INFO functions family, introduced to provide metadata about the semantic model. Specifically, INFO.LINGUISTICMETADATA retrieves information about the linguistic metadata associated with objects in the model, such as tables, columns, or measures. This function is particularly useful for model documentation, auditing, and understanding the cultural or linguistic settings applied to the data model. Syntax INFO.LINGUISTICMETADATA()     No parameters: The function does not take any arguments and returns a table with details about the linguistic metadata in the semantic model. Return Value The function returns a table with the following columns:     ObjectType: The type of object (e.g., Table, Column, Measure).     ObjectID: A unique identifier for the object.     Name: The name of the object.     Culture: The culture or language setting associated with the object (e.g.,...

 The LASTNONBLANK function in Power BI's DAX (Data Analysis Expressions) language is used to retrieve the last non-blank value in a column, based on the sort order of another column (typically a date or time column). It is particularly useful for scenarios where you need to identify the most recent non-empty value in a dataset, such as in time-based or sequential data analysis. Syntax DAX LASTNONBLANK(<column>, <expression>)     <column>: The column to search for the last non-blank value.     <expression>: An expression that defines the values to evaluate for non-blankness. This is typically a measure or a column reference. How It Works     The function scans the specified <column> in the order defined by the current filter context (often a date or another sequential column).     It returns the last value in <column> where the corresponding <expression> is non-blank (i.e., not empty, null, or blank). ...

 The ALLCROSSFILTERED function in Power BI DAX removes all filters from a table, including those applied by cross-filtering from other tables in the model, while preserving relationships. Unlike ALL, which removes filters only from the specified table or columns, ALLCROSSFILTERED clears both direct filters and those inherited through relationships, making it particularly useful in complex data models with multiple related tables. Syntax dax ALLCROSSFILTERED(<table>)     table: The table from which to remove all filters, including cross-filters. Return Value Returns the table with all filters removed, including those from related tables via cross-filtering. Key Characteristics     Removes all filters (slicers, page filters, visual filters, and cross-filters from relationships).     Preserves the data model’s relationships, so calculations respect the structure of the model.     Typically used in measures or calculated columns where you n...

 The VALUE function in Power BI's DAX (Data Analysis Expressions) converts a text string that represents a number into a numeric value. It's particularly useful when dealing with data that is stored as text but needs to be treated as a number for calculations or analysis. Syntax VALUE(text)     text: A text string that represents a number (e.g., "123", "45.67"). The text must be in a format that can be converted to a number, such as integers or decimals. Return Value     A numeric value (integer or decimal) if the conversion is successful.     If the text cannot be converted to a number (e.g., "abc"), it results in an error. Use Cases     Converting Text to Numbers for Calculations:         When importing data from sources like Excel, CSV, or databases, numeric values may sometimes be stored as text (e.g., "100" instead of 100). The VALUE function converts these text strings to numbers so they can be used in mathematical o...

INFO.KPIS Function Overview     Purpose: Returns a list of all KPIs in the current Power BI model, with columns matching the schema rowset for KPI objects.     Category: System/Information function.     Availability: Supported in Power BI Desktop, Analysis Services, and Power Pivot environments. It was documented as of August 13, 2024, but may be considered undocumented or unsupported in some contexts due to limited Microsoft documentation.     Limitations: Cannot be used in calculated tables or calculated columns, as is typical for INFO functions. It performs a context transition if called in a row context. Syntax DAX INFO.KPIS ( [<RestrictionName> [, [<RestrictionValue>] [, <RestrictionName> [, [<RestrictionValue>] [, … ] ] ] ] )     Parameters:         RestrictionName: (Optional) The name of a column in the TMSCHEMA_KPIS schema to filter the results.         RestrictionVa...

 The INFO.STORAGEFOLDERS DAX function in Power BI is part of the INFO family of functions introduced to provide metadata about the semantic model. Specifically, INFO.STORAGEFOLDERS returns information about the storage folders used in the model, which can be useful for understanding how data is organized and managed behind the scenes. Syntax INFO.STORAGEFOLDERS()     No arguments: This function does not require any input parameters.     Return value: It returns a table with columns that describe the storage folders in the model, such as:         ID: A unique identifier for the storage folder.         Name: The name of the storage folder.         Path: The path or location of the storage folder within the model’s storage structure.         Other columns may include additional metadata, depending on the model and environment. Purpose The INFO.STORAGEFOLDERS function is primarily used t...

 The COUPPCD function in Power BI DAX (Data Analysis Expressions) is used to calculate the previous coupon payment date before the settlement date for a security (typically a bond) with periodic interest payments. It’s part of the financial functions in DAX, designed to help with bond-related calculations. Syntax COUPPCD(settlement, maturity, frequency, [basis]) Parameters     settlement: The date when the bond is purchased or settled (must be a valid date).     maturity: The date when the bond expires (must be a valid date).     frequency: The number of coupon payments per year:         1 = Annual         2 = Semi-annual         4 = Quarterly     basis (optional): The day-count convention for calculating interest. Defaults to 0 if omitted:         0 = US (NASD) 30/360         1 = Actual/actual         2 = Actual/360   ...

 The TOTALMTD function in Power BI DAX (Data Analysis Expressions) calculates the total of an expression over a period from the start of the month to the specified date in a given dataset. It is used for month-to-date (MTD) calculations, which are common in financial, sales, and performance reporting to track metrics within the current month. Syntax TOTALMTD(<expression>, <dates>[, <filter>])     expression: The calculation you want to aggregate (e.g., SUM(Sales[Amount])).     dates: A column containing date values, typically from a date table.     filter (optional): Additional filters to apply to the calculation. How It Works     TOTALMTD evaluates the expression for all dates from the first day of the month up to the date specified in the context (e.g., the current date in a report).     It respects the calendar month boundaries and resets at the start of each month.     It requires a date table with co...

 The EVALUATEANDLOG function in Power BI's Data Analysis Expressions (DAX) is a specialized debugging tool designed to help developers understand and trace the intermediate results of DAX expressions during query evaluation. It returns the value of its input expression (scalar or table) while logging detailed information about the evaluation process in a DAX Evaluation Log profiler event, which can be captured and analyzed using external tools. This function is particularly useful for diagnosing performance issues, understanding complex calculations, and verifying the behavior of DAX expressions. Syntax DAX EVALUATEANDLOG(     Expression,     [Label],     [MaxRows] )     Expression: The DAX expression to evaluate, which can return either a scalar value or a table.     Label (optional): A string to identify the log entry, making it easier to locate specific logs when debugging.     MaxRows (optional): The maximum number of ...