What the DAX?

Power BI DAX: OPENINGBALANCEMONTH Function Description The OPENINGBALANCEMONTH function in Power BI DAX returns the balance of a specified measure at the beginning of a given month, based on a date column and a filter context. Syntax OPENINGBALANCEMONTH(<expression>, <dates>[, <filter>]) expression : The measure or calculation to evaluate (e.g., sum of sales). dates : A column containing date values, typically from a Date table. filter (optional): A filter expression to apply to the calculation. Use Case OPENINGBALANCEMONTH is used in financial reporting, inventory tracking, or any scenario requiring the starting balance of a measure at the beginning of a month. For example, it can calculate the opening balance of a bank account, inventory stock, or sales totals for a month. Example Suppose you have a table Sales with columns Date and Amount , and a related DateTable ...

Power BI DAX: INFO.CULTURES Function Description The INFO.CULTURES function in Power BI DAX returns a table containing information about the cultures (language and regional settings) supported by the DAX engine. Each row in the resulting table represents a culture with details such as culture name, display name, and associated formats for dates, numbers, and currencies. Syntax INFO.CULTURES() Parameters: None. The function takes no arguments. Return Value A table with the following columns: Name : The culture code (e.g., "en-US" for English - United States). DisplayName : The human-readable name of the culture (e.g., "English (United States)"). DateTimeFormat : The format used for dates and times in the culture. NumberFormat : The format used for numbers, including decimal and thousand separators. CurrencyFormat : The format used for currency values. ...

Power BI DAX ENDOFQUARTER Function Description The ENDOFQUARTER function in Power BI's Data Analysis Expressions (DAX) returns the last date of the quarter for a given date. It is a time intelligence function that helps in analyzing data based on quarterly periods. Syntax ENDOFQUARTER(<dates>) <dates> : A column containing date values or an expression that returns a single date. Return Value A date representing the last day of the quarter for the input date(s). Use Case The ENDOFQUARTER function is used in scenarios where you need to aggregate or filter data up to the end of a quarter, such as: Calculating total sales or revenue for a quarter. Comparing performance metrics at the end of each quarter. Creating time-based calculations, like quarter-end inventory levels. Example Suppose you have a table named Sales with a Date column. You w...

SUMMARIZECOLUMNS Function in Power BI DAX The SUMMARIZECOLUMNS function in Power BI DAX is used to create a summary table by grouping data based on specified columns and applying aggregations. It is optimized for query performance and is commonly used in complex calculations within Power BI reports. Syntax SUMMARIZECOLUMNS( <groupBy_columnName> [, <groupBy_columnName> ...], [<filterTable>] [, <filterTable> ...], <expression> [, <expression> ...] ) Parameters groupBy_columnName : Columns to group the data by. filterTable : (Optional) Tables or expressions to apply filters. expression : Aggregations or calculations (e.g., SUM, COUNT) to include in the result. Key Features Groups data by specified columns. Applies filters and aggregations in a single query. Optimized for performance in large datasets. Does not suppor...

DAX SELECTEDVALUE Function Description The SELECTEDVALUE function in Power BI DAX (Data Analysis Expressions) returns the value of a column when the context has been filtered down to a single distinct value. If no single value is selected or multiple values are present, it can return a default value or a blank. Syntax SELECTEDVALUE(<ColumnName> [, <AlternateResult>]) ColumnName : The column from which to retrieve the selected value. AlternateResult (optional): The value to return if no single distinct value is found. If not specified, returns BLANK(). Return Value The single selected value from the specified column, or the alternate result (if provided) when no single value exists. Use Cases Dynamic Measures : Use in measures to return a value based on a slicer or filter selection. Cond...

Power BI DAX: CONTAINSROW Function Description The CONTAINSROW function in DAX (Data Analysis Expressions) checks if a specific row of values exists in a table. It returns TRUE if the row is found, otherwise FALSE . Syntax CONTAINSROW(table, value1, [value2], ...) table : The table to search in (e.g., a table expression or table name). value1, [value2], ... : The values to search for, corresponding to the columns in the table. Return Value TRUE if the row with the specified values exists in the table, FALSE otherwise. Use Cases CONTAINSROW is commonly used to: Check if specific data combinations exist in a table. Filter data based on multiple column conditions. Validate data integrity or perform conditional logic in measures or calculated columns. Example Suppose you have a table named Sales with columns Product and Region . You want...

