50+ Essential Formulas every PMO should know

Fresh off achieving my PL-300 certification, I’m thrilled to share insights into the dynamic world of DAX formulas! Whether you’re diving into basic calculations or exploring advanced data modeling, this blog post is your ultimate guide.

Join me as we unlock the power of DAX to conquer everyday data challenges, supercharge performance, and craft stunning visualizations that tell compelling stories.

By the end of this post, you’ll have a toolkit of essential DAX formulas that will empower you to elevate your Power BI projects and deliver actionable insights with ease.

Let’s get started.

1. Creating a Date Table

DAX Formula:

Date = CALENDARAUTO()

Use: Create a date table spanning the entire range of dates in your data model (e.g., from the earliest to the latest date).

2. Calculating Fiscal Year

DAX Formula:

Year = "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1, 0)

Use: Calculates the fiscal year, appending “FY” and incrementing the year if the month is after June (e.g., “FY24”).

3. Formatting Date as Year and Month

DAX Formula:

Month = FORMAT('Date'[Date], "yyyy MMM")

Use: Format the date as “YYYY MMM” (e.g., “2024 Jun”).

4. Creating a Month Key

DAX Formula:

MonthKey = (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])

Use: Create a numeric key for the month in the format “YYYYMM” (e.g., “202406” for June 2024).

5. Average Price

DAX Formula:

Avg Price = AVERAGE(Sales[Unit Price])

Use: Calculates the average unit price from the Sales table.

6. Median Price

DAX Formula:

Median Price = MEDIAN(Sales[Unit Price])

Use: Determines the median unit price from the Sales table.

7. Minimum Price

DAX Formula:

Min Price = MIN(Sales[Unit Price])

Use: Finds the minimum unit price from the Sales table.

8. Maximum Price

DAX Formula:

Max Price = MAX(Sales[Unit Price])

Use: Retrieves the maximum unit price from the Sales table.

9. Orders

DAX Formula:

Orders = DISTINCTCOUNT(Sales[SalesOrderNumber])

Use: Counts the distinct number of sales orders in the Sales table.

10. Order Lines

DAX Formula:

Order Lines = COUNTROWS(Sales)

Use: Counts the total number of rows (order lines) in the Sales table.

11. HasONEValue() – Target Amount Based on Salesperson

DAX Formula:

Target = IF(HASONEVALUE('Salesperson (Performance)'[Salesperson]), SUM(Targets[TargetAmount]))

Use: Retrieves the target amount from the Targets table when a specific salesperson is selected in the ‘Salesperson (Performance)’ table context. This ensures accurate tracking of individual sales targets within the Power BI report.

12. Sales Variance from Target

DAX Formula:

Variance = IF(HASONEVALUE('Salesperson (Performance)'[Salesperson]), SUM(Sales[Sales]) - [Target])

Use: Calculates the variance between actual sales and target sales for a specific salesperson in the ‘Salesperson (Performance)’ table context, aiding in performance analysis and goal assessment.

13. Variance Margin Calculation

DAX Formula:

Variance Margin = DIVIDE([Variance], [Target])

Use: Computes the variance margin as a percentage by dividing the sales variance by the target amount, helping to assess the performance relative to the set targets in the ‘Salesperson (Performance)’ table context.

Certainly! Here’s how you can structure the DAX formula and its use case:

14. Find the sales amount for a particular category using Calculate

Blue Sales Amount = CALCULATE(SUM(Sales[SalesAmount]), Product[Color] = "Blue")

Use:
Calculates the total sales amount specifically for products with the color “Blue” in the Sales table context. This measure helps in analyzing sales performance for blue-colored products separately.

15. Count Customers Where Order Quantity Exceeds 5

Count Customers = 
CALCULATE(
    DISTINCTCOUNT(Order[Product1D]),
    FILTER(Order, Order[OrderQty] > 5)
)

Use: Calculates the distinct count of customers who ordered products (Product1D) where the order quantity (OrderQty) is greater than 5.

16. Count Customers with Order Quantity Filter Maintained

Count Customers = 
CALCULATE(
    DISTINCTCOUNT(Order[Product1D]),
    KEEPFILTERS(Order[OrderQty] > 5)
)

Use: Calculates the distinct count of customers who ordered products (Product1D) where the order quantity (OrderQty) is greater than 5, keeping other filters on the Order table applied.

Note: DAX Formula 15 (FILTER function): It applies a new filter condition (Order[OrderQty] > 5) to the Order table, potentially replacing any existing filters on OrderQty. It evaluates the distinct count based on this filtered context.

