Daily DAX : Day 290 TOPNPERLEVEL

Explanation of the Power BI DAX Function TOPNPERLEVEL (and why to avoid it)

The DAX function TOPNPERLEVEL is an undocumented and deprecated extension function in Power BI. This means it's not officially supported, its behavior might be inconsistent, and it's no longer recommended for use.

It's highly advised to avoid using TOPNPERLEVEL in your Power BI models.

Instead, you should use the standard and well-documented TOPN function, often in conjunction with other DAX functions like CALCULATE, SUMMARIZE, ALL, ALLSELECTED, and RANKX, to achieve similar "top N per level" or "top N by group" results.

Why was TOPNPERLEVEL problematic/deprecated?

The dax.guide website, a reliable resource for DAX functions, lists TOPNPERLEVEL with the following important remarks:

• Undocumented: It's not part of the official DAX specification or documentation from Microsoft.
• Not recommended: Its use is discouraged.
• Deprecated: It's considered obsolete, and there are likely better, more stable alternatives.
• Volatile: It may return different results even with the same arguments, which is a major concern for data consistency.
• DirectQuery compatibility limitations: It has limitations when used in DirectQuery mode.

How to achieve "Top N per Level" with TOPN and other DAX functions (the recommended way)

The common use case for what TOPNPERLEVEL might have aimed to do is to find the "top N items within each category," "top N products per region," or similar hierarchical "top N" scenarios. This is typically accomplished using a combination of TOPN with functions that manipulate context and grouping.

Here's a general pattern for achieving "Top N per Level" using supported DAX functions:

1. Define your measure: Create the measure you want to rank by (e.g., [Total Sales]).
2. Use CALCULATE and TOPN: Wrap your measure with CALCULATE and use TOPN as a filter argument.
3. Define the grouping: Inside TOPN, use VALUES or ALL on the column(s) that define your "levels" or groups.
4. Specify the ordering expression: This is usually your measure.

Example Use Case: Top 3 Products per Category

Let's say you have a Sales table with Product Name, Category, and Sales Amount. You want to show the top 3 products for each category based on Sales Amount.

Top 3 Products per Category Sales =
CALCULATE (
    [Total Sales], // Your base measure, e.g., SUM(Sales[Sales Amount])
    TOPN (
        3, // N_Value: Number of top items to return
        ALLSELECTED ( 'Product'[Product Name], 'Product'[Category] ), // Table: All products within the current filter context for category
        [Total Sales] // OrderBy_Expression: The measure to rank by
    )
)

Explanation:

• CALCULATE([Total Sales], ...): This changes the filter context for the [Total Sales] measure.
• TOPN(3, ALLSELECTED('Product'[Product Name], 'Product'[Category]), [Total Sales]): This is the core of the "per level" logic.
  • 3: We want the top 3.
  • ALLSELECTED('Product'[Product Name], 'Product'[Category]): This creates a virtual table of all product names and categories currently selected in the report (or all if nothing is selected). This is crucial for the "per level" aspect, as it considers the existing filter context for categories but then allows TOPN to operate across all products within that category context.
  • [Total Sales]: We are ranking based on the [Total Sales] for each product.

When this measure is used in a visual (e.g., a table or matrix) that has Category on rows, Power BI will evaluate the TOPN expression for each category in its respective filter context, effectively giving you the top 3 products within each category.

In summary:

While TOPNPERLEVEL existed as an undocumented DAX function, it is deprecated and not recommended for use. For "Top N per level" or "Top N by group" scenarios, you should leverage the powerful TOPN function in conjunction with CALCULATE, ALL, ALLSELECTED, VALUES, or SUMMARIZE to achieve robust and well-supported solutions.