What the DAX?

Power BI DAX: DISC Function Syntax DISC(Settlement, Maturity, Par, Redemption, [Basis]) Parameters Settlement : The date when the security is purchased. Maturity : The date when the security matures (expires). Par : The face value of the security (usually 100 or 1000). Redemption : The value received at maturity per Par value (e.g., 100). [Basis] (Optional): Day count basis (0 = US 30/360, 1 = Actual/Actual, etc.). Default is 0. What It Returns The DISC function calculates the discount rate for a security (like a Treasury bill) that is sold at a discount and does not pay periodic interest. Use Case Example Scenario : You bought a Treasury bill on January 15, 2025 , which matures on July 15, 2025 . The fac...

Power BI DAX: COUNTA Function Syntax COUNTA(<column>) Description The COUNTA function counts the number of non-blank values in a column. Unlike COUNT , which only counts numeric values, COUNTA counts all non-empty cells, including text, numbers, dates, and logical values (TRUE/FALSE). Parameters <column> : The column you want to count non-blank values from. Return Value A whole number representing the count of non-blank rows in the specified column. Use Case Example Scenario: You have a sales table with a column CustomerName . Some rows may have missing customer names. Goal: Count how many orders have a customer name recorded. DAX Measure: Orders with Customer = COUNTA(Sales[CustomerName]) Result: Returns the total number of r...

Power BI DAX: FALSE() Function A simple logical function returning the boolean value FALSE Overview The FALSE() function in DAX (Data Analysis Expressions) is a logical function that returns the boolean value FALSE . It is commonly used in calculations, conditional logic, and filter expressions where a constant boolean FALSE value is required. Syntax: FALSE() No parameters required. Return Value Returns the logical value FALSE (boolean). Use Cases 1. Conditional Logic in Calculated Columns Use FALSE() to set a default boolean value: IsActive = IF(Customer[Status] = "Active", TRUE(), FALSE()) Alternatively: IsActive = FALSE() // Default all to inactive 2. Filter Contexts in Measures Force a measure to return blank or z...

Power BI DAX: TBILLEQ Function Overview The TBILLEQ function in DAX calculates the bond-equivalent yield for a U.S. Treasury bill (T-bill) . It is commonly used in financial modeling to compare the yield of a T-bill with that of a semiannual coupon bond. Use Case: Comparing short-term Treasury bill returns with bond yields on a standardized basis. Syntax TBILLEQ(settlement, maturity, discount) Parameters Parameter Description settlement The date when the T-bill is purchased (settlement date). maturity The date when the T-bill matures. discount The discount rate of the T-bill (as a percentage, e.g., 0.05 for 5%). Return Value ...

Power BI DAX: NEXTDAY() Function Function Syntax NEXTDAY(<dates>) Description The NEXTDAY() function returns a table containing a single column of dates representing the next calendar day after the last date in the specified <dates> column or expression. Note: This is a table function , not a scalar function. It returns a table with one row. Parameters Parameter Description <dates> A column reference that contains dates (typically from a date table). Return Value A table with one column named Date and one row containing the date of the day immediately following the latest date in the input column. Example Next Working Day = VAR La...

Power BI DAX: GROUPBY Function What is GROUPBY? The GROUPBY function in DAX is used to group rows in a table by one or more columns and apply aggregations to each group. It returns a table with the grouped results. Syntax: GROUPBY ( <table>, <groupBy_columnName1> [, <groupBy_columnName2>, ...] [, <name>, <expression> ]... ) <table> : The source table to group. <groupBy_columnName> : Column(s) to group by. <name> : Name of the new aggregated column. <expression> : Aggregation (e.g., SUM, AVERAGE, COUNTROWS). How GROUPBY Works It works similarly to SQL GROUP BY or SUMMARIZE in DAX, but is more flexible when you need to: Group data without remov...

Power BI DAX: OR() Function Overview The OR() function in DAX (Data Analysis Expressions) is a logical function that evaluates two expressions and returns TRUE if either of them is true. Syntax OR(<expression1>, <expression2>) <expression1> : First logical expression to evaluate <expression2> : Second logical expression to evaluate Returns: TRUE if either expression is true; otherwise FALSE . How It Works Truth Table: Expression 1 Expression 2 OR Result TRUE TRUE TRUE ...

