What the DAX?

 The **ALLEXCEPT** DAX function in Power BI is used to modify the filter context in a calculation, removing filters from all columns in a table except for those explicitly specified. This allows you to perform calculations while retaining specific filters, which is useful for creating dynamic and flexible reports. ### Syntax ```dax ALLEXCEPT(<table>, <column1>[, <column2>, ...]) ``` - **table**: The table from which filters are removed. - **column1, column2, ...**: The columns where existing filters are preserved. ### How It Works - **ALLEXCEPT** removes filters from all columns in the specified table except for the columns listed in the function. - It modifies the filter context for calculations, allowing you to aggregate data across a broader dataset while keeping certain filters intact. - It’s commonly used in scenarios where you need to calculate totals or percentages relative to a specific subset of data, ignoring other filters. ### Use Case **Scenario**: You...

 The **INTERSECT** function in Power BI's DAX (Data Analysis Expressions) is used to return a table containing rows that are common to two input tables, based on matching values in all columns. It performs a set intersection operation, meaning it identifies rows where the values in all columns are identical in both tables. ### Syntax ```dax INTERSECT(<table1>, <table2>) ``` - **table1**: The first table for the intersection operation. - **table2**: The second table for the intersection operation. ### Return Value A table containing only the rows that exist in both `table1` and `table2`, with duplicate rows removed (distinct rows only). ### Key Characteristics - **Column Matching**: The function compares all columns in both tables. The tables must have the same number of columns and compatible data types for the comparison to work. - **Row-Based Comparison**: INTERSECT checks for exact matches across all columns in a row. - **Distinct Rows**: The result contains unique r...

 The **RRI** function in Power BI DAX (Data Analysis Expressions) calculates the **equivalent interest rate** for the growth of an investment over a specified period, essentially determining the compound annual growth rate (CAGR) or the rate of return that would be required for an investment to grow from a present value to a future value over a given number of periods. ### Syntax ```dax RRI(nper, pv, fv) ``` ### Parameters - **nper**: The number of periods over which the investment grows (e.g., number of years or months). - **pv**: The present value of the investment (starting amount). - **fv**: The future value of the investment (ending amount). ### Return Value The function returns a decimal value representing the equivalent interest rate per period. This rate assumes compound interest. ### How It Works The RRI function uses the formula for compound interest to compute the rate: \[ \text{RRI} = \left( \frac{\text{fv}}{\text{pv}} \right)^{\frac{1}{\text{nper}}} - 1 \] This calcula...

 The ISODD function in Power BI's DAX (Data Analysis Expressions) language is used to determine whether a given number is odd. It returns a Boolean value: TRUE if the number is odd, and FALSE if the number is even. Syntax ISODD(number)     number: The value or expression to evaluate. It must resolve to a numeric value (integer or decimal). If the input is not a number, the function will return an error. Return Value     TRUE: If the number is odd.     FALSE: If the number is even. Key Points     The function only evaluates the integer part of a number. For example, ISODD(3.7) evaluates 3 and returns TRUE.     If the input is a non-numeric value or blank, the function returns an error.     It is often used in conditional logic, filtering, or calculations requiring differentiation between odd and even numbers. Use Case Scenario: You have a dataset with a column of order IDs, and you want to categorize or process records diff...

 The LOG function in Power BI DAX (Data Analysis Expressions) calculates the logarithm of a number to a specified base. It is a mathematical function used for logarithmic computations, which are useful in scenarios involving exponential growth, scaling, or data normalization. Syntax DAX LOG(number, base)     number: The positive numeric value for which to calculate the logarithm.     base: The base of the logarithm. If omitted, the default base is 10 (common logarithm). Return Value The logarithm of the specified number with respect to the given base. If the number or base is invalid (e.g., negative or zero), the function returns an error. Key Notes     The number must be greater than 0.     The base must be greater than 0 and not equal to 1.     If you need the natural logarithm (base e), use the LN function instead. Use Cases     Scaling Data: Logarithms are used to transform skewed data (e.g., exponential growth) into a...

 The DATEDIFF function in Power BI's Data Analysis Expressions (DAX) calculates the difference between two dates based on a specified time interval. It’s commonly used for analyzing time-based data, such as calculating durations, aging, or time gaps in reports. Syntax dax DATEDIFF(<start_date>, <end_date>, <interval>)     start_date: The earlier date (a column or expression returning a date).     end_date: The later date (a column or expression returning a date).     interval: The unit of time for the difference. Options are:         SECOND         MINUTE         HOUR         DAY         WEEK         MONTH         QUARTER         YEAR The function returns an integer representing the number of intervals between the two dates. Key Notes     If start_date is later than e...

 The MINX function in Power BI DAX (Data Analysis Expressions) is used to evaluate an expression for each row of a table and return the smallest value from the results of that expression. It’s particularly useful when you need to find the minimum value of a calculated expression across a table or a filtered set of rows. Syntax dax MINX(<table>, <expression>)     <table>: The table over which the expression is evaluated. This can be a physical table, a table expression (like FILTER or ALL), or a table returned by another DAX function.     <expression>: The expression to evaluate for each row of the table. This must return a scalar (single) value for each row. Return Value The smallest value resulting from evaluating the expression for each row in the specified table. How It Works     Iterates through the table: MINX evaluates the provided expression for each row in the specified table.     Computes the expression: For e...

 The INFO.PARQUETFILESTORAGES DAX function in Power BI is part of the INFO family of functions, which provide metadata about a semantic model. Specifically, INFO.PARQUETFILESTORAGES retrieves information about the Parquet file storage used in a Power BI semantic model, particularly when leveraging Direct Lake mode in Microsoft Fabric. This function is useful for administrators or developers who need to inspect or audit the underlying storage structure of their data model. Syntax dax INFO.PARQUETFILESTORAGES()     No arguments: The function does not take any parameters and returns a table with metadata about Parquet file storage. Return Value The function returns a table containing details about the Parquet files used in the semantic model. The columns in the returned table may include:     TableID: The identifier for the table in the model.     TableName: The name of the table in the model.     ParquetFileStoragePath: The storage path or loca...

 The MDURATION function in Power BI's DAX (Data Analysis Expressions) language calculates the modified duration of a security with an assumed par value of $100. Modified duration measures the price sensitivity of a bond or fixed-income security to changes in interest rates, adjusting the Macaulay duration for the bond's yield to maturity. It’s commonly used in financial analysis to assess the risk of bond investments. Syntax dax MDURATION(Settlement, Maturity, Coupon, Yield, Frequency, [Basis]) Parameters     Settlement: The date the security is purchased or settled (must be a valid date).     Maturity: The date the security matures (must be a valid date).     Coupon: The annual coupon rate of the security (as a decimal, e.g., 0.05 for 5%).     Yield: The annual yield to maturity of the security (as a decimal, e.g., 0.06 for 6%).     Frequency: The number of coupon payments per year:         1 = Annual   ...

