What the DAX?

Power BI DAX Function: PRICEDISC Calculates the price per $100 face value of a discounted security (e.g., Treasury bill or zero-coupon bond) that is sold at a discount and does not pay periodic interest. Syntax PRICEDISC( Settlement, -- Date when the security is purchased Maturity, -- Date when the security matures Discount, -- Discount rate of the security (as decimal, e.g., 5% = 0.05) Redemption, -- Redemption value per $100 face value (usually 100) [Basis] -- Optional: Day count basis (0 = US 30/360, 1 = Actual/Actual, etc.) ) Parameters Parameter Description Typical Value Settlement Date the buyer purchases the security Date Maturity Date the security matures Date Discount Annual discount rate (expressed as decimal) e.g., 0.052 for 5.2% Redemption Value paid at maturity per $100 face value Usually 100 Basis (optional) Day count convention ...

INFO.PERSPECTIVES() – DAX Function Category: Information function (Tabular model metadata) Syntax INFO.PERSPECTIVES() Returns a table containing all perspectives defined in the current Tabular model. Returned Table Columns Column Name Description PerspectiveName Name of the perspective (string) PerspectiveID Internal ID of the perspective Description Description entered in the model (can be blank) Common Use Cases Dynamic perspective selector in reports Let end users choose which perspective they want to see (Sales, Finance, HR, etc.) Security / role-based visibility Show only relevant perspectives to certain user groups Documentation & auditing Generate a list of all available perspectives in the model Conditional formatting or slicer population Build a slicer t...

