What the DAX?

Power BI DAX Function: ISCURRENCY Description The ISCURRENCY function in DAX checks whether a value is of the currency data type (also known as fixed decimal or currency type in Power BI). It returns TRUE if the value is currency, otherwise FALSE . Note: ISCURRENCY is an alias for ISDECIMAL, as the currency type in DAX is a fixed decimal number with exactly 4 decimal places. Syntax ISCURRENCY( ) Parameters : The expression or column value to check. Return Value A Boolean: TRUE if the value is of currency data type, FALSE otherwise. Use Cases Validating data types in calculated columns or measures, especially when working with mixed data sources. Conditional logic where different calculations are needed based on whether a value is currency (e.g., financial reports). Debugging or ensuring proper type handling in complex DAX expressions involving unio...

Power BI DAX Function: LOOKUPWITHTOTALS LOOKUPWITHTOTALS is a DAX function introduced in Power BI as part of visual calculations (available since around May 2025). It is designed specifically for use in visual calculations on matrix or table visuals. Syntax LOOKUPWITHTOTALS( <expression>, <column_reference>, <value> [, <column_reference>, <value>] ... ) <expression> : The measure or expression to evaluate (e.g., a sales amount measure). <column_reference> : A column on the visual's axes (rows or columns). <value> : The specific value to match in that column. Additional pairs of column_reference and value are optional for multiple filters. How It Works LOOKUPWITHTOTALS looks up and evaluates the expression in the visual's matrix where the specified columns ...

Power BI DAX Function: IF.EAGER Overview The IF.EAGER function in DAX is a logical function that evaluates a condition and returns one value if the condition is TRUE, and another (optional) value if FALSE. It behaves functionally like the standard IF function but differs in how the DAX engine evaluates its arguments. Syntax IF.EAGER( , [, ]) logical_test : Any expression that evaluates to TRUE or FALSE. value_if_true : The value returned if the condition is TRUE. value_if_false : (Optional) The value returned if the condition is FALSE. If omitted, BLANK() is returned. Key Difference from IF The standard IF function uses either strict or eager evaluation depending on what the DAX engine determines is optimal. In contrast, IF.EAGER always uses eager evaluation : Both value_if_true and value_if_false expressions are evaluated before checking the condition. Th...

INFO.STORAGETABLECOLUMNS DAX Function The INFO.STORAGETABLECOLUMNS is a tabular DAX function in Power BI that returns metadata about the storage of columns in the semantic model's storage tables. It corresponds directly to the Dynamic Management View (DMV) DISCOVER_STORAGE_TABLE_COLUMNS used in Analysis Services. Syntax INFO.STORAGETABLECOLUMNS ( [ [, ] [, ...]]) Parameters are optional restrictions (filters) on columns like TableID, ColumnID, etc., to narrow down the results. What It Returns A table containing detailed information about how columns are stored in the Vertipaq engine (the in-memory storage engine for Power BI and Tabular models). Typical columns in the result include: TableID and ColumnID DictionarySize (size of the dictionary for value encoding) DataSize (compressed data size) RowsCount BitsCount (bits used for encoding) And other storage-spe...

Power BI DAX Function: XIRR The XIRR function in DAX (Data Analysis Expressions) calculates the Internal Rate of Return (IRR) for a series of cash flows that occur at irregular intervals . It is particularly useful for financial analysis where payments or receipts do not happen periodically. What is XIRR? XIRR stands for eXtended Internal Rate of Return . It finds the interest rate that makes the net present value (NPV) of the cash flows equal to zero, accounting for the exact timing of each cash flow using specific dates. Negative values typically represent outflows (e.g., investments or costs). Positive values represent inflows (e.g., returns or income). There must be at least one positive and one negative cash flow for the calculation to work. Syntax XIRR(, , [, ] [, ]) Parameters Parameter Description A table expression containing the cash flows and dates (usually a reference to a table). ...

Power BI DAX Function: INTRATE The INTRATE function in Data Analysis Expressions (DAX) calculates the interest rate for a fully invested security — a security that pays all interest at maturity (no periodic coupons). Syntax INTRATE( , , , [, ]) Parameters Parameter Description settlement The security's settlement date (date purchased). maturity The security's maturity date (date redeemed). investment The amount invested (purchase price). redemption The amount received at maturity (face value). basis (optional) Day count basis (default: 0). 0: US (NASD) 30/360 1: Actual/actual 2: Actual/360 3: Actual/365 4: European 30/360 Return Value The annual interest rate as a decimal (format as percentage in visuals). Formula INTRATE uses: INTRATE = ((redemption - investment) / investment) × (B / DIM) Where: - DIM : Days from settlement to maturity. -...

