What the DAX?

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...