The COLUMNSTATISTICS function in Power BI's DAX (Data Analysis Expressions) language is used to provide statistical information about a column in a table, such as count, distinct count, minimum, maximum, average, and standard deviation. It is primarily a diagnostic or metadata function, useful for analyzing the characteristics of a dataset during data exploration or model validation. Syntax dax COLUMNSTATISTICS()     No parameters: The function does not take any arguments and operates on the table it is called within. Return Value The function returns a table with the following columns:     Table: The name of the table containing the column.     Column: The name of the column.     Min: The minimum value in the column.     Max: The maximum value in the column.     Count: The total number of rows in the column (including duplicates and nulls).     Distinct Count: The number of unique values in the column.     ...

 The POWER function in Power BI DAX (Data Analysis Expressions) is used to raise a number to a specified power (exponent). It is a mathematical function that performs exponentiation. Syntax POWER(<base>, <exponent>)     base: The number to be raised to a power (can be any numeric value).     exponent: The power to which the base is raised (can be any numeric value, including decimals or negative numbers). Return Value The result is a numeric value representing the base raised to the exponent. Example dax Result = POWER(2, 3)     Output: 8 (since 2³ = 2 * 2 * 2 = 8) Use Cases     Financial Calculations:         Calculate compound interest, where the formula involves raising a base (1 + interest rate) to the power of time periods.         Example:         dax         CompoundInterest = Principal * (POWER(1 + Rate, Periods) - 1)       ...

 The EDATE function in Power BI's DAX (Data Analysis Expressions) language is used to shift a given date by a specified number of months, either forward or backward. It is particularly useful for calculations involving dates, such as comparing data across time periods, forecasting, or creating dynamic date-based measures. Syntax EDATE(start_date, months)     start_date: The initial date or a reference to a column containing a date.     months: The number of months to add (positive number) or subtract (negative number) from the start_date. Return Value     Returns a date that is the specified number of months before or after the start_date.     The day of the month remains the same unless it doesn't exist in the resulting month (e.g., shifting January 31 by one month results in February 28 or 29, depending on the year). Key Characteristics     Preserves the day of the month: If you shift from January 15 to +1 month, the result is Feb...

 The MONTH function in Power BI DAX (Data Analysis Expressions) extracts the month number from a given date, returning an integer between 1 (January) and 12 (December). It’s commonly used for date-related calculations, filtering, or grouping data by month in reports. Syntax dax MONTH(date)     date: A date expression or a column containing date values. The input must be a valid date in datetime format. Return Value An integer from 1 to 12 representing the month of the specified date. Use Case The MONTH function is useful in scenarios where you need to:     Extract the month from a date for reporting or analysis (e.g., sales by month).     Group or filter data based on months.     Create calculated columns or measures for time-based calculations. Example 1: Creating a Calculated Column Suppose you have a table Sales with a column OrderDate. You want to create a new column to display the month number of each order. dax MonthNumber = MONTH(Sales...

 The PREVIOUS function in Power BI's DAX (Data Analysis Expressions) language is used to reference the value of a column in the previous row within a table, based on the current context. It is primarily used in calculated columns to compare or perform calculations with data from the preceding row in a specified order. Syntax dax PREVIOUS ( <ColumnName> )     ColumnName: The name of the column from which to retrieve the value from the previous row. Key Points     The PREVIOUS function is only available in calculated columns, not in measures.     It requires a well-defined sort order in the table to determine what "previous" means. You typically use a column like a date, index, or any column that defines the sequence of rows.     If there is no previous row (e.g., for the first row in the table), the function returns BLANK().     It respects the current filter context, meaning it only considers rows that are visible in the curre...

 The ODDLYIELD function in Power BI DAX (Data Analysis Expressions) calculates the yield of a security with an odd (irregular) last period. This is typically used for bonds or other fixed-income securities that have a non-standard final coupon period, meaning the last interest payment period is shorter or longer than the regular coupon periods. Syntax dax ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis]) Parameters     settlement: The date when the security is purchased.     maturity: The date when the security matures.     last_interest: The date of the last interest payment before the settlement.     rate: The security's annual coupon rate (interest rate).     pr: The security's price per $100 face value.     redemption: The security's redemption value per $100 face value (usually 100).     frequency: The number of coupon payments per year (1 = annual, 2 = semi-annual, 4 =...

 The MID function in Power BI DAX (Data Analysis Expressions) is used to extract a substring from a text string, starting at a specified position and for a specified number of characters. It is similar to the MID function in Excel and is useful for text manipulation in data models. Syntax MID(<text>, <start_position>, <length>) Parameters     <text>: The text string from which you want to extract a substring. This can be a column reference or a text value.     <start_position>: The position in the text string where the extraction begins (1-based indexing, meaning the first character is at position 1).     <length>: The number of characters to extract starting from the <start_position>. Return Value A string containing the specified number of characters from the given text, starting at the specified position. Use Case The MID function is commonly used when you need to extract a specific portion of a text string,...

 The ISONORAFTER function in Power BI's DAX (Data Analysis Expressions) language is used in time intelligence calculations to filter or evaluate data based on whether a date is on or after a specified date. It is primarily used with the CALCULATE function to modify filter contexts in DAX expressions. Syntax ISONORAFTER(<column>, <date>, <sort_order>[, <column>, <date>, <sort_order>]...)     <column>: The date column to evaluate (e.g., a column in a Date table).     <date>: The date to compare against (can be a literal date, a DAX expression, or a reference to another column).     <sort_order>: Specifies the sort direction, either ASC (ascending) or DESC (descending).     Multiple column-date-sort triplets can be provided to evaluate multiple conditions. How It Works     ISONORAFTER creates a filter that includes dates that are on or after the specified date(s) based on the sort o...

 The RATE function in Power BI's DAX (Data Analysis Expressions) language is used to calculate the interest rate per period for an investment or loan, given a series of equal periodic payments (annuity). It is commonly used in financial analysis to determine the implied interest rate of a loan, investment, or savings plan based on cash flows. Syntax dax RATE(nper, pmt, pv, [fv], [type], [guess]) Parameters     nper: The total number of payment periods (e.g., months, years).     pmt: The payment amount made each period (must remain constant).     pv: The present value, or the initial amount of the loan or investment.     fv (optional): The future value, or the cash balance you want to attain after the last payment. Defaults to 0 if omitted.     type (optional): Indicates when payments are due. Use 0 (end of period, default) or 1 (beginning of period).     guess (optional): Your estimate of the interest rate. Defaults to 10%...

 The NEXTYEAR function in Power BI DAX (Data Analysis Expressions) is used to return all dates in the next year relative to a specified date column. It is part of the DAX time intelligence functions, which are designed to perform calculations over date and time periods, often requiring a proper date table in your data model. Syntax NEXTYEAR(<dates>[, <year_end_date>])     <dates>: A column containing date values, typically from a date table.     <year_end_date>: (Optional) A literal string specifying the end of the year in "mm/dd" format (e.g., "12/31"). If omitted, it assumes the year ends on December 31. Return Value A table containing a single column of all dates for the next year based on the input dates. How It Works     NEXTYEAR takes a date column and returns all dates for the subsequent year.     It considers the calendar year unless a custom year-end date is specified.     The function is often used ...

 The EXPON.DIST function in Power BI DAX (Data Analysis Expressions) is used to calculate the probability density or cumulative distribution function for an exponential distribution. The exponential distribution is commonly used to model the time between events in a Poisson process, such as the time until a machine fails, the time between customer arrivals, or other scenarios involving random intervals. Syntax EXPON.DIST(x, lambda, cumulative) Parameters:     x: The value at which to evaluate the function (must be non-negative, i.e., x ≥ 0).     lambda: The parameter of the exponential distribution (the rate parameter, λ > 0). It represents the average number of events per unit of time.     cumulative: A logical value (TRUE or FALSE).         TRUE: Returns the cumulative distribution function (CDF), which gives the probability that a random variable is less than or equal to x.         FALSE: Returns the probab...

LAST Function in Visual Calculations Syntax: LAST(<value>, <axis>) Description: The LAST function retrieves the value from the last row of a specified axis in the visual’s data grid. Visual Calculations are a feature in Power BI that allow calculations directly within a visual (e.g., table or matrix), using a simplified syntax that references the visual’s rows, columns, or groups.     <value>: The field or expression whose value you want to retrieve from the last row.     <axis>: The axis (e.g., ROWS, COLUMNS, or GROUPS) defining the scope where the last row is determined. If omitted, it defaults to ROWS. The "last row" is determined by the sorting and filtering applied to the visual’s axis in the current context. Key Characteristics:       Operates on the visual’s data grid, not the underlying data model.     Context-aware, respecting the visual’s filters, slicers, and sort order.     Returns a single v...