What the DAX?

 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...

 The ROUNDUP function in Power BI's DAX (Data Analysis Expressions) language rounds a number up to a specified number of decimal places, always rounding away from zero. Unlike the ROUND function, which rounds to the nearest value based on standard rounding rules, ROUNDUP ensures the number is rounded up, regardless of the value of the next digit. Syntax dax ROUNDUP(Number, Num_digits)     Number: The numeric value you want to round up.     Num_digits: The number of decimal places to round to.          If positive, it rounds to the specified number of decimal places.         If zero, it rounds to the nearest integer.         If negative, it rounds to the left of the decimal point (e.g., to the nearest 10, 100, etc.). Return Value A decimal number rounded up to the specified number of digits. Examples     Basic Rounding:     dax     ROUNDUP(3.14159, 2)     Retu...

 The RUNNINGSUM function in Power BI's DAX (Data Analysis Expressions) language is used to calculate a running total (or cumulative sum) of a specified expression across a table, typically ordered by a column such as a date or index. It is particularly useful for analyzing trends over time or sequential data, such as cumulative sales, inventory levels, or account balances. Syntax dax RUNNINGSUM(<table>, <expression>, <order_by_column>[, <direction>])     table: The table over which the running sum is calculated.     expression: The numeric expression to sum, such as a column (e.g., Sales[Amount]) or a calculation.     order_by_column: The column used to determine the order of rows for the running sum (e.g., Sales[Date]).     direction (optional): Specifies the sort direction, either "ASC" (ascending, default) or "DESC" (descending). How It Works     RUNNINGSUM evaluates the <expression> for each row in ...

 The DISTINCTCOUNTNOBLANK function in Power BI DAX (Data Analysis Expressions) counts the number of unique, non-blank values in a column. It ignores blank or empty values and only considers distinct (unique) non-blank values. Syntax DISTINCTCOUNTNOBLANK(<column>)     <column>: The column containing the values you want to count. Return Value     Returns an integer representing the count of unique, non-blank values in the specified column. Key Characteristics     Ignores blanks: Unlike DISTINCTCOUNT, which counts all unique values including blanks, DISTINCTCOUNTNOBLANK excludes blank or null values.     Case-sensitive: It treats values like "Apple" and "apple" as distinct.     Works on any data type: Can be used with text, numbers, dates, etc. Use Case DISTINCTCOUNTNOBLANK is useful when you need to count unique non-empty entries in a dataset, such as:     Customer Analysis: Counting unique customers who made pur...

 The LN function in Power BI's DAX (Data Analysis Expressions) language calculates the natural logarithm of a given number, using the base e (approximately 2.71828). It is a mathematical function used primarily for advanced calculations involving exponential relationships, growth rates, or logarithmic transformations. Syntax LN(number)     number: A positive real number for which you want to calculate the natural logarithm. The number must be greater than 0, as the natural logarithm is undefined for zero or negative values. Return Value The natural logarithm of the input number. Key Points     If the input number is less than or equal to 0, the LN function returns an error.     The result is a decimal value representing the power to which e must be raised to obtain the input number.     The LN function is often used in financial, statistical, or scientific calculations where logarithmic scales or exponential growth models are relevant. Use Ca...

 The FIXED function in Power BI's DAX (Data Analysis Expressions) language is used to round a number to a specified number of decimal places and return the result as a text string. It’s particularly useful when you need to control the display format of numerical values in reports, ensuring consistent decimal precision in visuals or calculations. Syntax DAX FIXED(Number, Decimals, [No_Commas])     Number: The number you want to round and convert to text.     Decimals: The number of decimal places to round to. Can be positive (round to the right of the decimal) or negative (round to the left).     No_Commas (optional): A logical value (TRUE/FALSE). If TRUE, the result omits thousand separators (commas). If FALSE or omitted, commas are included. Return Value A text string representing the rounded number, formatted with the specified decimal places. Key Points     The FIXED function always returns a text data type, not a number, which makes it id...

 The PATHLENGTH function in Power BI's DAX (Data Analysis Expressions) language is used to return the number of levels (or depth) in a hierarchy defined by a PATH function. It counts the number of items in a path string, which represents a hierarchical relationship, such as an organizational structure or a parent-child hierarchy. Syntax dax PATHLENGTH ( <Path> )     Path: A column or expression that contains a path string generated by the PATH function. The path string is typically a delimited list of IDs representing the hierarchy (e.g., "1|2|3"). Return Value     An integer representing the number of items (or levels) in the path. How It Works The PATHLENGTH function counts the number of elements in a path string created by the PATH function. Each element in the path represents a node in the hierarchy, and the function essentially tells you how deep a particular node is within that hierarchy. For example:     If the path is "1|2|3", PATHLENGTH ...

 The UTCTODAY function in Power BI's DAX (Data Analysis Expressions) language returns the current date in UTC time as a datetime value. It is similar to the DAX TODAY function but ensures the date is based on Coordinated Universal Time (UTC) rather than the local time of the system or user. Syntax dax UTCTODAY()     No parameters: The function takes no arguments.     Return value: A datetime value representing the current date in UTC at midnight (00:00:00). Key Characteristics     Dynamic: The function updates automatically based on the current date when the report is refreshed.     UTC-based: It always returns the date in UTC, which is critical for consistency in global applications where users or data sources span multiple time zones.     Date-only: It returns only the date portion (midnight of the current UTC day), not the time. Use Cases     Standardized Date Reporting Across Time Zones:         Whe...

 The CONCATENATE function in Power BI's DAX (Data Analysis Expressions) is used to combine two text strings into a single text string. It’s a straightforward function for merging text values, often used in creating custom columns or measures for reporting purposes. Syntax dax CONCATENATE(<text1>, <text2>)     text1: The first text string or column containing text values.     text2: The second text string or column containing text values. The function returns a single text string that is the result of combining text1 and text2. Key Points     Inputs: Both arguments must be text strings or expressions that evaluate to text. If non-text values (e.g., numbers) are provided, they are automatically converted to text.     Limitations:          CONCATENATE only combines two text strings. For combining more than two, you can nest CONCATENATE functions or use the & operator or the CONCATENATEX function for more ...