Power BI DAX IGNORE Function The IGNORE function in Power BI DAX (Data Analysis Expressions) is used to treat specific filters as if they do not exist in the filter context for a calculation. It is primarily used within the CALCULATE function to exclude specified filters while keeping others intact. Syntax IGNORE( <expression>, <column> [, <column> [, … ] ] ) expression : The DAX expression to evaluate (typically used within CALCULATE ). column : One or more columns whose filters should be ignored in the filter context. Purpose The IGNORE function allows you to bypass specific filters applied to a column in the current filter context, while preserving other filters. This is useful when you want to perform calculations without the influence of certain filters, such as slicers or row contexts, on specific columns. Use Case Suppose you have a sales dataset with columns for Region , Product , and Sa...

COUPDAYS DAX Function Description The COUPDAYS function in Power BI DAX calculates the number of days in the coupon period that contains the settlement date for a security with periodic interest payments. Syntax COUPDAYS(settlement, maturity, frequency, [basis]) Parameters settlement : The security's settlement date (date of purchase). maturity : The security's maturity date (when it expires). frequency : Number of coupon payments per year (1 = annual, 2 = semi-annual, 4 = quarterly). basis (optional): Day count basis (e.g., 0 = US 30/360, 1 = actual/actual). Defaults to 0 if omitted. Return Value A number representing the total days in the coupon period that includes the settlement date. Use Case The COUPDAYS function is used in financial analysis to determine the length of the coupon period for bonds or other fixed-income securities. This helps calculate ...

USERELATIONSHIP DAX Function Description The USERELATIONSHIP function in Power BI DAX (Data Analysis Expressions) is used to specify which relationship to use between two tables when multiple relationships exist. It activates an inactive relationship for a specific calculation, overriding the default active relationship. Syntax USERELATIONSHIP(<column1>, <column2>) <column1> : The column in the first table of the relationship. <column2> : The column in the second table of the relationship. Use Case USERELATIONSHIP is useful when a data model has multiple relationships between two tables, but only one can be active at a time. For example, in a sales model, you might have a Date table related to a Sales table by both Order Date (active) and Ship Date (inactive). Use USERELATIONSHIP to calculate metrics based on the inactive Ship Date relationship. Example Suppose you ...

Power BI DAX DAY Function Description The DAY function in DAX (Data Analysis Expressions) returns the day of the month (an integer from 1 to 31) from a specified date. Syntax DAY(<date>) Parameter: <date> : A date in datetime format, or a reference to a column containing dates. Return Value: An integer between 1 and 31 representing the day of the month. Use Case The DAY function is used to extract the day component from a date for analysis, filtering, or calculations in Power BI reports. Common scenarios include: Analyzing trends based on specific days of the month (e.g., sales on the 1st vs. the 15th). Creating custom date-based calculations, such as grouping data by day. Filtering reports to focus on specific days (e.g., transactions occurring on the last day of the month). Example Suppose you have a table named Sales with a column OrderDate . ...

Power BI DAX BLANK Function Description The BLANK function in DAX (Data Analysis Expressions) returns a blank value. A blank in DAX represents a null or empty value, often used to handle missing or undefined data in calculations or visuals in Power BI. Syntax BLANK() Parameters: None. The function takes no arguments. Return Value A blank value (null or empty). Use Cases Handling Missing Data: Use BLANK to represent missing or undefined values in calculations, ensuring consistent behavior in measures or calculated columns. Conditional Logic: Combine with functions like IF or ISBLANK to manage scenarios where data is absent. Data Visualization: Prevent errors in visuals by returning BLANK instead of invalid results (e.g., divide-by-zero errors). Default Values: Use in measures to provide a default blank value when conditions are not met. Example Suppose you w...

