What the DAX?

DAX DISTINCTCOUNT Function Description The DISTINCTCOUNT function in Power BI DAX (Data Analysis Expressions) counts the number of unique values in a column. It ignores duplicates and returns the count of distinct values, including blanks (if present). Syntax DISTINCTCOUNT(<column>) <column> : The column containing the values to count distinct entries for. Return Value An integer representing the count of unique values in the specified column. Use Case DISTINCTCOUNT is commonly used in business intelligence scenarios to analyze unique occurrences, such as: Counting unique customers who made purchases. Calculating the number of distinct products sold in a period. Determining unique website visitors in web analytics. Example Suppose you have a table named Sales with a column CustomerID . To count unique customers: UniqueCustomers = DISTINCT...

Power BI DAX ACOS Function Description The ACOS function in Power BI DAX (Data Analysis Expressions) calculates the arccosine (inverse cosine) of a given number. It returns the angle (in radians) whose cosine is the specified value. Syntax ACOS(number) number : A numeric value between -1 and 1, representing the cosine of an angle. Return Value The angle in radians between 0 and π (approximately 3.14159) whose cosine is the input number. If the input is outside the range [-1, 1], the function returns an error. Use Case The ACOS function is commonly used in scenarios involving geometric or trigonometric calculations, such as: Calculating angles in spatial data analysis (e.g., in geographic or engineering datasets). Analyzing relationships between variables in scientific or statistical models. Converting cosine values to angles for visualization or further computation in ...

Power BI DAX SAMPLE Function Description The SAMPLE function in Power BI DAX (Data Analysis Expressions) is used to retrieve a random sample of rows from a table. It is useful for statistical analysis, testing, or scenarios where you need a subset of data without bias. Syntax SAMPLE(<NumberOfRows>, <Table>, <OrderByExpression> [, <Order>] [, <OrderByExpression> [, <Order>]] ...) NumberOfRows : The number of rows to return (integer). Table : The table to sample from. OrderByExpression : (Optional) A column or expression to sort the table before sampling. Order : (Optional) ASC (ascending) or DESC (descending) for sorting. Default is ASC. Return Value A table containing a random subset of rows from the input table, with the specified number of rows. Use Case The SAMPLE function is commonly used in: Statistical Analysis : To anal...

CHISQ.DIST.RT: Power BI DAX Function Explained What is CHISQ.DIST.RT? The CHISQ.DIST.RT function in DAX (Data Analysis Expressions) for Power BI calculates the right-tailed probability of the chi-squared distribution. This is the probability that a chi-squared random variable exceeds a given value, essentially 1 minus the cumulative distribution function (CDF) up to that point. It's part of the statistical functions in Power BI, used for hypothesis testing and analyzing variances in data. Syntax CHISQ.DIST.RT( x , deg_freedom ) x : The value at which to evaluate the distribution (must be ≥ 0). deg_freedom : The degrees of freedom (integer ≥ 1, up to 10^10). Returns: A decimal number between 0 and 1 representing the right-tail probability. How It Works The chi-squared distribution models the sum of squares of inde...

Power BI DAX SIGN Function Description The SIGN function in DAX (Data Analysis Expressions) returns an integer indicating the sign of a number: 1 if the number is positive. 0 if the number is zero. -1 if the number is negative. Syntax SIGN(<number>) Parameter: number : A numeric expression or column containing a number. Return Value An integer: 1 , 0 , or -1 . Use Case The SIGN function is useful for categorizing or analyzing data based on whether values are positive, negative, or zero. Common scenarios include: Financial Analysis: Identify profit (positive) or loss (negative) in financial metrics. Trend Analysis: Determine the direction of change in values (e.g., sales growth or decline). Conditional Formatting: Apply visual indicators based on the sign of a value. Example Suppose you have a table Sales ...

Power BI DAX SINH Function Description The SINH function in Power BI DAX (Data Analysis Expressions) calculates the hyperbolic sine of a given number. The hyperbolic sine is a mathematical function used in various scientific and engineering calculations, defined as (e^x - e^-x) / 2 , where e is the base of the natural logarithm (~2.718). Syntax SINH(number) number : A numeric value (in radians) for which to calculate the hyperbolic sine. Return Value The hyperbolic sine of the input number, returned as a floating-point number. Use Case The SINH function is used in scenarios involving exponential growth, physics, engineering, or financial modeling where hyperbolic functions are relevant. For example: Physics Calculations : Modeling wave propagation or heat transfer where hyperbolic functions describe certain behaviors. Financial Modeling : Calculating growth rates or compounded values in spe...