DAX Formula 16 (KEEPFILTERS function): It retains all existing filters that might already be applied to the Order table context, including those on OrderQty. It calculates the distinct count based on the context where OrderQty is greater than 5, without altering other filters.

In summary, Formula 1 introduces a new filter condition on OrderQty, possibly replacing existing filters, while Formula 2 preserves all existing filters, ensuring the count is calculated within the context where OrderQty exceeds 5 without affecting other filters.

To express the logic where [TotalProductCost] is calculated based on conditions in DAX, you can use the IF function. Here’s how you can structure it:

17. Calculate Total Product Cost Based on Conditions

TotalProductCost = 
IF(
    ISBLANK([TotalProductCost]),
    [OrderQuantity] * [StandardCost],
    [TotalProductCost]
)

Use:
Calculates the total product cost (TotalProductCost) based on the following conditions:

• If [TotalProductCost] is blank (null or empty), calculate it as [OrderQuantity] * [StandardCost].
• Otherwise, use the existing value of [TotalProductCost].

IF(ISBLANK([TotalProductCost]), …): Checks if [TotalProductCost] is blank (null). If true, it calculates [OrderQuantity] * [StandardCost].

• ELSE: If [TotalProductCost] is not blank, it uses the existing value of [TotalProductCost].

This formula ensures that [TotalProductCost] is computed dynamically based on whether it already has a value or needs to be calculated from [OrderQuantity] and [StandardCost].

18. Calculate Sales of ALL Region W/O filters

DAX Formula:

Sales All Region = 
CALCULATE(
    SUM(Sales[Sales]),
    REMOVEFILTERS(Region)
)

Explanation:

The CALCULATE() function in DAX is used to manipulate the filter context. In this formula:

• SUM(Sales[Sales]): Calculates the sum of the Sales column in the Sales table.
• REMOVEFILTERS(Region): Modifies the filter context by removing any active filters applied to columns in the Region table.

Use: This measure (Sales All Region) evaluates the total sales across all regions by ignoring any filters that might be applied to the Region table columns. It provides a clear way to view the aggregate sales figure without regional filters affecting the result.

19. Comparative Analysis of Regional Sales Contribution Relative to Total Sales Across Multiple Regions

DAX Formula:

Sales % All Region =  
DIVIDE(  
    SUM(Sales[Sales]),  
    CALCULATE(  
        SUM(Sales[Sales]),  
        REMOVEFILTERS(Region)  
    )  
)

Explanation:

The DIVIDE() function in DAX divides one expression by another while handling errors and zero values gracefully. In this formula:

• SUM(Sales[Sales]): Calculates the sum of the Sales column in the Sales table without any modification to the filter context.
• CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Region)): Modifies the filter context by removing any active filters applied to columns in the Region table and calculates the sum of Sales in this modified context.

Use:This measure (Sales % All Region) computes the percentage of total sales across all regions. It divides the total sales (SUM(Sales[Sales])) by the total sales calculated in a filter context where any regional filters are ignored (CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Region))). This allows for a comparison of sales performance across regions relative to the overall sales.

20. Analyzing Country Sales Contribution Compared to Regional Totals

DAX Formula:

Sales % Country =  
DIVIDE(  
    SUM(Sales[Sales]),  
    CALCULATE(  
        SUM(Sales[Sales]),  
        REMOVEFILTERS(Region[Region])  
    )  
)

Explanation: The Sales % Country measure calculates the percentage of total sales within the context of each country, preserving filters applied to country-specific columns while ignoring filters on the region column.

• SUM(Sales[Sales]): Calculates the total sales in the current filter context.
• CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Region[Region])): Adjusts the filter context by removing any active filters on the Region column of the Region table. Filters on other columns (like country-specific filters) remain intact.

Use: This measure is useful for analyzing and comparing sales performance across different countries within the region. It allows stakeholders to see how each country contributes to the overall sales figure, taking into account regional contexts while focusing on country-specific sales percentages.

21. Analyzing Country Sales only if the Region in scope

DAX Formula:

Sales % Country =  
IF(  
    ISINSCOPE(Region[Region]),  
    DIVIDE(  
        SUM(Sales[Sales]),  
        CALCULATE(  
            SUM(Sales[Sales]),  
            REMOVEFILTERS(Region[Region])  
        )  
    )  
)

Explanation:

The Sales % Country measure calculates the percentage of total sales within the context of each country only when the Region[Region] column is in scope.