Power BI DAX: NORM.DIST Function NORM.DIST returns the normal distribution (Gaussian bell curve) for a specified mean and standard deviation. Syntax NORM.DIST(x, mean, standard_dev, cumulative) Parameters Parameter Description Required x The value at which to evaluate the distribution Yes mean The arithmetic mean of the distribution (center of the bell curve) Yes standard_dev The standard deviation of the distribution (> 0) Yes cumulative Logical value: TRUE = cumulative distribution function (CDF), FALSE = probability density function (PDF) Yes Return Value If cumulative = FALSE → returns the height of the normal curve at point x (probability ...

Power BI DAX – LOWER Function Syntax LOWER( ) Parameter: – A text string or a column containing text. What It Does The LOWER function converts all letters in a text string to lowercase . Non-letter characters (numbers, spaces, symbols) remain unchanged. Return Value A text string with all alphabetic characters converted to lowercase.</ Common Use Cases Use Case Why Use LOWER? Example Case-insensitive filtering or lookup Users may type "USA", "usa", or "UsA". Converting to lowercase ensures consistent matching. SearchValue = LOWER(SELECTEDVALUE(SearchTable[Input])) Cleaning inconsistent data Standardize product names, categories, or customer names sto...

Power BI DAX – BITRSHIFT Function Syntax: BITRSHIFT(Number, ShiftAmount) What It Does Performs a logical right shift on an integer value. Takes the binary representation of Number Shifts all bits to the right by ShiftAmount positions Fills the leftmost bits with zeros (this is what makes it a logical shift) Returns the resulting integer Note: Unlike arithmetic right shift (which preserves the sign bit), BITRSHIFT always fills with zeros, so it works the same for positive and negative numbers in two's complement representation (DAX treats negative numbers as large positive equivalents in bit operations). Parameters Parameter Description Number Any integer expression (can be positive or negative) ShiftAmount Number of positions to shift right. Positive = right shift, Nega...

Power BI DAX Function: ODDLPRICE ODDLPRICE calculates the price per $100 face value of a security with an odd (irregular) last coupon period . Syntax ODDLPRICE( <Settlement>, <Maturity>, <Last Interest>, <Rate>, <Yield>, <Redemption>, <Frequency> [, <Basis>] ) Parameters Parameter Description Settlement Date when the security is purchased Maturity Date when the security matures Last Interest Date of the last coupon payment before settlement Rate Annual coupon rate of the security (e.g., 5% = 0.05) Yield Annual yield of the security Redemption Redemption value per $100 f...

DDB - Double Declining Balance Function The DDB function calculates depreciation of an asset using the Double Declining Balance method — an accelerated depreciation method commonly used in accounting and financial modeling. Syntax DDB(cost, salvage, life, period, [factor]) Parameters Parameter Description Example cost Initial cost of the asset 10000 salvage Value of the asset at the end of its useful life (residual value) 1000 life Number of periods (usually years) over which the asset is depreciated 5 period The period for which you want to calculate depreciation (must use same unit as life) 1, 2, 3... [factor] Optional. The rate at which the balance declines. Default is 2 (double declining) 2 (default) Example DDB(10000, 1000, 5, 1) = 4,000 → Ye...

DAX UNION Function in Power BI Syntax UNION(<table_expression1>, <table_expression2> [, <table_expression3>, …]) What It Does The UNION function combines multiple tables into a single table by stacking rows vertically (row-wise append). It is the DAX equivalent of SQL's UNION ALL — it includes duplicate rows unless you wrap it with DISTINCT or SUMMARIZECOLUMNS . Key Requirements All input tables must have the same number of columns Corresponding columns must have compatible data types Column names in the result come from the first table Common Use Cases Use Case Description Example Combine similar data from multiple tables Merge sales data from different regions or years stored in separate tables Union East_Sales and West_Sales tables Create a dynamic calendar or date table Combine custom holidays, fiscal periods, etc. Add ...

Power BI DAX - MATCHBY() Function Introduced in: Power BI Desktop (March 2024 update) and later What is MATCHBY? MATCHBY is a new DAX function that explicitly defines how rows are matched when using row context in functions like EARLIER , RANKX , COUNTROWS with filter, etc. It solves the long-standing ambiguity in DAX where the engine previously decided matching logic automatically (often causing bugs when tables have duplicate values). Syntax MATCHBY ( , [= | <= | >= | > | <] , ... ) Or more commonly used inside calculation functions: RANKX ( MATCHBY ( Customers, Customers[CustomerKey] ), [Total Sales], , DESC ) Key Use Cases Use Case Description Example Ranking with duplicate keys Ensure correct ranking when multiple tables have same column names or duplicates Rank sales by customer even if CustomerName is not unique Complex EARLIER scenarios ...

div class="section"> Power BI DAX Function: CLOSINGBALANCEQUARTER Returns the value of an expression evaluated at the last date of the current quarter in the current context. Syntax CLOSINGBALANCEQUARTER(<Expression>, <Dates>[, <Filter>]) Parameter Description <Expression> The measure or calculation you want to evaluate (e.g., SUM(Sales[Amount])) <Dates> A column containing dates (usually from your Date table, e.g., 'Date'[Date]) <Filter> (Optional) A filter to apply to the dates How It Works No matter which day, month, or year is currently visible in your visual or filter context, CLOSINGBALANCEQUARTER always returns the value as of June 30, September 30, December 31, or March 31 of the quarter that contains the current context. It looks forward to the end of the current quarter. It is time-intelligence function — perfect f...

INFO.STORAGETABLECOLUMNSEGMENTS Category: Diagnostic / Internal function (VertiPaq engine) Availability: Only works in DirectQuery over Power BI datasets or when using XMLA endpoint with tools like DAX Studio or Tabular Editor. What it does Returns detailed information about how a column is physically segmented and stored inside the VertiPaq (in-memory columnar) engine of Power BI / Analysis Services Tabular models. Syntax INFO.STORAGETABLECOLUMNSEGMENTS( TableName, ColumnName ) Parameters Parameter Description TableName Name of the table (as string, e.g. "Sales") ColumnName Name of the column (as string, e.g. "Order Date") Returned Columns (simplified) Column Meaning SegmentNumber Which data segment (1 segment ≈ 1 million rows) Rows Number of rows in that segment...

USEROBJECTID() – Power BI DAX Function Definition USEROBJECTID() is a DAX function available in Power BI and Analysis Services Tabular models that returns the Azure Active Directory (Azure AD) Object ID of the currently logged-in user. Function USEROBJECTID() Return Type Text (GUID string) Category Security / Row-Level Security (RLS) Availability Power BI Service, Power BI Report Server (with AAD), Azure Analysis Services Common Use Case: Row-Level Security (RLS) The primary and most powerful use of USEROBJECTID() is to implement dynamic Row-Level Security . Example Scenario: A company wants each sales rep to see only th...

Power BI DAX Function: UPPER Syntax UPPER(<text>) Description The UPPER function converts all letters in a text string to uppercase . Non-letter characters (numbers, spaces, symbols) remain unchanged. Parameters Parameter Description <text> A text string or a column containing text Return Value A text string with all letters converted to uppercase. Common Use Cases Standardizing data for lookups – Ensure consistent matching regardless of original case (e.g., "USA", "usa", "UsA" → all become "USA") Creating case-insensitive keys for relationships or merging queries Data cleansing – Make customer names, product codes, or categories appear uniform in reports Sorting consistency – When combined with sorting, uppercase ensures predictable alphabetical order Examples 1...

📈 DAX Function: MOVINGAVERAGE Concept and Syntax The MOVINGAVERAGE DAX function computes the average of a measure's values over a defined number of preceding and/or succeeding data points (a window ) in a time series. Simplified Syntax: MOVINGAVERAGE(<Measure>, <Date Column>, <Window Size> [, <Window Type>]) <Measure>: The measure to average (e.g., [Total Sales]). <Date Column>: The date column in your table. <Window Size>: The number of periods (days, months, etc.) to include in the average calculation. <Window Type> (Optional): Determines how the window is centered (e.g., Rolling for preceding periods only, Center for periods before and after). Use Case: Smoothing Sales Data The primary use case is Trend Analysis . Scenario: You have daily sales data that shows high volatility—some days are high, others are low. This makes it hard to see the underlying sales trend. Solution with MOV...

Power BI DAX: The COT Function Syntax COT(<value>) Parameters: <value> – A numeric expression in radians representing an angle. Description The COT function returns the cotangent of the given angle (in radians). Formula: COT(θ) = 1 / TAN(θ) Note: The input must be in radians . Use RADIANS() to convert degrees if needed. Use Cases Trigonometric calculations in engineering, physics, or geometry models. Financial modeling involving periodic functions or wave patterns. Scientific data analysis where cotangent is part of a formula (e.g., optics, mechanics). Creating custom KPIs or metrics based on angular relationships. Example Calculate cotangent of 45 degrees: Cotangent_45 = COT(RADIANS(45)) Result: 1 (since cot(45°) = 1) Important No...

Power BI DAX: CHISQ.INV Function The CHISQ.INV function in DAX returns the inverse of the left-tailed probability of the chi-squared (χ²) distribution. It is used in statistical analysis to find the critical value from the chi-squared distribution for a given probability and degrees of freedom. Syntax CHISQ.INV ( probability , deg_freedom ) Parameters probability – A decimal number between 0 and 1 (exclusive) representing the probability associated with the chi-squared distribution. deg_freedom – An integer representing the degrees of freedom. Must be ≥ 1. Return Value Returns a numeric value — the critical chi-squared value (χ²) such that the area to the left of this value under the chi-squared distribution curve equals the specified probability . Example: Critical Value = CHISQ.INV(0.95, 10) Returns approximat...

Power BI DAX: ISSUBTOTAL Function Overview The ISSUBTOTAL function in DAX determines whether the current cell in a matrix or table visual is displaying a subtotal value for a specified column. It is primarily used in visual-level calculations (like conditional formatting or dynamic measures) to apply different logic to subtotal rows versus detail rows. Syntax ISSUBTOTAL(<column>) Parameters Parameter Description <column> A column reference (e.g., Sales[Region] ) for which you want to check if the current row context is a subtotal. Return Value TRUE – If the current row is a subtotal for the specified column. FALSE – If it is a detail row or grand total. ...

Power BI DAX: DOLLARFR() Function Syntax DOLLARFR(decimal_price, fraction) Parameters Parameter Description decimal_price A decimal number representing the price (e.g., 1.25 for $1.25). fraction An integer that specifies the denominator of the fraction (e.g., 8 for 1/8ths, 32 for 1/32nds). Description The DOLLARFR() function converts a decimal price into a fractional dollar price commonly used in financial markets, especially in bond and treasury pricing where prices are quoted in fractions (like 1/32 or 1/64). It expresses the price in the format: whole_number + fraction , where the fractional part is based on the specified denominator. Exam...

Power BI DAX: SUBSTITUTEWITHINDEX Advanced Overview The SUBSTITUTEWITHINDEX function is a powerful text manipulation function in Power BI DAX, introduced to efficiently replace portions of a string using an index-based lookup table. It allows you to perform multiple, dynamic substitutions in a single operation — far more efficiently than nested SUBSTITUTE functions. Syntax SUBSTITUTEWITHINDEX ( <expression>, <search_table>, <index_column>, <replacement_column> [, <occurrence>] ) <expression> : The original text string to modify. <search_table> : A table containing search and replacement values. <index_column> : Column in the table with values to find in the expression....

Power BI DAX: TBILLYIELD Function Function Syntax TBILLYIELD( settlement , maturity , price ) Parameters settlement : The date when the Treasury bill is purchased (settlement date). maturity : The date when the Treasury bill matures (expiration date). price : The price per $100 face value of the Treasury bill. What It Returns The TBILLYIELD function calculates the yield (annualized return) of a U.S. Treasury bill, expressed as a decimal (e.g., 0.05 = 5%). Use Case: Treasury Bill Yield Analysis Use TBILLYIELD when you need to: Calculate the effective yield of a T-bill based on its purchase price and maturity. Compare returns across different T-bills with varying prices and terms. Support fixed-income investment dashboards or cash management reports. ...

Power BI DAX: The EXP Function The EXP function in DAX returns e raised to the power of a given number (i.e., the mathematical constant e x ). Syntax EXP(<number>) <number> : A scalar numeric expression (column, measure, or literal value). Note: e ≈ 2.718281828459 (Euler's number). EXP(1) returns e . How It Works The EXP function computes: e x where x is the input value. Examples: EXP(0) → 1 EXP(1) → 2.71828... EXP(2) → 7.38905... EXP(-1) → 0.36787... Use Cases 1. Exponential Growth Modeling Simulate compound growth, population increase, or investment returns. Projected Population = INITIAL_POPULATION * EXP(GROWTH_RATE * YEARS) ...

Power BI DAX: DEGREES Function Syntax DEGREES(angle_in_radians) Description The DEGREES function converts a number representing an angle in radians to degrees . This is useful when working with trigonometric functions in DAX (like SIN , COS , TAN ), which expect or return values in radians, but you need results in degrees for reporting or calculations. Parameters Parameter Description angle_in_radians A real number representing an angle in radians. Return Value A decimal number representing the angle in degrees . Example 1: Basic Conversion Convert π radians to degrees: Degrees in DAX = DEGREES(PI()) Result: 180 Example 2: Wi...

NORM.S.DIST Function in Power BI DAX Function Syntax NORM.S.DIST(z, cumulative) Parameters Parameter Description z The value for which you want the distribution (z-score). Must be a numeric value. cumulative A logical value (TRUE/FALSE): • TRUE = Returns the cumulative distribution function (CDF) • FALSE = Returns the probability density function (PDF) What It Returns If cumulative = TRUE : Probability that a standard normal random variable is ≤ z If cumulative = FALSE : Height of the standard normal curve at point z Example 1: Cumulative Probability (CDF) NORM.S.DIST(1.96, TRUE) Result: ...

Power BI DAX: ORDERBY Function Overview The ORDERBY function is used in DAX (Data Analysis Expressions) to define the sort order of columns in a table when creating calculated tables or modifying table structures. Note: ORDERBY is not a standalone function you use in measures or regular calculations. It is used inside table functions like SUMMARIZECOLUMNS to control sorting. Syntax ORDERBY ( <expression> [, ASC|DESC] [, <expression> [, ASC|DESC] ]... ) <expression> : A column or expression to sort by. ASC (default): Sort in ascending order. DESC : Sort in descending order. Use Case Example Suppose you want to create a summary table of Total Sales by Product Category , sorted by Sales descending , then by Category Name ascending . Sample DAX Code Sales Summary = SUMMARIZECOLUMNS( ...

Power BI DAX: ISNUMBER Function Syntax ISNUMBER(<value>) Description The ISNUMBER function checks whether a given value is a number (of numeric data type). It returns TRUE if the value is a number, and FALSE otherwise. This function is useful for data validation, filtering non-numeric entries, or conditional logic in calculated columns and measures. Parameters <value> : Any DAX expression that returns a scalar value (e.g., a column reference, literal, or calculation). Return Value TRUE or FALSE (Boolean) Use Case Example Scenario: You have a column SalesAmountText that contains mixed data — some numeric strings like "100", some actual numbers, and some text like "N/A". You want to create a calculated column that flags only true...

CONTAINSSTRINGEXACT DAX Function in Power BI The CONTAINSSTRINGEXACT function checks whether one text string contains another text string exactly (case-sensitive and whole-word match). Syntax CONTAINSSTRINGEXACT(<within_text>, <find_text>) Parameter Description within_text The text string to search within (e.g., a column value). find_text The exact text to find (must match case and be a whole word). Return Value Returns TRUE if find_text is found as an exact, case-sensitive, whole-word match within within_text . Otherwise, returns FALSE . Note: Unlike CONTAINSSTRING (which is case-insensitive and allows partial matches), CONTAINSSTRINGEXACT requires: E...

Power BI DAX Function: SAMPLEAXISWITHLOCALMINMAX What is SAMPLEAXISWITHLOCALMINMAX? This is an advanced DAX (Data Analysis Expressions) function in Power BI used for data sampling in visualizations. It helps reduce the number of data points in line charts with a continuous (numeric) X-axis, preventing overcrowding while preserving key trends. The function bins the X-axis into equal-sized groups and keeps local minimum and maximum values from each bin across multiple series (like different lines on a chart). Note: This function is primarily internal to Power BI visuals and is undocumented for direct user use in most scenarios. It's volatile (results can vary) and not supported in DirectQuery for calculated columns or row-level security. Syntax SAMPLEAXISWITHLOCALMINMAX( , , , , [ [, ...] ], [, [, ] [, ] [, ] ) Size : Number of rows to return (approximate). ...

Power BI DAX: WEEKNUM Function Syntax WEEKNUM(<date> [, <return_type>]) Parameters Parameter Description <date> A date in datetime format or a column containing dates. <return_type> (optional) A number that determines the week numbering system: 1 or omitted: Week begins on Sunday (ISO week 1 contains Jan 4). 2 : Week begins on Monday (ISO 8601 standard). 21 : Week begins on Sunday (week 1 is the first week with 4+ days in the new year). Return Value An integer representing the week number of the year for the given date. Use Cases Weekly Sales Reporting : Group sales data by week for trend analy...

ISO.CEILING DAX Function in Power BI Function Syntax ISO.CEILING(<number> [, <significance>]) Description The ISO.CEILING function rounds a number up to the nearest multiple of a specified significance, following the ISO standard (same behavior as CEILING in most cases, but ensures consistency across locales). Unlike CEILING.PRECISE , it allows a custom significance (step value), and always rounds up (away from zero for positive numbers). Returns: A decimal number rounded up to the nearest multiple of significance . Parameters Parameter Description <number> The numeric value you want to round up. <significance> (optional) The mul...

Power BI DAX: AVERAGE Function Function Syntax AVERAGE(<column>) Description The AVERAGE function calculates the arithmetic mean of the values in a column, ignoring any BLANK values and non-numeric entries. Returns: A decimal number representing the average of all numbers in the specified column. Use Case Use AVERAGE when you want to find the typical value in a dataset, such as: Average sales per transaction Average customer satisfaction score Average employee performance rating Average delivery time in days Example Suppose you have a table SalesData with the following data: SalesID Amount 1 100 2 150 ...