Power BI DAX FORMAT Function Description The FORMAT function in DAX (Data Analysis Expressions) is used to convert a value into a specified text format. It is primarily used to display numbers, dates, or other data types as strings in a custom format for reporting purposes in Power BI. Syntax FORMAT(<value>, <format_string>) <value> : The value to be formatted (e.g., a number, date, or expression). <format_string> : A string specifying the desired format (e.g., "0.00" for numbers, "yyyy-mm-dd" for dates). Return Value A text string representing the formatted value. Use Cases Custom Number Formatting : Display numbers with specific decimal places, percentages, or currency symbols. Date Formatting : Convert dates into user-friendly formats (e.g., "January 1, 2025" or "01/01/2025"). Dynamic Labels : Create d...

INFO.ATTRIBUTEHIERARCHYSTORAGES DAX Function Overview The INFO.ATTRIBUTEHIERARCHYSTORAGES is an advanced INFO function in DAX (Data Analysis Expressions) used in Power BI, Analysis Services, and Power Pivot. It retrieves detailed metadata about the storage configurations for attribute hierarchies in the semantic model. Attribute hierarchies are internal structures in tabular models that define how dimension attributes are organized for efficient querying and aggregation. Note: This function is undocumented by Microsoft and may be unsupported in future releases. It corresponds to the underlying TMSCHEMA_ATTRIBUTE_HIERARCHY_STORAGES Dynamic Management View (DMV). Use with caution, as it requires semantic model admin permissions and is intended for advanced model inspection. Syntax INFO.ATTRIBUTEHIERARCHYSTORAGES( [<RestrictionName> [, [<RestrictionValue>] [, <RestrictionName> [, [<RestrictionValue> [, … ] ] ] ] ] ] ) Parameters: Restriction...

Power BI DAX DATEADD Function Description The DATEADD function in Power BI DAX (Data Analysis Expressions) shifts a date/time column by a specified number of intervals (e.g., days, months, years) to create a new date. It is commonly used for time-based calculations, such as comparing sales across different periods or creating rolling date ranges. Syntax DATEADD(<dates>, <number_of_intervals>, <interval>) <dates> : A column containing date/time values. <number_of_intervals> : An integer specifying the number of intervals to add (positive) or subtract (negative). <interval> : The time unit for the shift. Options are: YEAR QUARTER MONTH DAY Return Value A column of dates shif...

Power BI DAX CONTAINS Function Description The CONTAINS function in Power BI DAX checks if a specific value exists in a column within a table. It returns TRUE if the value is found and FALSE if it is not. Syntax CONTAINS(<table>, <column>, <value>[, <column>, <value>]...) table : The table to search in. column : The column to check for the value. value : The value to look for in the column. Multiple column-value pairs can be specified to check multiple conditions. Use Case The CONTAINS function is useful for filtering data, creating conditional calculations, or validating the presence of specific values in a dataset. It is commonly used in scenarios like: Checking if a specific product exists in a sales table. Filtering rows based on multiple conditions across columns. Creating measures to flag specific data points. ...

Power BI DAX: INFO.HIERARCHIES Function Description The INFO.HIERARCHIES function in DAX (Data Analysis Expressions) is used in Power BI to retrieve metadata about hierarchies defined in a tabular model. It returns a table containing information about the hierarchies, such as their names and associated attributes, within the current model context. Syntax INFO.HIERARCHIES() Parameters: None. The function does not accept any parameters. Return Value: A table with columns describing the hierarchies in the model, including: HIERARCHY_NAME : The name of the hierarchy. TABLE_NAME : The table containing the hierarchy. LEVELS : The number of levels in the hierarchy. PARENT_CHILD : Indicates if it is a parent-child hierarchy (True/False). Use Case The INFO.HIERARCHIES function is primarily used for: Model Documentation : To programmatically retrieve and document the structure o...

NATURALLEFTOUTERJOIN in Power BI DAX Description The NATURALLEFTOUTERJOIN function in Power BI DAX performs a left outer join between two tables based on common column names. It returns a table that includes all rows from the left table and matching rows from the right table. If there is no match, NULL values are returned for columns from the right table. Syntax NATURALLEFTOUTERJOIN(<LeftTable>, <RightTable>) <LeftTable> : The table from which all rows are included. <RightTable> : The table from which matching rows are included. Key Points Joins tables based on columns with the same name in both tables. Preserves all rows from the left table. Non-matching rows from the right table result in NULL values. Unlike SQL, it does not allow explicit specification of join conditions; it relies on identical column names. Use Case NATURALLE...