Power BI DAX: TRUNC Function Overview The TRUNC function in DAX (Data Analysis Expressions) truncates a number to an integer by removing the decimal part. It does not round the number — it simply chops off the fractional portion. Syntax TRUNC(number, [digits]) number (Required): The numeric value you want to truncate. digits (Optional): Number of decimal places to keep. Default is 0. Example: TRUNC(12.789) → 12 TRUNC(-8.95) → -8 (truncates toward zero) TRUNC(45.678, 2) → 45.67 Key Behavior Truncates toward zero : Positive and negative numbers are cut off at the decimal point. Not the same as rounding : Use ROUND if you need rounding. Useful for grouping or extracting integer parts without rounding bias. Use Cases ...

T.INV Function in Power BI DAX Description The T.INV function in Power BI DAX returns the inverse of the Student's t-distribution (left-tailed) for a specified probability and degrees of freedom. It is used to calculate the critical value from the t-distribution, which is commonly applied in statistical analysis, such as hypothesis testing or confidence intervals. Syntax T.INV(Probability, Degrees_freedom) Probability : A number between 0 and 1 representing the probability associated with the t-distribution. Degrees_freedom : An integer indicating the number of degrees of freedom (must be positive). Return Value The function returns the t-value (critical value) of the left-tailed inverse of the Student's t-distribution. Use Case T.INV is used in scenarios involving statistical analysis, such as: Calculating critical values for hypothesis testing (e.g., t-tests). ...

Power BI DAX BITAND Function Description The BITAND function in Power BI DAX performs a bitwise AND operation on two integer values. It compares each bit of the two numbers and returns a result where each bit is set to 1 only if both corresponding bits in the input numbers are 1. Syntax BITAND(<number1>, <number2>) number1 : The first integer value. number2 : The second integer value. Return Value : An integer representing the result of the bitwise AND operation. How It Works The BITAND function converts the input numbers to their binary representation, performs a bitwise AND operation, and returns the result as an integer. For example: BITAND(5, 3) Binary of 5: 0101 Binary of 3: 0011 Bitwise AND: 0101 & 0011 = 0001 Result: 1 (in decimal) Use Case The BITAND function is useful in scenarios involving bitmasking or f...

Power BI DAX CUSTOMDATA Function Description The CUSTOMDATA function in Power BI DAX retrieves the value of the CustomData property from the connection string used to connect to the data source. It is primarily used in scenarios where dynamic data access or user-specific filtering is required based on connection metadata. Syntax CUSTOMDATA() No parameters : The function does not accept any arguments. Return value : Returns a string containing the value of the CustomData property from the connection string, or an empty string if not defined. Use Case The CUSTOMDATA function is commonly used in role-based security or dynamic data filtering . For example, it can be used to filter data based on user-specific information passed through the connection string, such as a user ID, department, or region. Example Suppose a Power BI report is connected to a data source with a connection string that inclu...

🔢 Power BI DAX: LINEST Function 📝 What is LINEST? LINEST calculates the linear regression statistics for a set of X and Y values. It returns an array of values including slope, intercept, R-squared, and more! 💻 Syntax LINEST (known_y's, [known_x's], [const], [stats]) Parameter Description Required? known_y's Array of Y values (dependent variable) ✅ Yes known_x's Array of X values (independent variable) ❌ Optional const TRUE = force intercept=0, FALSE = calculate intercept ❌ Optional stats TRUE = full statistics, FALSE = just slope/intercept ❌ Optional 📊 What It Returns When stats = TRUE , LINEST returns a 5x2 array : Column 1 Column 2 Row 1 Slope Standard Error (Slope) Row 2 Y-Intercept Standard Error (Intercept) ...