• IF(ISINSCOPE(Region[Region]), …): Checks if the Region[Region] column is in the current scope of the visual or calculation context.
• DIVIDE(SUM(Sales[Sales]), …): Computes the division of total sales by the total sales within the context of the region, adjusting the filter context to ignore filters on the Region[Region] column.

Use: This updated measure ensures that it returns a meaningful value (percentage of sales by country relative to regional total) only when viewing data at the country level within a regional context. When the region isn’t specifically scoped, such as in higher-level views, it returns a blank value.

22. Sales Percentage Contribution Across Group Analysis

DAX Formula:

Sales % Group =  
DIVIDE(  
    SUM(Sales[Sales]),  
    CALCULATE(  
        SUM(Sales[Sales]),  
        REMOVEFILTERS(  
            Region[Region],  
            Region[Country]  
        )  
    )  
)

Explanation:

The Sales % Group measure calculates the percentage of total sales relative to the total sales across all groups by removing filters on both the Region[Region] and Region[Country] columns.

• DIVIDE(SUM(Sales[Sales]), …): Computes the division of total sales by the total sales within the context where filters on Region[Region] and Region[Country] are removed.
• CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(…)): Adjusts the filter context by removing active filters on both Region[Region] and Region[Country] columns, ensuring the calculation is based on the entire group rather than specific regions or countries.

Use: This measure is useful for analyzing and comparing the contribution of sales within a broader group context, ensuring that the percentage calculation reflects sales performance across all regions and countries without specific filters affecting the result.

23. Sales Percentage by Group (Region or Country) Analysis

DAX Formula:

Sales % Group =  
IF(  
    ISINSCOPE(Region[Region])  
    || ISINSCOPE(Region[Country]),  
    DIVIDE(  
        SUM(Sales[Sales]),  
        CALCULATE(  
            SUM(Sales[Sales]),  
            REMOVEFILTERS(  
                Region[Region],  
                Region[Country]  
            )  
        )  
    )  
)

Explanation:

The Sales % Group measure calculates the percentage of total sales within the context of a group (region or country) only when either the Region[Region] or Region[Country] columns are in scope.

• IF(ISINSCOPE(Region[Region]) || ISINSCOPE(Region[Country]), …): Checks if either the Region[Region] or Region[Country] columns are in the current scope of the visual or calculation context.
• DIVIDE(SUM(Sales[Sales]), …): Computes the division of total sales by the total sales within the context where filters on both Region[Region] and Region[Country] are removed.

Use: This measure ensures that it returns a meaningful value (percentage of sales by group) only when viewing data at the region or country level within the hierarchy. When neither region nor country is specifically scoped, such as in higher-level views, it returns a blank value.

24. Year-to-Date (YTD) Sales Analysis up to June 30th

DAX Formula:

Sales YTD =  
TOTALYTD(SUM(Sales[Sales]), 'Date'[Date], "6-30")

Explanation:

The Sales YTD measure calculates the year-to-date (YTD) total of sales using the TOTALYTD function in DAX.