Power BI DAX: INFO.LEVELS Function Description The INFO.LEVELS function in Power BI DAX (Data Analysis Expressions) is used to retrieve metadata about the levels of a hierarchy in a tabular model. It returns a table containing information about the levels within a specified hierarchy, such as their names, order, and other properties. Syntax INFO.LEVELS(Hierarchy) Parameters: Hierarchy : The name of the hierarchy (e.g., [TableName].[HierarchyName] ) or a reference to a hierarchy in the model. Return Value A table with the following columns: HIERARCHY_NAME : Name of the hierarchy. LEVEL_NUMBER : The level number in the hierarchy (starting from 0 for the top level). LEVEL_NAME : Name of the level. LEVEL_CAPTION : Display name of the level. LEVEL_UNIQUE_NAME : Unique identifier for the level. Use Case The INFO.LEVELS function is primarily us...

Power BI DAX HOUR Function The HOUR function in Power BI DAX (Data Analysis Expressions) extracts the hour component from a given time or datetime value and returns it as an integer between 0 and 23. Syntax HOUR(datetime) datetime : A datetime value or a column containing datetime values (e.g., "2025-09-17 14:30:00"). Return Value An integer from 0 to 23, representing the hour of the day in 24-hour format. Use Case The HOUR function is useful for analyzing time-based data, such as grouping or filtering data by specific hours of the day. Common scenarios include: Analyzing sales or user activity by hour (e.g., peak hours for transactions). Creating time-based reports or dashboards (e.g., visualizing hourly trends). Filtering data for specific time periods (e.g., business hours vs. after-hours). Example Suppose you have a table named Sales with...

TOTALQTD DAX Function The TOTALQTD function in Power BI's DAX (Data Analysis Expressions) calculates the total for a specified expression over the quarter-to-date period based on a date column. Syntax TOTALQTD(<expression>, <dates>[, <filter>]) expression : The calculation to aggregate (e.g., SUM(Table[Sales]) ). dates : A column containing date values. filter (optional): A filter expression to apply to the calculation. Purpose TOTALQTD returns the cumulative total of an expression from the start of the quarter to the latest date in the current context. It’s useful for financial reporting, sales tracking, or any analysis requiring quarter-to-date metrics. Use Case Suppose you have a sales table and want to calculate total sales from the beginning of the current quarter to the selected date in a report. Example Table: SalesData Date S...

Power BI DAX: ENDOFYEAR Function Description The ENDOFYEAR function in Power BI DAX (Data Analysis Expressions) returns the last date of the year for a given date column. It is commonly used in time intelligence calculations to analyze data at the year-end level, such as financial reporting or annual summaries. Syntax ENDOFYEAR(<dates>[, <year_end_date>]) <dates> : A column containing date values or an expression that returns a date. <year_end_date> : (Optional) A literal string (e.g., "12/31" or "30/06") specifying a custom year-end date. If omitted, it defaults to December 31. Return Value A single date representing the last day of the year for the specified date(s). Use Case The ENDOFYEAR function is useful for: Calculating year-end values, such as total sales or inventory levels at the end of the year. Comparing data points...

Power BI DAX EXPAND Function The EXPAND function in Power BI's Data Analysis Expressions (DAX) is used to expand a table by adding rows for each combination of values from a specified column in another table, typically in a one-to-many relationship. It is particularly useful in scenarios requiring detailed data expansion for analysis, such as generating all possible combinations for reporting. Syntax EXPAND(<table>, <column>) <table> : The base table to be expanded. <column> : The column from a related table whose values will be used to create new rows in the base table. Use Case The EXPAND function is commonly used when you need to create a detailed table that includes all possible combinations of data from related tables. For example, in sales analysis, you might use EXPAND to generate rows for every product in every region, even if no sales exist for some combinations, to ensure comprehensive r...

QUOTIENT DAX Function in Power BI What is QUOTIENT? The QUOTIENT function in DAX (Data Analysis Expressions) performs integer division. It divides the numerator by the denominator and returns only the whole number part of the result, discarding any remainder (fractional part). This is similar to the integer division operator in many programming languages (e.g., \ in Python or Integer Division in Excel). It's useful when you need to avoid decimals in division results, such as calculating complete units or groups. Syntax QUOTIENT(<numerator>, <denominator>) Parameters numerator : The number to divide (dividend). Can be a number, expression, or column reference. denominator : The number to divide by (divisor). Must not be zero. Return Value Returns a whole number (integer). If the result is negative, it rounds toward zero (e.g., QUOTIENT(-5, 2) = -2). Key Remarks Returns a #VALUE! error if either argu...