Power BI DAX BITOR Function Description The BITOR function in Power BI DAX performs a bitwise OR operation on two integers. It compares each bit of the first number with the corresponding bit of the second number. If either bit is 1, the result for that bit is 1; otherwise, it is 0. Syntax BITOR(number1, number2) number1 : First integer for the bitwise OR operation. number2 : Second integer for the bitwise OR operation. Return Value An integer representing the result of the bitwise OR operation. Use Case The BITOR function is useful in scenarios involving bit manipulation , such as managing permissions, flags, or binary states in data models. For example, it can combine permission flags where each bit represents a specific permission. Example Suppose you have two permission flags: Read Permission (binary: 001, decimal: 1) Write Permission (binary: 010, decima...

FIRSTNONBLANKVALUE DAX Function Description The FIRSTNONBLANKVALUE function in Power BI DAX returns the first non-blank value of a specified column or expression, evaluated in the context of a sorted column. Syntax FIRSTNONBLANKVALUE(<column>, <expression>) <column> : The column used to determine the sort order for finding the first non-blank value. <expression> : The expression or column to evaluate for the non-blank value. Use Case FIRSTNONBLANKVALUE is useful in scenarios where you need to retrieve the first valid (non-blank) value in a dataset, such as: Finding the first recorded sales amount for a customer in a sorted date order. Retrieving the first non-blank status or category in a time series. Analyzing time-based data where the earliest non-blank entry is needed. Example Suppose you have a table Sales with columns Date , Custom...

Power BI DAX COUPDAYSNC Function Description The COUPDAYSNC function in Power BI DAX calculates the number of days from the settlement date to the next coupon date for a security with periodic interest payments (e.g., a bond). Syntax COUPDAYSNC(settlement, maturity, frequency, [basis]) Parameters settlement : The date when the security is purchased (must be a valid date). maturity : The date when the security expires (must be after the settlement date). frequency : The number of coupon payments per year: 1 = Annual 2 = Semi-annual 4 = Quarterly basis (optional): The day count convention to use (defaults to 0 if omitted): 0 = US (NASD) 30/360 1 = Actual/actual 2 = Actual/360 3 = Actual/365 4 = European 30/360 ...

CROSSFILTER DAX Function Definition The CROSSFILTER function in Power BI DAX (Data Analysis Expressions) is used to modify the filter direction between two tables in a data model, overriding the default relationship behavior for a specific calculation. Syntax CROSSFILTER(<columnName1>, <columnName2>, <direction>) columnName1 : The column from the first table in the relationship. columnName2 : The column from the second table in the relationship. direction : The filter direction, which can be: Both : Enables bidirectional filtering. OneWay : Filters in the direction of the relationship (default). None : Disables filtering between the tables. Purpose The CROSSFILTER function allows you to control how filters propagate between related tables in a calculation, overriding the default relationship behav...

PERCENTILE.EXC DAX Function Description The PERCENTILE.EXC function in Power BI DAX calculates the k-th percentile of a column of values, excluding the 0th and 100th percentiles. It returns the value at a specified percentile rank, useful for statistical analysis. Syntax PERCENTILE.EXC(<column>, <k>) <column> : A column containing numeric values. <k> : A number between 0 and 1 (exclusive), representing the percentile rank (e.g., 0.9 for 90th percentile). Use Case PERCENTILE.EXC is used to identify thresholds or benchmarks in datasets, such as determining the value below which a certain percentage of data falls. Common scenarios include: Finding the 90th percentile of sales to identify top-performing products. Analyzing test scores to determine high or low performance thresholds. Setting performance benchmarks, e.g., salary ranges or response times....

Power BI DAX CURRENCY Function Description The CURRENCY function in DAX (Data Analysis Expressions) converts a given value into the currency data type. It ensures that numerical values are treated as currency, which is useful for financial calculations and formatting in Power BI reports. Syntax CURRENCY(<value>) value : The value or expression to convert to the currency data type. It can be a number, column, or expression that evaluates to a number. Return Value A value in the currency data type, which has a fixed precision of four decimal places. Use Case The CURRENCY function is used in scenarios where precise currency calculations are needed, such as in financial reports, budgeting, or sales analysis. It ensures consistent formatting and accuracy in calculations involving monetary values. Example Suppose you have a column Sales[Amount] with numerical values representing sales a...

Power BI DAX ISINSCOPE Function Description The ISINSCOPE function in DAX (Data Analysis Expressions) is used in Power BI to determine whether a specified column is currently in scope within a report's filter context, typically in a matrix or table visual with hierarchical levels. It returns TRUE if the column is in scope, and FALSE otherwise. Syntax ISINSCOPE(<column_name>) <column_name> : The name of the column to check if it is in the current filter scope. Return Value TRUE or FALSE (Boolean). Use Case ISINSCOPE is commonly used in hierarchical visuals (e.g., matrix or table) to create dynamic calculations that behave differently based on the level of hierarchy being displayed. It is particularly useful for customizing measures at different levels of a hierarchy, such as subtotals or grand totals. Example Suppose you have a sales table with a hierarchy of Year , Qu...

Power BI DAX STARTOFMONTH Function Description The STARTOFMONTH function in DAX (Data Analysis Expressions) returns the first date of the month for a given date or date column. It is commonly used in Power BI for time intelligence calculations, such as aggregating data at the start of a month or comparing month-to-date metrics. Syntax STARTOFMONTH(<dates>) <dates> : A column reference containing dates, or a function/expression that returns a date. Return Value A single date representing the first day of the month for the input date, in datetime format (e.g., 2025-08-01 00:00:00 for any date in August 2025). Use Case STARTOFMONTH is useful for: Calculating month-to-date metrics (e.g., sales from the start of the month). Grouping or filtering data by the first day of each month. Creating time-based comparisons, such as comparing current month performance to pre...

Power BI DAX ATAN Function The ATAN function in Power BI DAX (Data Analysis Expressions) calculates the arctangent (inverse tangent) of a given number, returning the angle (in radians) whose tangent is the specified value. Syntax ATAN(number) number : The value (a real number) for which you want the arctangent. Return Value A value in radians between -π/2 and π/2 (approximately -1.5708 to 1.5708). Use Case The ATAN function is commonly used in scenarios involving trigonometry, such as calculating angles in geometric or spatial data analysis. For example: Geospatial Analysis : Calculate the angle between two points on a map using the arctangent of the ratio of differences in coordinates (e.g., for navigation or orientation). Engineering : Determine angles in mechanical or structural designs based on ratios of measurements. Data Visualization : Compute angles for custom visuals, such as polar c...

Power BI DAX INFO.RELATIONSHIPS Function Description The INFO.RELATIONSHIPS function in Power BI DAX returns a table describing the relationships in the current data model. It provides metadata about the relationships, including the tables and columns involved, the relationship's direction, and its active status. Syntax INFO.RELATIONSHIPS() Parameters: None. This function takes no arguments. Return Value A table with the following columns: FromTable : Name of the table on the "many" side of the relationship. FromColumn : Name of the column in the "many" side table. ToTable : Name of the table on the "one" side of the relationship. ToColumn : Name of the column in the "one" side table. IsActive : Boolean indicating if the relationship is active (TRUE) or inactive (FALSE). RelationshipType : Type of relationship (e.g., "OneToM...

NORM.S.INV DAX Function Description The NORM.S.INV function in Power BI DAX (Data Analysis Expressions) returns the inverse of the standard normal cumulative distribution for a specified probability. It calculates the z-score (standard normal deviate) corresponding to a given probability in a standard normal distribution (mean = 0, standard deviation = 1). Syntax NORM.S.INV(probability) probability : A number between 0 and 1 (exclusive), representing the probability associated with the standard normal distribution. Return Value A real number representing the z-score for the given probability. Use Case NORM.S.INV is used in statistical analysis to find critical values or thresholds for a standard normal distribution. Common scenarios include: Confidence Intervals : Calculate z-scores for confidence levels (e.g., 95% confidence corresponds to a z-score of approximately 1.96). Hypot...

Power BI DAX USERNAME Function Description The USERNAME function in Power BI DAX returns the username of the current user accessing the report or dashboard. It retrieves the User Principal Name (UPN), typically in the format of an email address (e.g., user@company.com ), based on the user's Power BI login credentials. Syntax USERNAME() Returns: A text string representing the current user's UPN. Use Case The USERNAME function is primarily used to implement row-level security (RLS) in Power BI, enabling personalized data filtering based on the logged-in user. It allows reports to dynamically display data relevant to the current user without requiring manual filtering. Example Scenario Suppose you have a sales dataset with a table named SalesData that includes a column UserEmail indicating which employee is responsible for each sale. You want to ensure that each employee only sees their own sales data in a ...

Power BI DAX TODAY Function Description The TODAY function in Power BI DAX (Data Analysis Expressions) returns the current date based on the system date of the computer running the Power BI Desktop or Power BI Service. It takes no arguments and is commonly used for date-based calculations and dynamic reporting. Syntax TODAY() Returns: A date value representing the current date. Use Case The TODAY function is used to create dynamic calculations or filters that depend on the current date, such as: Calculating the difference between the current date and another date (e.g., aging of invoices). Filtering data to show records up to the current date. Creating dynamic reports that update automatically based on the current date. Example Suppose you have a sales table with a column OrderDate . You can calculate the number of days since each order was placed using: DaysSinceOrder = DATEDIF...

Power BI DAX INFO.CALCDEPENDENCY Function Overview The INFO.CALCDEPENDENCY function in Power BI's Data Analysis Expressions (DAX) is used to retrieve metadata about the dependencies of calculations within a Power BI semantic model. It returns a table that details the objects (such as tables, columns, or measures) required to execute a specific DAX query or all calculations in the model. This function is particularly useful for developers and BI professionals who need to analyze or document the structure and dependencies of their data model. Syntax INFO.CALCDEPENDENCY([<Restriction name>, <Restriction value>], ...) Parameters : Optional pairs of restriction names and values, specified as text in double quotes (e.g., "Query", "EVALUATE { [Orders] }" ). Return Value : A table containing details about calculation dependencies, including object names, types, and expressions. Key Cha...

ALLNOBLANKROW DAX Function Purpose Removes filters from a table or column in Power BI DAX, excluding blank rows. Syntax ALLNOBLANKROW( <Table> | <Column> [, <Column> ...] ) Key Points Removes all filters from specified table/column(s). Excludes blank rows, unlike ALL . Returns all rows or unique values, ignoring filters. Use Case Calculate percentages (e.g., sales by category) while ignoring blank rows in data. Example Data: ProductID SalesAmount 1 100 2 200 (blank) 50 DAX Measure: Percentage of Total Sales = DIVIDE( SUM(Sales[SalesAmount]), CALCULATE( SUM(Sales[SalesAmount]), ALLNOBLANKROW(Sales) ), 0 ) Result: Electronics: 33.33% (100 / 300) Clothing: 66.67% (200 / 300) Blank row ($50) excluded. When to Use Percentage calcu...

Power BI DAX ASIN Function Description The ASIN function in DAX (Data Analysis Expressions) returns the arcsine (inverse sine) of a number. The result is an angle, in radians, between -π/2 and π/2. Syntax ASIN(number) number : A value between -1 and 1, representing the sine of an angle. Return Value An angle in radians, ranging from -π/2 to π/2. If the input is outside [-1, 1], the function returns an error. Use Case The ASIN function is used in scenarios requiring trigonometric calculations, such as: Geometric Analysis : Calculate angles in datasets involving spatial or physical measurements (e.g., engineering or physics). Data Transformations : Convert sine values to

Understanding the ALLSELECTED DAX Function in Power BI The ALLSELECTED DAX function in Power BI removes filters from a table or column while keeping filters applied by slicers or visuals in a report. It’s ideal for calculations based on user-selected data. Syntax ALLSELECTED([table | column[, column[, ...]]]) table : Table to remove filters from. column : Specific column(s) to remove filters from. How It Works ALLSELECTED removes filters from the query context but retains user-driven filters (e.g., slicers). Unlike ALL , which ignores all filters, ALLSELECTED respects user selections, making it dynamic. Common Use Cases Percentage of Total : Calculate the percentage of sales for selected data. Sales % = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales)) ) Ranking : Rank items within user-selected filters. Rank = RANKX( ALLSELECTED(Sales[Product]), CALCULATE(SUM(Sales[Amount])) ) Dynamic ...