• TOTALYTD(SUM(Sales[Sales]), ‘Date'[Date], “6-30”): This function evaluates the sum of the Sales column over a specified date column ('Date'[Date]). It computes the cumulative total from the beginning of the year up to the date specified as “6-30”, which represents June 30th for Adventure Works, the end of their fiscal year.

Use: This measure is useful for tracking and analyzing cumulative sales performance year-to-date. It provides insights into how sales are accumulating over time, up to a specified point in the fiscal year.

25. Year-over-Year (YoY) Sales Growth Analysis

DAX Formula:

Sales YoY Growth =  
VAR SalesPriorYear =  
 CALCULATE(  
 SUM(Sales[Sales]),  
 PARALLELPERIOD(  
 'Date'[Date],  
 -12,  
 MONTH  
 )  
 )  
RETURN  
 SalesPriorYear

Explanation:

The Sales YoY Growth measure calculates the year-over-year (YoY) growth in sales using a variable (SalesPriorYear) and the CALCULATE function in DAX.

• VAR SalesPriorYear: Defines a variable to store the result of calculating sales from the previous year.
• CALCULATE(SUM(Sales[Sales]), PARALLELPERIOD(‘Date'[Date], -12, MONTH)): Calculates the sum of sales for the same period in the previous year using the PARALLELPERIOD function to shift the date context back by 12 months.

Use: This measure is useful for analyzing and comparing sales growth year-over-year. It helps in understanding how sales performance in the current period compares to the equivalent period in the previous year, providing insights into sales trends and performance changes over time.

Note: Variables in DAX: Variables are like containers that store values temporarily. They are declared with a unique name and can only be used within the measure where they are defined. In DAX, variables are handy for storing complex calculations or intermediate results.

SalesPriorYear Variable: This variable calculates the sum of sales for the previous year. It does this by using the PARALLELPERIOD() function, which shifts the date context back by 12 months (-12). This means it considers sales data from the same period in the previous year relative to each date in the current filter context.

26. Year-over-Year (YoY) Sales Growth Percentage Analysis

DAX Formula:

Sales YoY Growth =  
VAR SalesPriorYear =  
 CALCULATE(  
 SUM(Sales[Sales]),  
 PARALLELPERIOD(  
 'Date'[Date],  
 -12,  
 MONTH  
 )  
 )  
RETURN  
 DIVIDE(  
 (SUM(Sales[Sales]) - SalesPriorYear),  
 SalesPriorYear  
 )

Explanation:

The Sales YoY Growth measure calculates the year-over-year (YoY) growth in sales using a variable named SalesPriorYear. Here’s how it works:

• VAR SalesPriorYear: Defines a variable to store the total sales from the previous year (PARALLELPERIOD('Date'[Date], -12, MONTH) shifts the date context back by 12 months).
• RETURN: Specifies the calculation result that will be returned by the measure.
• DIVIDE(): Calculates the percentage growth by dividing the difference between current year sales (SUM(Sales[Sales])) and previous year sales (SalesPriorYear) by SalesPriorYear.

Explanation of Variable Reference:

• Usage in RETURN Clause: The SalesPriorYear variable is referenced twice in the formula. First, it calculates the previous year’s sales total (SalesPriorYear). Second, it’s used in the DIVIDE() function to calculate the percentage change in sales year-over-year.

Use: This measure is useful for tracking and analyzing the percentage change in sales performance year-over-year. It helps in understanding how sales growth or decline compares between the current year and the previous year.

This formula efficiently uses variables to store and reuse intermediate results, improving both readability and performance in DAX calculations.

Here are the structured summaries for each of the 25 DAX formulas and functions:

27. Total Sales Values over each row (SUMX)

DAX Formula:

SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Selling price])

Explanation:
Iterates over each row in the ‘Sales’ table, multiplies ‘Quantity’ by ‘Selling price’ for each row, and sums the results to calculate total sales amount.

28. AVERAGEX – Iterates over each row

DAX Formula:

AVERAGEX('Sale Data', 'Sale Data'[Gia ban] + 'Sale Data'[VAT])

Explanation:
Calculates average value of ‘Gia ban’ (selling price) with VAT applied for each item in ‘Sale Data’.

29. Count based on a specific condition (COUNTX)

DAX Formula:

COUNTX('Sale Data', [Selling Price])

Explanation:
Counts all product lines with defined selling price in ‘Sale Data’ table.

Anything to Keep in Mind:
COUNT is a basic function that counts the number of rows in a table or column with non-blank values. COUNTX, on the other hand, allows users to count rows based on specific conditions or expressions1. It counts only values, dates, or strings, and returns a blank if no rows match the condition2.

30. To count the number of Rows in the table (COUNTROWS)

DAX Formula:

COUNTROWS(
    ALLNOBLANKROW('Sale Datamaker'[Now])
)

Explanation:
Counts rows in ‘Sale Datamaker’ table where ‘Now’ column is not blank.

Anything to Keep in Mind:
This function can be used to count the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table, or applying context to a table. Use ALLNOBLANKROW to exclude blank rows from count.

31. Counting empty cells (COUNTBLANK)

DAX Formula:

COUNTBLANK('Sale Data'[Product])

Explanation:
Counts empty fields (blank cells) in ‘Product’ column of ‘Sale Data’ table.

32. DISTINCTCOUNT (without duplicates)

DAX Formula:

DISTINCTCOUNT('Sale Data'[Product])

Explanation:
Counts distinct products in ‘Product’ column of ‘Sale Data’ table.

Anything to Keep in Mind:
Provides unique count of products, useful for uniqueness checks. You can also use the VALUES function to create a list of distinct values and then count the rows of the filtered list.

33. SUMMARIZE

DAX Formula:

SUMMARIZE (
    Sales,
    Sales[Product],
    "Total Revenue", SUM(Sales[Revenue])
)

Explanation:
Summarizes total revenue for each product in ‘Sales’ table.

Anything to Keep in Mind:
the SUMMARIZE function is used to create summary tables based on the grouping of data from one or more tables. Its primary purpose is to summarize data and calculate aggregate values based on specified columns and expressions. Groups data by ‘Product’ column and calculates total revenue.

34. DIVIDE – Mathematical function

DAX Formula:

DIVIDE(5, 2)

Explanation:
Divides 5 by 2 and returns result (2.5).

Anything to Keep in Mind:
Handles division, returns the alternate result or BLANK() on division by 0.

35. MIN – Find the smallest value

DAX Formula:

MIN('Sale Datamaker'[Revenue])

Explanation:
Finds smallest revenue value in ‘Revenue’ column of ‘Sale Datamaker’ table.

Anything to Keep in Mind:
Useful for identifying minimum values in numeric columns.

36. MINX (Find the smallest based on filter condition)

MINX(
    FILTER('Sale Data', 'Sale Data'[Year] = 2017),
    [Profit]
)

Explanation:
Finds lowest profit value in ‘Sale Data’ for year 2017.

Anything to Keep in Mind:
Filters data before evaluating minimum profit.

37. MAX

DAX Formula:

MAX('Sale Data'[Revenue])

Explanation:
Finds highest revenue value in ‘Revenue’ column of ‘Sale Data’ table.

Anything to Keep in Mind:
Useful for identifying maximum values in numeric columns.

38. MAXX (Based on filter condition)

DAX Formula:

MAXX(
    FILTER('Sale Data', 'Sale Data'[Year] = 2017),
    [Profit]
)

Explanation:
Finds highest profit value in ‘Sale Data’ for year 2017.

Anything to Keep in Mind:
Filters data before evaluating maximum profit.

39. FILTER

DAX Formula:

FILTER(
    Sales,
    Sales[Revenue] > 1000
)

Explanation:
Filters rows in ‘Sales’ table where revenue is greater than $1000.

Anything to Keep in Mind:
Allows selective data retrieval based on conditions.

40. CALCULATE

DAX Formula:

CALCULATE(
    SUM('Sale Datamaker'[Revenue]),
    DATESBETWEEN('Sale Datamaker'[Today], DATE(2018, 1, 1), DATE(2018, 3, 31))
)

Explanation:
Calculates sales in first 3 months of 2018 using ‘DATESBETWEEN’.

Anything to Keep in Mind:
Alters context for evaluating data, useful for time-based calculations.

41. ALLSELECTED

DAX Formula:

CALCULATE(
    SUM(Sales[Revenue]),
    ALLSELECTED(Sales)
)

Explanation:
Calculates total revenue for all products, respecting slicer or visual filters.

Anything to Keep in Mind:
Removes filters but retains current context for calculations.

42. SELECTEDVALUE

DAX Formula:

SELECTEDVALUE(Products[ProductName])

Explanation:
Returns selected product name from slicer or filter interaction.

Anything to Keep in Mind:
Handles single value selection from context.

43. SWITCH

DAX Formula:

SWITCH(
    [Month],
    1, "January",
    2, "February",
    3, "March",
    4, "April",
    5, "May",
    6, "June",
    7, "July",
    8, "August",
    9, "September",
    10, "October",
    11, "November",
    12, "December",
    "Unknown value"
)

Explanation:
Returns month name based on numeric month value.

Anything to Keep in Mind:
Simplifies nested IF statements for readability.

44. IF (Excel function)

DAX Formula:

IF(Sales[Revenue] > 1000, "High", "Low")

Explanation:
Categorizes sales as “High” if revenue > $1000, otherwise “Low”.

Anything to Keep in Mind:
Conditional logic based on specified criteria. Also, ChatGPT

In DAX (Data Analysis Expressions), there isn’t a direct IFS function like in Excel. However, you can achieve similar conditional logic using other functions such as IF, SWITCH, or by combining multiple functions and logical operators.

45. VALUES (Used to convert a text string that represents a number to a numeric value)

DAX Formula:

COUNTROWS(
    VALUES('Sale Data'[OrderNumber])
)

Explanation:
Counts distinct orders in ‘OrderNumber’ column of ‘Sale Data’.

Anything to Keep in Mind:
Provides unique count of specified column values. Data Cleaning: Often, data imported from external sources might have numeric values stored as text (e.g., numbers formatted with commas or currency symbols). Using VALUE, you can clean up these values for analysis.

46. RELATED (Alternative to Excel Index, Match function)

DAX Formula:

FILTER(
    'Sale Data'[OrderID],
    RELATED('Product Data'[Product]) <> "Microsoft Azure"
)

Explanation:
Filters transactions excluding ‘Microsoft Azure’ related product groups.

Anything to Keep in Mind:
Relates values between tables based on relationships.

47. RELATEDTABLE

DAX Formula:

RELATEDTABLE(Products)

Explanation:
Returns all products related to specific order in ‘Sales’ table.

Anything to Keep in Mind:
Evaluates table expression in modified context.

48. RANKX (Rank the items)

DAX Formula:

RANKX(
    Sales,
    [Revenue],
    ,
    DESC
)

Explanation:
Ranks items in ‘Sales’ table based on revenue in descending order.

Anything to Keep in Mind:
Useful for ranking data based on specified criteria.

49. LOOKUPVALUE

DAX Formula:

LOOKUPVALUE(
    'Sale Datamaker'[Product],
    'Sale Datamaker'[Nhom product], "Microsoft Office 365"
)

Explanation:
Returns product in ‘Sale Datamaker’ with ‘Nhom product’ value “Microsoft Office 365”.

Anything to Keep in Mind:
Searches table for specific values based on criteria.

50. CONCATENATE

DAX Formula:

CONCATENATE("Ha Noi", ", ", "Viet Nam")

Explanation:
Concatenates “Ha Noi” and “Viet Nam” with comma separator.

Anything to Keep in Mind:
Joins text strings into single text string.

51. TOTALYTD

DAX Formula:

TOTALYTD(
    SUM(Sales[Revenue]),
    Sales[Date]
)

Explanation:
Calculates year-to-date total revenue using ‘Sales’ table and ‘Date’ column.

Anything to Keep in Mind:
Evaluates expression in current context year-to-date.

52. EARLIER

Use Case: You want to compare the current row’s sales revenue with the previous row’s revenue in a calculated column.

PreviousRowValue = EARLIER('Sales'[Revenue])

Explanation: This function allows referencing the value of a column from the previous row context within the same table, useful for calculating differences or ratios.

53. FIRSTNONBLANK

Use Case: Finding the first date where sales occurred for a product.

FirstNonBlankDate = FIRSTNONBLANK('Date'[Date], 'Sales'[Sales])

Explanation: Returns the first non-blank value in the specified column ('Date'[Date]), based on the context defined by another column ('Sales'[Sales]).

54. LASTNONBLANK

Use Case: Finding the most recent date with sales data for a product.

LastNonBlankDate = LASTNONBLANK('Date'[Date], 'Sales'[Sales])

Explanation: Similar to FIRSTNONBLANK, but returns the last non-blank value in the specified column ('Date'[Date]), based on the context defined by another column ('Sales'[Sales]).

55. ALL

Use Case: Calculating total sales irrespective of any applied filters on a specific column.

AllProducts = ALL('Product')

Explanation: Returns all the rows from the specified table ('Product') ignoring any filters applied on columns from that table.

56. ALLEXCEPT

Use Case: Calculating total sales for all product categories except for a specific category.

AllExceptProducts = ALLEXCEPT('Product', 'Product'[Category])

Explanation: Returns all rows from the 'Product' table except for those that are filtered out by the 'Product'[Category] column.

57. USERELATIONSHIP

Use Case: Using a different relationship between tables than the default one to calculate sales.

SalesAmountWithCustomRelationship = CALCULATE(SUM('Sales'[SalesAmount]), USERELATIONSHIP('Date'[Date], 'Sales'[OrderDate]))

Explanation: Overrides the active relationship between 'Date' and 'Sales' tables and uses a different relationship ('Date'[Date] to 'Sales'[OrderDate]) for calculating the sum of sales amount.

58. KEEPFILTERS

Use Case: Calculating sales for a specific year and retaining filters on other columns (like month) in the filter context.

TotalSalesForYear = CALCULATE(SUM('Sales'[SalesAmount]), 'Date'[Year] = 2023, KEEPFILTERS('Date'[Month] = "June"))

Explanation: Ensures that filters applied to 'Date'[Month] = "June" are retained while calculating the total sales for the year 2023, ignoring other filters on 'Date'.

59. UNION

Use Case: Combining data from two tables into a single table for unified reporting or analysis.

UnionTable = UNION('Table1', 'Table2')

Explanation: Stacks rows from 'Table1' on top of 'Table2', creating a single table (UnionTable) with data from both original tables.

Thats a wrap of these 50+ Powerful Power BI DAX Formulas and Functions for Beginners.

These summaries should help any PMO understand each function’s purpose, syntax, and usage considerations in Power BI.

Adjust examples and parameters as per your specific data and analysis needs.