APPROXIMATEDISTINCTCOUNT DAX Function in Power BI What is APPROXIMATEDISTINCTCOUNT? The APPROXIMATEDISTINCTCOUNT function in DAX (Data Analysis Expressions) is a statistical aggregation function used in Power BI to estimate the number of unique (distinct) values in a specified column. It provides an approximation rather than an exact count, which makes it faster for processing large datasets. Syntax APPROXIMATEDISTINCTCOUNT(<column>) <column> : The column reference (e.g., 'Table'[ColumnName] ) containing any data type. Return Value: An integer representing the approximate distinct count, or BLANK if no rows are found. How Does It Work? This function invokes an optimized aggregation operation directly in the underlying data source, reducing query time at the cost of slight accuracy loss (typically up to 2% error with 97% probability). It leverages database-specific functions like AP...

DAX SWITCH Function Purpose The SWITCH function in DAX (Data Analysis Expressions) evaluates an expression against a list of values and returns a result corresponding to the first matching value. It is similar to a CASE statement in SQL or a nested IF statement, but more concise and readable. Syntax SWITCH(<expression>, <value1>, <result1> [, <value2>, <result2>] ... [, <default_result>]) <expression> : The value or expression to evaluate. <value1>, <value2>, ... : The values to compare against the expression. <result1>, <result2>, ... : The results returned if the corresponding value matches. <default_result> (optional): The result if no values match. Use Case A common use case for SWITCH is to categorize data based on a condition, such as assigning labels to ranges of values. For example, in a sales report, yo...

