What the DAX?

DAX Function: STARTOFWEEK STARTOFWEEK returns a table containing the first date of the week in the current filter context, according to a properly marked calendar table. Syntax STARTOFWEEK ( <calendar> ) <calendar> = name of your date/calendar table (must be marked as date table in Power BI) Return Value A single-row table with: All primary tagged columns (usually Date) All other time-intelligence related columns from your calendar Important: This is a time-intelligence function. → Requires a proper calendar table marked as Date table → Works with calendar (not fiscal) week logic in recent versions Common Use Cases Week-to-date (WTD) calculations WTD Sales = CALCULATE( [Total Sales], DATESBETWEEN( 'Calendar'[Date], STARTOFWEEK('Calendar'), LASTDATE('Calendar'[Date]) ) ) Compare current week vs previous week Sales same week last year = CALCULATE( [Total Sales...

DAX Function: PREVIOUSWEEK Purpose: Returns a table with all dates from the previous week (relative to the first date in the current context). Syntax PREVIOUSWEEK ( <Calendar> ) <Calendar> = reference to your date/calendar table (must be properly marked as date table or used in enhanced time intelligence mode) Important Requirements You need a proper Date / Calendar table Best used with Enhanced DAX Time Intelligence (preview feature → now more widely available) Usually combined with CALCULATE Most Common Use Case Compare this week vs previous week (week-over-week analysis) Typical pattern – Previous Week Sales: Previous Week Sales = CALCULATE( [Total Sales], PREVIOUSWEEK( 'Calendar' ) ) Other useful variations: -- Previous week margin % Previous Week Margin % = DIVIDE( CALCULATE([Total Margin], PREVIOUSWEEK('Calendar')), CALCULATE([Total Sales], PREVIOUSWEEK('Calendar')) ) -- WoW % c...

The DAX function NATURALJOINUSAGE  This is a specialized, mostly undocumented table manipulation function. Based on the documentation from DAX Guide, it is not intended for standard report authoring but serves a specific role in complex data architectures. Explanation of NATURALJOINUSAGE The function is used to add a table as a Natural Join into the filter context. Unlike common join functions like NATURALINNERJOIN, which return a physical-style table result, NATURALJOINUSAGE acts as a value filter within specific query structures. Key Characteristics:     Restriction: It can only be used as a value filter within the SUMMARIZECOLUMNS function.     Internal Purpose: It is primarily intended for internal use by the Power BI engine. Composite Models: It is specifically designed to handle queries sent to remote semantic models within a Composite Model (where one Power BI model connects to another Power BI dataset or Analysis Services model).      Non-...

DAX ISDECIMAL Function ISDECIMAL checks if a value has the decimal number data type in Power BI (also known as fixed-precision decimal / currency type). Syntax ISDECIMAL ( <value> ) Parameter Description <value> The expression or column value to test Return Value TRUE → value is a decimal number (fixed decimal / Currency type) FALSE → value is anything else (whole number, float/double, text, date, blank, etc.) Important Notes ISDECIMAL is just another name (alias) for ISCURRENCY In Power BI, "Decimal Number" usually means floating point (Double), while Fixed Decimal Number = Currency type = what ISDECIMAL detects Not supported in DirectQuery mode (calculated columns or RLS rules) Common Use Cases 1. Clean mixed imported data (most common real-world use) ValidCurrency = IF( ISDECIMAL ( 'Sales'[Amount Imported] ), 'Sales'[Amount Imported], BLANK() ) → Only keeps...

Power BI DAX Function ISDOUBLE  The ISDOUBLE function in Power BI (DAX) is a relatively straightforward but essential information function. Its primary purpose is to check whether a value or an expression results in a Double (a double-precision floating-point) data type. How it Works The function returns a simple Boolean value:     • TRUE: If the value is a Double (decimal number).     • FALSE: If the value is any other data type (String, Integer, Date, Boolean, etc.). Syntax ISDOUBLE(<value>) Why use ISDOUBLE? In DAX, "Double" is the data type used for Decimal Numbers. While it might seem obvious what a number is, DAX distinguishes between Integers (whole numbers) and Doubles (numbers with potential decimals). Key Use Cases     • Data Validation: Before performing complex scientific or financial calculations that require high precision, you can use ISDOUBLE to ensure the input data is in the correct format.     • Conditional Logic in M...

DAX Function: INFO.VIEW.TABLES() INFO.VIEW.TABLES is a DAX function in Power BI (introduced around late 2024) that returns a table containing metadata about all tables in your semantic model.It is part of the more user-friendly INFO.VIEW family of functions (compared to the older INFO.* functions), and — crucially — can be used inside calculated tables, measures, and columns (unlike most plain INFO functions). Main Use Case The primary and most powerful use case is automatic model self-documentation. You create one or more calculated tables that list: All tables in the model Their properties (name, description, storage mode, whether it's a calculated table, calculation group, date table, etc.) Hidden/visible status, row count hints, etc. → The documentation stays up-to-date automatically every time the model refreshes. Very useful for: Large team / enterprise models Handover to other developers Governance & auditing Training new team members Finding hidden/orphaned tables Qui...

DAX ISINT64 Function ISINT64 checks whether a value is a whole number (integer) that fits in a 64-bit integer format. It returns: TRUE → value is an integer (no decimal part) FALSE → value has decimal places, is text, blank, date, etc. Important: ISINT64 is just another name (alias) for ISINTEGER . Microsoft recommends using ISINTEGER in most cases. Syntax ISINT64 ( <value> ) Parameter Description <value> The value / expression / column to test Basic Examples // Returns TRUE ISINT64( 42 ) ISINT64( -17 ) ISINT64( 0 ) // Returns FALSE ISINT64( 3.14 ) // decimal ISINT64( "123" ) // text ISINT64( BLANK() ) // blank ISINT64( TODAY() ) // date Realistic Use Cases (2025–2026) Flexible User-Defined Functions (UDFs – Preview feature) // Modern DAX UDF pattern (very common since 2025) FUNCTION GetNameOrId = ( input ) => IF ( ISINT64 ( input ), ...

TOTALWTD – Week-to-Date Total TOTALWTD is a DAX time-intelligence function in Power BI that calculates the cumulative total (running total) from the start of the week up to the current date in context. Syntax TOTALWTD( <expression>, <dates> [,<filter>] ) Parameters <expression> → usually an aggregation like SUM(Sales[Amount]) <dates> → reference to your date column (e.g. 'Calendar'[Date] ) [<filter>] → optional additional filter (rarely used) Important Requirements The table containing <dates> must be marked as a Date table in Power BI Week starts on Sunday by default (standard ISO-like week behavior in most cases) Does not work reliably in DirectQuery mode for calculated columns / RLS Common Use Cases 1. Week-to-date Sales WTD Sales = TOTALWTD( SUM(Sales[Amount]), 'Calendar'[Date] ) → Shows sales from Monday (or Sunday) until the selected day. 2....

DAX Function: ISINTEGER Purpose: Checks if a value is a whole number (integer) → returns TRUE or FALSE Syntax ISINTEGER ( <value> ) Parameters Parameter Description <value> The value to test (can be a number, column reference, expression...) Returns TRUE → value is a whole number (..., -2, -1, 0, 1, 2, 42, ...) FALSE → value has decimal places, is text, blank, etc. Important: ISINTEGER is just another name for ISINT64 . It checks for whole numbers — not whether the value is numeric in general. (Use ISNUMBER if you want to check "is this any kind of number?") Simple Examples Direct values: ISINTEGER( 42 ) → TRUE ISINTEGER( 0 ) → TRUE ISINTEGER( -17 ) → TRUE ISINTEGER( 3.0 ) → TRUE (3.0 is mathematically an integer) ISINTEGER( 3.1 ) → FALSE ISINTEGER( "42" ) → FALSE (text is not integer) ISINTEGER( BLANK() ) → FALSE Realistic Use Cases ...

OPENINGBALANCEWEEK – DAX Function Purpose: Returns the value of an expression calculated at the end of the previous week (i.e. the opening balance position carried into the current week). Syntax (2025+ calendar-based version) OPENINGBALANCEWEEK( <expression>, <calendar> [,<filter>] ) Parameters expression → usually a measure like SUM(Sales[Amount]) or SUMX(Inventory, [Value]) calendar → reference to your calendar table (must have date column marked as Date table) filter (optional) → additional filter context (rarely used) What it really returns The value your measure had on the last day of the previous week. → Monday's opening balance = Sunday's closing balance (of the prior week) Typical Use Cases Opening inventory / stock value at the start of each week Opening cash / bank balance before weekly transactions Carried-forward arrears / backlog at week start Week-to-date change calculation : ...

INFO.VIEW.RELATIONSHIPS( ) What it does: Returns a table containing detailed metadata about every relationship defined in your current Power BI semantic model. Syntax INFO.VIEW.RELATIONSHIPS() No parameters – just call it as is. Typical usage (as a calculated table) Model Relationships = INFO.VIEW.RELATIONSHIPS() Most useful columns returned Column Type Description ID Integer Unique numeric ID of the relationship From Table String Name of the "many" side table From Column String Column name used on the many side To Table String Name of the "one" side table To Column String Column name used on the one side CrossFilteringBehavior String Single / Both / Automatic IsActive Boolean Is this relationship currently active? FromCardinality / ToCardinality String * → 1, 1 → 1, etc. RelyOnReferentialIntegrity Bo...

INFO.VIEW.MEASURES – Power BI DAX Function Introduced: ~October 2024 Purpose: Returns a table containing metadata about all measures in your current semantic model (Power BI dataset). Syntax INFO.VIEW.MEASURES ( ) No parameters – super simple! What Columns Does It Return? Column Name Data Type Description ID Integer Internal ID of the measure Name String Measure name (what you see in the Fields pane) Table String Name of the home table where the measure lives Description String Description (if filled in) DataType String Result data type (e.g. Decimal, Integer, ...) Expression String The actual DAX code of the measure FormatString String Current / static format string ...

DAX Function ISDATETIME  In Power BI, ISDATETIME is an Information function in DAX used to validate whether a specific value or expression results in a DateTime data type. It is essentially a "safety check" for your data logic, ensuring that the input you are passing into other functions is actually a date or time before you try to calculate something with it. 1. Syntax and Return Value The syntax is straightforward: ISDATETIME(<value>)     • Value: Any DAX expression or column reference you want to check.     • Returns: A Boolean value—TRUE if the value is a date/time; FALSE otherwise. 2. Key Use Cases While Power BI usually handles data types automatically, ISDATETIME becomes critical in scenarios where data is "dirty" or polymorphic (mixed types). A. Data Validation in Conditional Logic If you have a column that might contain mixed data (like a "Notes" column where some entries are dates and others are text), you can use ISDATETIME to prevent your...

DAX ISNUMERIC / ISNUMBER Both functions do the same thing — Microsoft calls them aliases. What they do Returns TRUE if the value is a number, otherwise FALSE . Syntax ISNUMERIC ( <value> ) ISNUMBER ( <value> ) You can use either name — result is identical. Simple examples ISNUMBER( 42 ) → TRUE ISNUMBER( 3.14 ) → TRUE ISNUMBER( -500 ) → TRUE ISNUMBER( "123" ) → FALSE (it's text) ISNUMBER( "abc" ) → FALSE ISNUMBER( BLANK() ) → FALSE ISNUMBER( TODAY() ) → TRUE (dates are numeric in DAX) ISNUMBER( 1/1/2025 ) → TRUE Most common real-world use cases Clean dirty imported data (mixed text + numbers) Safe Number = IF( ISNUMBER( Sales[Amount Text] ), VALUE( Sales[Amount Text] ), BLANK() ) Conditional formatting / flags Is Numeric Flag = IF( ISNUMBER( Data[Column] ), "Number ✓", "Text / Error ✗" ) ...

DATESWTD – Week-To-Date in DAX DATESWTD returns all dates from the start of the current week up to the date in context . Basic Syntax DATESWTD(<dates> [, <week_start_day>]) <dates> → usually your calendar table date column <week_start_day> → optional • 1 = Monday (most common in Europe/UK) ← default in many countries • 2 = Tuesday • … • 7 = Sunday (most common in USA) Most Common Real-World Use Cases What you want Typical Measure Formula Week-to-date Sales WTD Sales WTD Sales = CALCULATE([Total Sales], DATESWTD('Calendar'[Date])) WTD Orders (week starts Monday) WTD Orders WTD Orders = CALCULATE([Order Count], DATESWTD('Calendar'[Date], 1)) WTD (week starts Sunday ...

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...

Power BI DAX Function: NEXTWEEK Description The NEXTWEEK function is a time intelligence function in DAX that returns a table containing a single column of dates representing all the dates in the next week relative to the current filter context. It determines the "next week" based on the last (most recent) date in the current context. This makes it useful for forward-looking calculations. Syntax NEXTWEEK( [, ]) : A column reference containing dates (typically from a date table). (optional): A literal string specifying the week numbering system. Defaults to "12/31" (week containing December 31 belongs to the new year). Common options include "12/31" or "01/06" for ISO-like behavior. Example A common use case is calculating sales or metrics for the upcoming week: Next Week Sales = CALCULATE( SUM(Sales[Amount]), NEXTWEEK('Date'[Dat...

Power BI DAX Function: ISSTRING The ISSTRING function in DAX checks whether a given value is of text (string) data type. It returns TRUE if the value is text, and FALSE otherwise. Note: ISSTRING is an alias for the ISTEXT function. Both functions behave identically. Syntax ISSTRING(<value>) Parameters Parameter Description <value> The value or expression to check (can be a column reference, scalar value, etc.). Return Value BOOLEAN: TRUE if the value is text/string, FALSE otherwise (including numbers, dates, blanks, etc.). Example EVALUATE { ("Hello", ISSTRING("Hello")), // Returns TRUE (123, ISSTRING(123)), // Returns FALSE (DATE(2026,1,1), ISSTRING(DATE(2026,1,1))), // Returns FALSE (BLANK(), ISSTRING(BLANK())) // Returns FALSE } Use Case...

Power BI DAX Function: ISBOOLEAN Description The ISBOOLEAN function checks whether a given value is a Boolean (logical) value, meaning it is either TRUE or FALSE . It returns TRUE if the value is Boolean, and FALSE otherwise. Note: ISBOOLEAN is an alias for ISLOGICAL and belongs to the Information functions category in DAX. Syntax ISBOOLEAN(<value>) Parameters value : The value or expression to check. This can be a constant, column reference, or any DAX expression. Return Value A Boolean value: TRUE or FALSE . Examples ISBOOLEAN(TRUE) // Returns TRUE ISBOOLEAN(FALSE) // Returns TRUE ISBOOLEAN(1) // Returns FALSE (1 is treated as numeric, not Boolean in type checking) ISBOOLEAN("True") // Returns FALSE (text string) ISBOOLEAN(BLANK()) // Returns FALSE Another...

 Power BI DAX Function INFO.DEPENDENCIES In the world of Power BI and DAX development, keeping track of how different objects—like measures, columns, and tables—interact can become a massive headache as a model grows. The INFO.DEPENDENCIES () function is part of a suite of DAX Metadata Functions (often called DAX Info functions) that allows you to programmatically see the lineage and relationships within your data model. What is INFO.DEPENDENCIES? The INFO.DEPENDENCIES() function returns a table that describes the dependencies between different objects in your Power BI model. Essentially, it tells you, "If I change Object A, what else might break?" or "What does Object B need in order to calculate?" It scans your model and outputs a detailed list showing:     • Referencing Objects : The measure or column that uses another item.     • Referenced Objects : The table, column, or measure that is being used. The Output Structure When you run this function, you get a ...

CLOSINGBALANCEWEEK DAX Function in Power BI Description The CLOSINGBALANCEWEEK function is a time intelligence function in DAX (Data Analysis Expressions) used in Power BI. It evaluates a given expression at the last date of the current week in the context, returning the "closing balance" for that week. This function was introduced in 2025 as part of the new calendar-based time intelligence features, which provide better support for week-based calculations. Syntax CLOSINGBALANCEWEEK ( <Expression>, <Calendar> [, <Filter>] ) <Expression> : The expression to evaluate (e.g., a measure like SUM of sales or inventory balance). <Calendar> : A reference to a calendar table or column (required in the new calendar-based functions). <Filter> : Optional filter expression (rarely used). How It Works ...

Power BI DAX Function: INFO.VIEW.COLUMNS() INFO.VIEW.COLUMNS() is a DAX table function that returns a table containing metadata about all columns in the current Power BI semantic model. Description This function provides detailed information on each column, including: Table : The name of the table the column belongs to. Name : The column name. Description : Any description added to the column. Expression : The DAX formula if it's a calculated column (blank for regular columns). DataCategory : Category like "Time", "Image", etc. DataType : The data type of the column. IsHidden : Whether the column is hidden. And other metadata fields. It uses friendly names (e.g., table and column names instead of IDs) and is designed for ease of use compared to the base INFO.COLUMNS() function. Syntax INFO.VIEW.COLUMNS() No parameters. Returns a table. Restrictions Requires write permissions on the semantic model. ...

Power BI DAX Function: INFO.CSDLMETADATA INFO.CSDLMETADATA` is a specialized DAX function introduced as part of a broader set of "INFO" functions (like `INFO.TABLES` or `INFO.MEASURES`) used for **model introspection**. It allows you to programmatically access the underlying structure of your Power BI semantic model. What does it do? The function returns a table with a single column containing the Conceptual Schema Definition Language (CSDL)  metadata of your model in XML format . In plain English, it exports a highly technical "blueprint" of your entire data model—including tables, columns, relationships, and measures—as a structured XML string. It is essentially the DAX equivalent of the `DISCOVER_CSDL_METADATA` Dynamic Management View (DMV) used in Analysis Services. --- Syntax and Output The syntax is straightforward as it requires no arguments: ```dax EVALUATE INFO.CSDLMETADATA() ``` Return Value : A table with one column and one row. Content : A long string ...