Understanding the DAX COUNTX Function in Power BI The COUNTX function in Power BI's DAX (Data Analysis Expressions) language is used to count rows in a table where a specified expression evaluates to a non-blank value. It's ideal for custom calculations and conditional counting. Syntax COUNTX(<table>, <expression>) <table> : The table or table expression to iterate over. <expression> : The expression evaluated for each row; counts non-blank results. Use Cases Conditional Counting : Count rows meeting specific criteria, like sales above a threshold. Dynamic Calculations : Count based on calculated values (e.g., profit = Revenue - Cost). Filtered Counts : Combine with FILTER for complex conditions. Example 1: Counting High-Value Sales Count sales transactions where the total value (Quantity * UnitPrice) exceeds $100. HighValueSales = COUNTX(Sales, Sales[Quantity] * Sales[UnitPrice] > 100) ...

Explanation of the Power BI DAX Function INFO.VARIATIONS The INFO.VARIATIONS DAX function is part of a category of DAX functions designed for analyzing a Power BI data model itself, rather than for performing typical business calculations. These functions are often referred to as "info functions" or "DMV functions" because they are based on Dynamic Management Views (DMVs) from Analysis Services. What it Does INFO.VARIATIONS returns a table that provides metadata about "variations" in the current data model. In the context of DAX and tabular models, a "variation" refers to a column that has different representations or roles. For example, a column might be used as a key for a relationship, a sort-by column, or a column in a hierarchy. The returned table includes a variety of columns with information about these variations, such as: ID: The ID of the variation object. ColumnID: The ID of the column the variation is based on. ...

Understanding the Power BI DAX Function `ISATLEVEL` The ISATLEVEL function in Power BI's DAX (Data Analysis Expressions) language is a logical function that returns TRUE if the current row in a table is at a specified level in a hierarchy, and FALSE otherwise. It's a powerful tool for controlling the behavior of calculations at different levels of aggregation. Syntax ISATLEVEL( [ <hierarchy> ] , [ <level> ] ) <hierarchy> : The name of the hierarchy you are testing against. This is typically a column from a table that is part of a hierarchy. <level> : The name of the level in the hierarchy you want to test. This is also typically a column from a table. How it Works Imagine you have a hierarchy like "Year > Quarter > Month." When you are at the "Year" level, ISATLEVEL([Date].[Year]) will be TRUE . When you are at the "Quarter" level, ISATLEVEL([Date].[Quarter]) will be TRUE . ...