Power BI DAX Function: INFO.ROLEMEMBERSHIPS Description The INFO.ROLEMEMBERSHIPS function is a DAX information function that returns a table listing all role memberships defined in the current data model. It corresponds directly to the TMSCHEMA_ROLE_MEMBERSHIPS Dynamic Management View (DMV) in tabular models. The returned table includes columns such as Role ID, Member Name (user or group), and other metadata matching the schema for role memberships. Syntax INFO.ROLEMEMBERSHIPS() No parameters are required. Key Characteristics Returns a table (tabular result). Can be used in DAX queries (e.g., in DAX Query View or tools like DAX Studio). Cannot be used in calculated tables or calculated columns. Requires model admin permissions in Power BI Service (similar to DMVs). Performs context transition if called in a row context. Its use is not recommended for regular reporting; it's primarily for metadata inspection. Use Cases Model Doc...

Power BI DAX Function: GCD The GCD (Greatest Common Divisor) function in DAX returns the largest positive integer that divides two integers without leaving a remainder. It is a mathematical function similar to Excel's GCD. Syntax GCD(number1, number2) number1, number2 : Integer values (or expressions evaluating to integers). Only two arguments are supported. Return Value An integer representing the greatest common divisor. Remarks & Limitations Returns #VALUE! if any argument is non-numeric. Returns #NUM! if any argument is less than 0 or >= 2^53. Not supported in DirectQuery mode for calculated columns or row-level security (RLS). The function is considered deprecated in some contexts, but still available. Examples Expression Result Explanation GCD(12, 18) 6 6 divides both 12 and 18 evenly. GCD(7, 13) 1 7 and 13 are prime to each other. GCD(48, 18) 6 Largest common divisor is 6. GCD(5, 0) 5 GCD with 0 is ...

Power BI DAX Function: ENDOFMONTH Description The ENDOFMONTH function is a time intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns a table containing a single column and single row with the last date of the month in the current filter context, based on the provided dates. Syntax ENDOFMONTH( ) Parameters: : A column reference containing dates, or a table expression returning a single column of dates. Return Value: A single-column table with one row containing the end-of-month date. Example Assuming you have a Date table marked as a date table: Month End Sales = CALCULATE( SUM(Sales[Amount]), ENDOFMONTH('Date'[Date]) ) This measure calculates the total sales as of the last day of the month in the current context (e.g., for monthly reports, it aggregates sales up to month-end). Use Cases Month-end reporting : Calcul...

Power BI DAX Function: ROLLUPGROUP Overview The ROLLUPGROUP function in DAX is used within SUMMARIZE or SUMMARIZECOLUMNS expressions to control how rollup (subtotal and grand total) rows are generated. It groups multiple columns together, treating them as a single unit in the rollup hierarchy. This allows you to create combined subtotals instead of separate partial subtotals for each column. Note: ROLLUPGROUP does not return a value itself; it modifies the behavior of grouping and rollup operations. Syntax ROLLUPGROUP ( [, [, … ] ] ) It is typically used inside a ROLLUP or directly in the grouping parameters of SUMMARIZE . Use Cases Creating grouped subtotals: When you have multiple grouping columns (e.g., Year and Education Level) and want a single subtotal that combines them, rather than separate subtotals for each. Avoiding partial subtotals: In complex hierarchies, using ROLLUPGROUP inside ROLLUP preven...

Power BI DAX Function: ACOSH The ACOSH function in Data Analysis Expressions (DAX) is a mathematical function used in Power BI, Analysis Services, and Power Pivot. Description The ACOSH function returns the inverse hyperbolic cosine (also known as hyperbolic arccosine) of a number. Mathematically, it computes the value whose hyperbolic cosine is the given number. Key property: ACOSH(COSH(number)) = number Syntax ACOSH(number) Parameters Parameter Description number A real number greater than or equal to 1. If the number is less than 1, the function returns an error. Return Value A decimal number representing the inverse hyperbolic cosine in radians. Examples Expression Result ACOSH(1) 0 ACOSH(10) Approximately 2.993222 ACOSH(COSH(4)) 4 Use Cases ACOSH is a specialized function primarily used in a...