HASONEFILTER DAX Function Description The HASONEFILTER function in Power BI DAX checks if a column has exactly one value filtered in the current context. It returns TRUE if the column is filtered to a single value, otherwise FALSE . Syntax HASONEFILTER(<columnName>) <columnName> : The name of the column to check for a single filter. Return Value TRUE or FALSE (Boolean). Use Case HASONEFILTER is useful in scenarios where you need to apply conditional logic based on whether a single value is selected in a slicer or filter. It’s commonly used in measures to customize calculations or display different results when a single filter is applied. Example Suppose you have a table Sales with columns Region and SalesAmount . You want to display a specific message when a single region is selected in a slicer. RegionMessage = IF( HASONEFILTER(Sales[Region]), "Single...

LCM Function in Power BI DAX Description The LCM function in Power BI DAX calculates the Least Common Multiple of two integers. It returns the smallest positive number that is a multiple of both input numbers. Syntax LCM(number1, number2) number1 : First integer number. number2 : Second integer number. Return Value An integer representing the least common multiple of the two input numbers. If either input is not an integer, it is truncated to an integer. Returns an error if inputs are negative or zero. Use Case The LCM function is useful in scenarios requiring synchronization or alignment of periodic events, such as: Scheduling Analysis : Determine when two recurring events (e.g., maintenance schedules or delivery cycles) will coincide. Financial Modeling : Align payment cycles or interest calculations that occur at different intervals. Data Aggregation : Combi...

Power BI DAX: INFO.CALCULATIONGROUPS Function The INFO.CALCULATIONGROUPS function in Power BI DAX is used to retrieve metadata about calculation groups defined in a model. It is part of the INFO family of functions introduced in SQL Server 2022 Analysis Services and Azure Analysis Services to support model introspection. Syntax INFO.CALCULATIONGROUPS() Returns: A table containing metadata about all calculation groups in the current model. Return Value The function returns a table with the following columns: CalculationGroupName: Name of the calculation group. Priority: The precedence order of the calculation group (higher values take precedence). Description: Description of the calculation group, if provided. CalculationItems: A comma-separated list of calculation items within the group. Use Case INFO.CALCULATIONGROUPS is primarily used for model documentation and dynamic reporting . I...

Power BI DAX VAR.S Function Description The VAR.S function in Power BI DAX (Data Analysis Expressions) calculates the sample variance of a given set of values in a column. Variance measures how much the values in a dataset deviate from the mean, providing insight into data spread. VAR.S is used for a sample of a population, not the entire population (for the entire population, use VAR.P ). Syntax VAR.S(<column>) <column> : The column containing the numeric values for which to calculate the sample variance. Return Value A numeric value representing the sample variance of the specified column. Use Case VAR.S is useful in statistical analysis to understand data variability. Common scenarios include: Analyzing sales fluctuations across regions or time periods. Assessing the variability of product prices or customer ratings. Evaluating performance metrics, such as...

Power BI DAX: CLOSINGBALANCEMONTH Function Description The CLOSINGBALANCEMONTH function in Power BI DAX (Data Analysis Expressions) calculates the value of an expression at the end of a specified month, considering a given date column and filter context. Syntax CLOSINGBALANCEMONTH(<expression>, <dates>[, <filter>]) <expression> : The value 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. Return Value The value of the expression at the last date of the month within the filter context. Use Case CLOSINGBALANCEMONTH is commonly used in financial and time-based analysis to determine the balance or total of a measure (e.g., inventory, account balance, or sales) at the end of each month. It is particularly useful for: ...

AMORLINC DAX Function Description The AMORLINC function in Power BI DAX calculates the depreciation for each accounting period using the French straight-line depreciation method (amortissement linéaire). It is designed for financial calculations, particularly in French accounting systems, where assets are depreciated linearly over their useful life, with partial periods accounted for. Syntax AMORLINC(<cost>, <date_purchased>, <first_period>, <salvage>, <period>, <rate>, [<basis>]) Parameters cost : The initial cost of the asset (required). date_purchased : The date the asset was purchased (required). first_period : The date of the end of the first period (required). salvage : The salvage value of the asset at the end of its useful life (required). period : The specific period for which to calculate depreciation (required). rate : The depr...

PERCENTILE.EXC DAX Function The PERCENTILE.EXC function in Power BI DAX calculates the k-th percentile of values in a column, where k is a value between 0 and 1 (exclusive). It is used to determine the value below which a given percentage of data points fall, excluding the lowest and highest values in the interpolation. Syntax PERCENTILE.EXC(<column>, <k>) <column> : A column containing numeric values. <k> : A number between 0 and 1 (exclusive, e.g., 0.25 for 25th percentile). Key Points Returns the value at the specified percentile using linear interpolation. Excludes the minimum and maximum values in the calculation (unlike PERCENTILE.INC ). Ignores non-numeric values and blanks. Commonly used in statistical analysis to understand data distribution. Use Case Example Scenario: A retail company wants to analyze sales performance across s...

Power BI DAX RANGE Function Overview The RANGE function in DAX (Data Analysis Expressions) is used in Power BI to generate a table with a sequence of numbers within a specified range. It is particularly useful for creating dynamic tables or lists of values for calculations, iterations, or visualizations. Syntax RANGE(<start>, <end>, [<step>]) start : The starting number of the sequence. end : The ending number of the sequence (inclusive). step (optional): The increment between numbers in the sequence. Default is 1. Return Value A single-column table named Value containing the sequence of numbers from start to end , incremented by step . Use Case The RANGE function is often used in scenarios where you need to: Create a series of dates or numbers for a custom calendar or index. Generate iterative calculations, such as running totals or simulat...

DAX COTH Function The COTH function in Power BI's Data Analysis Expressions (DAX) calculates the hyperbolic cotangent of a number. It is a mathematical function used in specific analytical scenarios involving hyperbolic trigonometry. Syntax COTH(number) Parameter: number : A real number for which to calculate the hyperbolic cotangent. Must be non-zero. Return Value Returns the hyperbolic cotangent of the input number. Formula The hyperbolic cotangent is defined as: COTH(x) = COSH(x) / SINH(x) = (e^x + e^(-x)) / (e^x - e^(-x)) Where e is the base of the natural logarithm (~2.718). Use Case The COTH function is rarely used in typical business intelligence scenarios but is valuable in specialized fields like: Engineering: Modeling catenary curves (e.g., shapes of hanging cables or chains). Physics: Calculations involving hyperbolic relationships, such as in relativity or wav...

Power BI DAX EARLIER Function Description The EARLIER function in DAX (Data Analysis Expressions) is used to access a previous row context during calculations in Power BI. It is primarily used in nested calculations, such as within CALCULATE or iteration functions like FILTER , to reference a value from an earlier row context. Syntax EARLIER( <column> [, <number>] ) <column> : The column to retrieve the value from in the earlier row context. <number> : (Optional) Specifies which earlier context to access (1 = immediate earlier context, 2 = two levels back, etc.). Defaults to 1. Use Case The EARLIER function is commonly used in scenarios where you need to compare values across rows within a table, such as ranking, running totals, or conditional calculations based on prior row contexts. Example Suppose you have a sales table and want to calculate the rank of each product based on sales ...