Power BI DAX Function: CHISQ.INV.RT What is CHISQ.INV.RT? The CHISQ.INV.RT function in DAX (Data Analysis Expressions) is a statistical function used in Power BI, Power Pivot, and Analysis Services. It calculates the inverse of the right-tailed chi-squared probability distribution . In simple terms: Given a probability (between 0 and 1) and degrees of freedom , it returns the critical value (x) such that the area under the chi-squared distribution curve to the right of x equals the given probability. This is the inverse of CHISQ.DIST.RT : If CHISQ.DIST.RT(x, deg_freedom) = probability , then CHISQ.INV.RT(probability, deg_freedom) = x . Syntax CHISQ.INV.RT(probability, deg_freedom) Parameters Parameter Description Data Type probability The right-tailed probability (0 Decimal Number deg_freedom Degrees of freedom (mu...

Power BI DAX Function: INFO.OBJECTTRANSLATIONS What is INFO.OBJECTTRANSLATIONS? This DAX function is part of the INFO category in Power BI, designed to retrieve metadata about your semantic model (like tables, columns, and translations). Specifically, INFO.OBJECTTRANSLATIONS returns a table listing all object translations defined in the current model. These translations allow you to localize names and descriptions of model objects (e.g., tables, columns, measures) into different languages, making reports accessible to multilingual users. Syntax INFO.OBJECTTRANSLATIONS ( [ [, [ ] [, [, [ ] [, … ] ] ] ] ] ) Return Type: Table Parameters: Optional restriction pairs (name-value) to filter results, e.g., by object ID or language. Limitations: Cannot be used in calculated tables or columns. Best for measures or queries in tools like DAX Studio. The returned table matches the schema of the TMSCHEMA_OBJEC...

📐 SIN Function in Power BI DAX 🔍 What is SIN? SIN(angle) calculates the sine of an angle (in radians). SIN(0) = 0 | SIN(π/2) = 1 | SIN(π) = 0 | SIN(3π/2) = -1 📝 Syntax SIN(<angle>) • <angle> = Number (in radians ) ⚙️ How to Use Example Formula Result What it does 45° SIN(RADIANS(45)) 0.707 Sine of 45° 90° SIN(RADIANS(90)) 1 Sine of 90° Column Data SIN(RADIANS([Angle])) Calculated Apply to each row 🎯 Real Use Cases Use Case Formula Example Business Value 📏 Distance Calc SIN(RADIANS([LatDiff])) Calculate distances between locations 📈 Wave Patterns SIN(RADIANS([Time])) Model seasonal sales trends 🔧 Engineering SIN(RADIANS([Angle])) * [Force] Calculate force components ...

📊 Power BI DAX INDEX() Function Syntax INDEX( table , row_position [, column ]) What It Does INDEX() returns a single value from a specific position in a table. Row Position: Which row (1 = first row) Column: Optional - which column name Parameters Table Parameter Type Required? Description table Table Yes Your data table row_position Number Yes Row number (1, 2, 3...) column Column Name No Specific column (defaults to first column) Simple Examples 1. Get 1st Row, 1st Column INDEX(Sales, 1) Returns: First sale amount 2. Get 3rd Row, "Product" Column INDEX(Sales, 3, "Product") Returns: Product name from row 3 Top 3 Use Cases ...

🔢Power BI DAX: ROUND Function Syntax ROUND(<number>, <num_digits>) What it does: ROUND rounds a number to a specified number of decimal places. Parameter Description Example <number> The number to round 12.346 <num_digits> Decimal places (0 = whole number) 2 Examples: ROUND(12.346, 2) → 12.35 ROUND(12.346, 0) → 12 ROUND(12.346, 1) → 12.3 🎯 Common Use Cases Financial Reports: Round currency to 2 decimals Total Sales = ROUND(SUM(Sales[Amount]), 2) KPIs:...

BETA.DIST DAX Function The BETA.DIST function in Power BI DAX (Data Analysis Expressions) calculates the probability density or cumulative distribution for the beta distribution, which is commonly used in statistical modeling for proportions or probabilities. Syntax BETA.DIST(value, alpha, beta, cumulative, [A], [B]) Parameters Parameter Description value The value at which to evaluate the distribution (between A and B). alpha A parameter of the beta distribution (must be positive). beta A parameter of the beta distribution (must be positive). cumulative A logical value: TRUE for cumulative distribution, FALSE for probability density. A (optional) The lower bound of the distribution (default is 0). ...

TOPNSKIP DAX Function in Power BI The TOPNSKIP DAX function retrieves a specified number of rows from a table, skipping a defined number of rows, based on a sort order. It’s useful for paginated reports or scenarios requiring a subset of ranked data. Syntax TOPNSKIP(n_value, skip_value, table, orderBy_expression [, order [, ...]]) n_value : Number of rows to return. skip_value : Number of rows to skip. table : The table to query. orderBy_expression : Column or expression to sort by. order : (Optional) ASC or DESC (default is ASC). Use Case TOPNSKIP is ideal for scenarios like: Paginated Reports : Display a fixed number of rows per page (e.g., top 10 products, skipping the first 20 for page 3). Top Performers : Retrieve top N customers by sales, skipping initial rows for ranking analysis. Data Sampling : Extract a subset of sorted data for performance or visualizat...

DAX MAXX Function The MAXX function in Power BI's Data Analysis Expressions (DAX) evaluates an expression for each row in a table and returns the maximum value of that expression. Syntax MAXX(<table>, <expression>) <table> : The table to iterate over. <expression> : The expression to evaluate for each row, returning a scalar value. Return Value The maximum value of the expression across all rows in the specified table. Use Case MAXX is useful when you need to find the maximum value of a calculated expression across rows in a table, such as finding the highest sales amount after applying a discount or the latest date in a dataset. Example Suppose you have a table Sales with columns Product , Quantity , and UnitPrice . You want to find the maximum revenue (Quantity * UnitPrice) for any product. MaxRevenue = MAXX(Sales, Sales[Quantity] * Sales[UnitPrice]) This measure ...

Power BI DAX Function: CURRENTGROUP Description The CURRENTGROUP function in DAX (Data Analysis Expressions) is used within the context of a calculation to reference the current group of rows in a grouped calculation, such as in a SUMMARIZE or ADDCOLUMNS function. It is primarily used in Power BI to access the rows in the current group during iterative calculations over grouped data. Syntax CURRENTGROUP() Returns: A table containing the rows of the current group in the grouping operation. Use Case CURRENTGROUP is commonly used with the SUMMARIZE function to perform calculations on a group of rows. It allows you to reference the subset of data in the current group for further calculations, such as aggregations or filtering within the group. Example Suppose you have a sales table with columns Region , Product , and SalesAmount . You want to calculate the total sales for each region and then find the percentage co...

Power BI DAX RADIANS Function Description The RADIANS function in Power BI DAX converts an angle from degrees to radians. Many trigonometric calculations, such as those involving SIN , COS , or TAN , require angles in radians rather than degrees. Syntax RADIANS(number) number : The angle in degrees (required). Return Value The angle converted to radians (numeric value). Use Case The RADIANS function is useful in scenarios involving trigonometric calculations, such as: Calculating distances between geographic coordinates using the Haversine formula. Performing calculations in engineering or physics dashboards that involve angles. Creating visualizations that require trigonometric transformations. Example Suppose you have a column Degrees with an angle value of 180. You want to convert it to radians for use in a SIN calculation. SineOfAngle = SIN(RADIANS(180)) R...

Power BI DAX: OPENINGBALANCEYEAR Function Description The OPENINGBALANCEYEAR function in Power BI DAX (Data Analysis Expressions) calculates the balance of a specified measure or column at the start of a given year . It is commonly used in financial and time-intelligence analysis to retrieve the opening balance for a year based on a date column and a measure or expression. Syntax OPENINGBALANCEYEAR(<expression>, <dates>[, <filter>][, <year_end_date>]) expression : The measure or column to evaluate (e.g., sum of sales or account balance). dates : A column containing date values, typically from a Date table. filter (optional): A filter expression to apply to the calculation. year_end_date (optional): A literal string defining the year-end date (e.g., "12/31" for December 31). If omitted, it assumes December 31. Return Value The value of the expression calculated...

DAX INFO.CHANGEDPROPERTIES Function Description The INFO.CHANGEDPROPERTIES function in Power BI DAX (Data Analysis Expressions) returns a table containing information about properties that have changed in the current session or calculation context. It is primarily used for debugging and understanding how calculations or data model changes impact results. Syntax INFO.CHANGEDPROPERTIES() Returns: A table with columns describing changed properties, such as property names, old values, and new values. Columns in the Result Table PropertyName : The name of the changed property. ObjectName : The name of the object (e.g., table, column) affected by the change. OldValue : The previous value of the property. NewValue : The new value of the property. Use Case INFO.CHANGEDPROPERTIES is useful in scenarios where you need to track or debug changes in the data model or calculation context,...

Power BI DAX TREATAS Function Description The TREATAS function in Power BI DAX (Data Analysis Expressions) is used to apply the values from one table as filters to another table, treating the values as if they belong to the columns of the target table. It is particularly useful for creating virtual relationships between tables without a physical relationship in the data model. Syntax TREATAS(table_expression, column1 [, column2, ...]) table_expression : A table or expression that returns a table containing the values to be used as filters. column1, column2, ... : The columns in the target table to which the values from the table_expression are applied. Use Case TREATAS is commonly used when you need to filter a table based on values from another table without an explicit relationship. This is helpful in scenarios like: Filtering data dynamically based on a list of values from an unrelated table...

Power BI DAX ACOT Function Description The ACOT function in Power BI DAX (Data Analysis Expressions) calculates the arc-cotangent (inverse cotangent) of a given number. It returns the angle (in radians) whose cotangent is the specified value. Syntax ACOT(number) number : A numeric value (real number) for which to calculate the arc-cotangent. Return Value Returns the arc-cotangent of the input number in radians , ranging from 0 to π (approximately 3.14159). Use Case The ACOT function is used in trigonometric calculations, particularly in scenarios involving angles and slopes. Common applications include: Engineering and Physics : Calculating angles in mechanical or structural designs where slopes or ratios are provided. Data Analysis : Analyzing geometric or spatial data, such as in geographic information systems (GIS). Financial Modeling : Used in niche cases involving ...

Power BI DAX OFFSET Function Description The OFFSET function in DAX (Data Analysis Expressions) is used to return a reference to a row that is a specified number of rows before or after the current row in a table, based on a defined sort order. It is particularly useful for comparing values across rows, such as calculating differences between consecutive periods or rows. Syntax OFFSET( <offset>, <relation>, <orderBy> [, <blanks>] [, <partitionBy>] ) <offset> : An integer specifying the number of rows to move (positive for rows after, negative for rows before). <relation> : The table or table expression to evaluate. <orderBy> : Column(s) to define the sort order. <blanks> : (Optional) Specifies how to handle blank values (e.g., DEFAULT , BLANK_AS_NULL ). <partitionBy> : (Optional) Columns to partition the table, resetti...

Power BI DAX STDEV.P Function Description The STDEV.P function in Power BI DAX (Data Analysis Expressions) calculates the standard deviation of a population based on a numeric column. It measures how much the values in a dataset deviate from the mean (average) of the population. Syntax STDEV.P(<ColumnName>) ColumnName : The column containing the numeric values for which you want to calculate the standard deviation. Return Value A single numeric value representing the population standard deviation. Key Points STDEV.P assumes the data represents the entire population . For sample data, use STDEV.S instead. It ignores non-numeric values and blanks in the column. The formula used is: √(Σ(x - μ)² / N) , where: x : Each value in the dataset μ : Population mean N : Number of values in the population ...

Power BI DAX CROSSJOIN Function Description The CROSSJOIN function in Power BI DAX (Data Analysis Expressions) creates a new table that contains all possible combinations of rows from two or more input tables. It performs a Cartesian product, meaning every row from one table is paired with every row from the other table(s). Syntax CROSSJOIN(<table1>, <table2> [, <table3>, ...]) <table1>, <table2>, ... : The tables or table expressions to combine. Return Value A table containing all possible combinations of rows from the input tables, with columns from each table included in the result. Use Case CROSSJOIN is useful when you need to analyze combinations of data from multiple tables, such as generating scenarios, creating matrices, or preparing data for further calculations. For example, it can be used to pair every product with every sales region to analyze potential sales o...

Power BI DAX: KEEPFILTERS Function The KEEPFILTERS function in Power BI DAX (Data Analysis Expressions) is used to modify how filters are applied in a calculation, ensuring that existing filters are preserved and combined with any new filters specified in the expression. Syntax KEEPFILTERS(<expression>) <expression> : The DAX expression to evaluate, typically a calculation like CALCULATE . Purpose KEEPFILTERS ensures that the existing filter context (from slicers, visuals, or other filters) is not overridden but is instead combined with any new filters applied within a CALCULATE function. Without KEEPFILTERS , CALCULATE may replace the existing filter context entirely. How It Works By default, CALCULATE overwrites the filter context for the columns specified in its filter arguments. KEEPFILTERS modifies this behavior by applying new filters as an intersection (AND) with the existing filter ...

Power BI DAX EVEN Function Description The EVEN function in DAX (Data Analysis Expressions) rounds a number up to the nearest even integer. If the number is already an even integer, it remains unchanged. This function is useful for scenarios requiring even numbers, such as inventory management or scheduling. Syntax EVEN(number) number : The value to round (required). Can be a number, column reference, or expression that evaluates to a number. Return Value An even integer, rounded up if necessary. Use Case The EVEN function is often used in business scenarios where quantities must be even, such as packaging items in pairs or ensuring even distribution in reports. Example: A company sells products in pairs (e.g., shoes, gloves). If an order has an odd quantity, the EVEN function can round up to ensure pairs are maintained. Example Suppose you have a table with a column Quantity conta...