DAX Function: DATESBETWEEN Description The DATESBETWEEN function in Power BI DAX is a time intelligence function that returns a one-column table containing all dates between a specified start date and end date (inclusive). It is commonly used inside CALCULATE to filter calculations over a custom date range. Syntax DATESBETWEEN(<Dates>, <StartDate>, <EndDate>) Parameters <Dates> : A reference to a date column (usually from a marked Date table, e.g., 'Date'[Date]). <StartDate> : The starting date of the range. Can be a fixed date, BLANK() for the earliest date, or a dynamic expression. <EndDate> : The ending date of the range. Can be a fixed date or dynamic expression. Common Use Cases Calculating metrics for a specific fixed period (e.g., a fiscal quarter or camp...

Power BI DAX Function: INFO.REFRESHPOLICIES() Overview INFO.REFRESHPOLICIES() is one of the new INFO DAX functions introduced in Power BI's DAX query view (available since late 2023). These INFO functions provide metadata about the semantic model (dataset), similar to Dynamic Management Views (DMVs) in Analysis Services. Specifically, INFO.REFRESHPOLICIES() returns a table containing information about the incremental refresh policies configured on tables in the current semantic model. Syntax INFO.REFRESHPOLICIES() This function takes no parameters and returns a table with columns detailing the refresh policies (e.g., table ID, policy type, rolling window periods, etc.). Use Cases Model Documentation: Query and document which tables have incremental refresh policies applied, including details like historical and incremental periods. Auditing and Troubleshooting: Verify incremental refresh c...

Power BI DAX Function: GENERATESERIES Description The GENERATESERIES function in DAX returns a single-column table containing an arithmetic sequence of values (numbers, dates, or times). The column is named "Value" by default. It is commonly used to generate sequences for tables, parameters, or dynamic calculations. Syntax GENERATESERIES( , [, ]) Parameters Parameter Description The starting value of the series (required). The ending value of the series (required). The sequence includes values up to and including the last value ≤ endValue. The optional increment (step). Defaults to 1 if omitted. Return Value A table with one column ("Value") containing the sequence. Returns an empty table if startValue > endValue. Examples Basic integers: GENERATESERIES(1, 5) Returns: 1, 2, 3, 4, 5 With incremen...

INFO.HIERARCHYSTORAGES DAX Function Description INFO.HIERARCHYSTORAGES() is a tabular DAX function in Power BI (and Analysis Services) that returns metadata about the storage details of hierarchies in the semantic model. It corresponds to the TMSCHEMA_HIERARCHY_STORAGES Dynamic Management View (DMV), providing administrative insights into how hierarchy data is stored internally. This function returns a table with columns containing information such as hierarchy IDs, storage modes, and related properties. Syntax INFO.HIERARCHYSTORAGES() No parameters. It simply returns a table of hierarchy storage metadata. Use Cases Model Documentation and Analysis: Query the structure and storage of hierarchies directly within Power BI using DAX Query View. Advanced Model Inspection: Useful for developers and administrators to understand internal storage configurations, optimize models, or troubleshoot h...

Power BI DAX Function: DATESINPERIOD Description The DATESINPERIOD function is a Time Intelligence function in DAX that returns a table containing a single column of dates. These dates begin from a specified start date and continue for a given number of intervals (forward or backward in time). It is commonly used with the CALCULATE function to modify the filter context for time-based calculations, such as rolling totals or moving averages. Syntax DATESINPERIOD( , , , ) : A reference to a date column (usually from a marked Date table). : The starting date for the period (often MAX('Date'[Date]) for dynamic periods). : The number of intervals to include. Positive for future, negative for past. : The type of interval: DAY, MONTH, QUARTER, or YEAR (no quotes needed). Use Cases DATESINPERIOD is ideal for rolling or trailing periods where the end date is dynamic (e.g., the last available date ...

Power BI DAX Function: COUPNUM The COUPNUM function in DAX (Data Analysis Expressions) is a financial function used in Power BI, Analysis Services, and Power Pivot. It calculates the number of coupon payments (interest payments) payable on a bond or security between the settlement date and the maturity date. Syntax COUPNUM(<settlement>, <maturity>, <frequency> [, <basis>]) Parameters Parameter Description <settlement> The security's settlement date (the date the buyer purchases the bond). Must be a valid date. <maturity> The security's maturity date (the date when the bond expires). <frequency> The number of coupon payments per year: 1 = Annual 2 = Semi-annual 4 = Quarterly ...

Power BI DAX Function: BITLSHIFT Description The BITLSHIFT function in Data Analysis Expressions (DAX) performs a bitwise left shift operation on an integer. It shifts the bits of the specified number to the left by the given number of positions, filling the low-order bits with zeros. This is mathematically equivalent to multiplying the number by 2 raised to the power of the shift amount (for positive shifts). Note: If the shift amount is negative, it performs a right shift instead (equivalent to BITRSHIFT with a positive amount). Syntax BITLSHIFT( , ) Parameters Number : An integer expression (can be positive or negative). Shift_Amount : An integer specifying the number of bits to shift (positive for left, negative for right). Examples Expression Result Explanation BITLSHIFT(4, 2) 16 4 in binary is 100; shifting left by 2: 10000 (16) BITLSHIFT(10, 1) 20 10 (1010) → 10100 (20) BITLSHIFT(8, 3) 64 8 (1000) → 1000000 (64) BITLSHIFT(16, ...

Power BI DAX Function: USERCULTURE() Description The USERCULTURE() function returns the culture code (locale) of the current user as a string in the format "languagecode-country/regioncode", such as "en-US" for English (United States) or "de-DE" for German (Germany). It has no parameters: USERCULTURE() The locale is determined by: Browser settings or operating system in Power BI Desktop. User's language settings in the Power BI service (Settings > Language). Use Cases This function is primarily used for creating multilingual or localized reports in Power BI, especially in Premium workspaces. Common scenarios include: Dynamic visual titles and labels: Translate titles based on the user's language. Locale-specific formatting: Format dates, numbers, or text according to the user's culture. Conditional logic for translations: Use with SWITCH to select translated text or values. Examples 1. Dynamic Vi...

INFO.PROPERTIES DAX Function Category: Information Function (Power BI specific) Syntax INFO.PROPERTIES() What It Returns Returns a table containing metadata (properties) about the current Power BI model, such as: Model name Compatibility level Refresh date and time Power BI service dataset ID Whether it's in Import, DirectQuery, or Composite mode Last processed time Server name, database name (in DirectQuery) Example Output (Sample) Property Name Value ModelName Sales Report 2025 CompatibilityLevel 1600 LastUpdate 2025-12-10T08:30:22 DataSourceType Import RefreshedTime 12/10/2025 08:30 AM DatasetId a1b2c3d4-5678-90ef-... Common Use Cases 1. Show last refresh time on...

INFO.MODEL – DAX Function in Power BI Syntax INFO.MODEL() Description INFO.MODEL() is a system information function in DAX that returns a table containing metadata about the current data model. It provides detailed information about every table, column, measure, hierarchy, and relationship in the Power BI (.pbix) or Analysis Services model. Return Value A table with the following columns: Column Name Description ID Unique internal identifier of the object Name Name of the table, column, measure, etc. Type Type of object: Table, Column, Measure, Hierarchy, Relationship, etc. Description Description (if any was entered in the model) IsHidden TRUE if the object i...

DAX Function: FV (Future Value) The FV function in Power BI DAX calculates the future value of an investment based on periodic, constant payments and a constant interest rate. Syntax FV( <rate>, <nper>, <pmt>, [pv], [type] ) Parameters Parameter Description Required? rate Interest rate per period (e.g., 5%/12 for monthly) Required nper Total number of payment periods Required pmt Payment made each period (usually negative for outflows) Required [pv] Present value (initial investment). Default = 0 Optional [type] 0 = payment at end of period, 1 = payment at beginning. Default = 0 Optional Common Use Cases Calculate how much a savings plan or retirement contribution will be worth in the future Financial modeling and forecasting...

INFO.DATASOURCES DAX Function Category: Information Function (Power BI specific) Syntax INFO.DATASOURCES ( ) Description Returns a table containing metadata about all data sources used in the current Power BI model. This function is only available in Power BI (not in Excel Power Pivot or SSAS Tabular). Returned Table Columns Column Name Description ConnectionString The full connection string used to connect to the data source Url URL of the data sources (e.g., web, SharePoint, OData) Server Server name for database connections Database Database name (if applicable) Type Type of data source (e.g., Sql, OData, Web, Folder, etc.) PrivacyLevel ...

Power BI DAX: The CALCULATE Function (Simplified) What is CALCULATE? CALCULATE is the most important and powerful function in DAX. It evaluates an expression (like a measure) in a modified filter context . In simple words: It lets you change the filters temporarily while calculating something. Basic Syntax CALCULATE(<expression>, <filter1>, <filter2>, ...) <expression> → Usually a measure like Total Sales = SUM(Sales[Amount]) <filter> → Conditions that modify the current filter context Common Use Cases Use Case Example Explanation 1. Sales in a Specific Year Sales 2024 = CALCULATE( SUM(Sales[Amount]), 'Date'[Year] = 2024 ) Shows total sales only for year 2024, no matter what slicer user selects 2. % of Total Sales ...

Power BI DAX Function: HASONEVALUE() Syntax: HASONEVALUE(<columnName>) Returns: TRUE or FALSE What does HASONEVALUE() do? It checks whether a column has exactly one distinct value in the current filter context. TRUE → Only one unique value exists (e.g., when you are looking at a single customer, single month, etc.) FALSE → There are multiple values or no values (e.g., total level, multiple selections) Common Use Cases Use Case Why HASONEVALUE() is Needed Example Measure Show a value only at detailed level (not in totals) Totals have many values → HASONEVALUE returns FALSE Sales Amount (Detail Only) = IF( HASONEVALUE(Customer[CustomerKey]), SUM(Sales[SalesAmount]), BLANK() ) ...

Power BI DAX - ABS() Function ABS() is a simple but very useful mathematical function in DAX that returns the absolute (positive) value of a number. Syntax ABS(number) Parameters number – Any real number or a column/expression that returns a number Return Value A positive number (or zero). The sign of negative numbers is removed. Examples Expression Result ABS(-10) 10 ABS(15) 15 ABS(-5.7) 5.7 ABS(0) 0 Common Use Cases in Power BI Calculate forecast error magnitude Absolute Error = ABS([Actual Sales] - [Forecasted Sales]) Handle negative variances positively Budget Variance (Abs) = ABS([Budget] - [Actual]) Distance or difference calculations When you only care about magnitude, not direc...

Power BI DAX: ISSELECTEDMEASURE() Function Introduced in: December 2023 Update (Calculation Groups 2.0) What is ISSELECTEDMEASURE()? The ISSELECTEDMEASURE() function checks whether one or more specified measures are currently selected in a calculation item (inside a calculation group). Syntax ISSELECTEDMEASURE ( <measure1> [, <measure2>, ...] ) Return Value TRUE – if at least one of the listed measures is the one currently being evaluated by the calculation item. FALSE – otherwise. Use Case: Dynamic Formatting or Logic Based on Selected Measure This function is extremely useful when you want a calculation item to behave differently depending on which base measure the user has selected in a visual. Real-World Example Imagine you have a calculation group called "Dynamic Format" with one calculation item ...

Power BI DAX: STARTOFQUARTER Function Syntax STARTOFQUARTER(<date>) Description Returns the first date of the quarter that contains the given date. The result is always a date at midnight (00:00:00) of the first day of that quarter. Parameters <date> – Any expression that returns a date/datetime value (usually a column from a date table) Return Value A date representing the first day of the quarter (e.g., 1 January, 1 April, 1 July, or 1 October). Examples Input Date STARTOFQUARTER Result Quarter 2025-03-15 2025-01-01 Q1 2025 2025-05-20 2025-04-01 Q2 2025 2025-08-10 2025-07-01 ...

Power BI DAX – ATANH Function ATANH returns the inverse hyperbolic tangent of a number. Syntax ATANH(number) Parameter Description number A real number between -1 and 1 (inclusive: -1 ≤ number ≤ 1). The value must be in this range, otherwise the function returns an error. Return Value A decimal number representing the inverse hyperbolic tangent of the input. Key Properties ATANH is the inverse of TANH : ATANH(TANH(x)) = x ATANH(-x) = -ATANH(x) → it is an odd function Domain: -1 ≤ number ≤ 1 Range: All real numbers (-∞ to +∞) Common Use Cases in Power BI / Analysis Services ATANH is relatively rare in typical business reports, but very useful in specific ...

Power BI DAX: LASTDATE() Function Syntax LASTDATE ( ) What It Does LASTDATE() returns a single date (as a table with one row and one column) that represents the latest (most recent) date in the current filter context from the specified date column. It is a table function that returns a one-column, one-row table containing the last date. Note: LASTDATE() ignores blank dates and only considers actual valid dates. It respects the current filter context (slicers, filters, rows in visuals, etc.). Key Differences Function Returns Use When LASTDATE(DateColumn) Table (1x1) You need the date value inside CALCULATE or other functions MAX(DateColumn) Scalar value You just need the date as a value (simpler in many cases) In most modern scenarios, MAX(Date[Date]) is preferred over LASTDATE(Date[Date]) because it's simpler and returns a scalar directly. Com...

DAX Function: SQRT Returns the square root of a number. Syntax SQRT(<number>) Parameters Parameter Description <number> The number for which you want the square root. Must be a non-negative number. Return Value A decimal number representing the square root of the given number. Note: If the input is negative, SQRT returns a blank (or an error in some contexts). Common Use Cases Calculating distances using the Pythagorean theorem (e.g., diagonal of a rectangle) Computing standard deviation components or volatility in financial models Geometry calculations (area of circles from diameter, etc.) Normalizing or scaling data Reverse-engineering squared values (e....