# SQL's APPLY Clause in PowerBI DAX ?

If you have ever worked with any top RDBMS’s like SQL Server or Oracle, the chances are that at some point you must have encountered the APPLY clause especially when dealing with complex queries involving table-valued functions or correlating sub queries.

SQL’s CROSS APPLY and OUTER APPLY operators offer powerful ways to evaluate and join rows from one table through a table-valued function or subquery that enables more dynamic and flexible queries than traditional JOIN clauses.

Here is a typical output of an APPLY clause

![](https://www.sqlservercentral.com/wp-content/uploads/legacy/5e39cc7c310de74cfe77668b5db9dd4d511b7740/5942.gif align="left")

You might ask — *isn’t a SQL APPLY CLAUSE similar to a SQL* CROSS JOIN?

The answer is **Yes and No**.

**Yes**, because the CROSS APPLY clause can produce a Cartesian-like result when used with a table-valued function or subquery where each row from the left table is combined with matching rows from the right side.

**No**, because unlike a CROSS JOIN, the APPLY operator allows you to pass values from the left table into the right side (usually a UDF(User Defined Function) or a correlated subquery). This means the right side can be dynamically evaluated for each row of the left input something which is not possible with a CROSS JOIN.

This article is more inclined towards replicating the APPLY clause behavior in DAX. So we won’t delve too deeply into all the intricate details of the APPLY clause.

Now, lets take a use case.

Suppose we need to get a list of TOP N customers for each Product based on Sales Amount. You probably might be aware of the TOP N clause in DAX. But using only the TOP N function, will give us the overall TOP N customers but not product wise TOP N customers.

```css
EVALUATE
        TOPN (
            2,
           ALLSELECTED (Customer[Name]),
            [Sales Amount]
     )
```

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762163646451/552461bc-1d02-4d4f-a06b-5ca03493905d.png align="center")

To fetch product wise TOP N customers based on Sales Amount we will have to use the GENERATE function in combination with the TOP function.

You can find details of the GENERATE function [here](https://learn.microsoft.com/en-us/dax/generate-function-dax).

GENERATE function basically creates a Cartesian product between two tables which effectively exhibits a SQL CROSS JOIN behavior. Using it in combination with TOP N clause, the GENERATE function behaves exactly like SQL’s APPLY clause.

To exhibit such behavior we can directly use GENERATE with TOPN.

```css
EVALUATE

SELECTCOLUMNS (
    GENERATE (
        'Product',
        TOPN (
            2,
           ALLSELECTED (Customer[Name]),
            [Sales Amount]
        )
),
"Product Name",'Product'[Product Name],
"Product Key",'Product'[ProductKey],
"Sales Amount",[Sales Amount])
```

The equivalent SQL query will be something like this.

```sql
SELECT [productkey],
       [sales amount]
FROM   sales
       CROSS APPLY(SELECT TOP 2 [name]
                   FROM   customer
                   WHERE  customer.customerkey = sales.customerkey)
```

The above query is not the exact equivalent of our DAX expression, given that we don’t have a ORDER by clause and we also don’t have a join across the sales table and customer in the subquery.

Back to our DAX expression.

The issue with our earlier DAX expression is that, it behaves similar to SQL’s OUTER APPLY instead of CROSS APPLY.

With the underlying data, the DAX expression returns blank entries for products without any sales and the total number of rows for those products vary according to the number of customers in the table.

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762198015410/f954f60e-d398-4b4f-be34-fca58381e903.png align="center")

For example the ProductKey **1719** above, has no buyers so the number of rows returned for ProductKey **1719** will be equal to the number of customers that exist in the table.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">Note that in <a target="_self" rel="noopener noreferrer nofollow" href="http://DAX.do" style="pointer-events: none">DAX.do</a> the result set will not display all the resultant rows due to the following limitations.</div>
</div>

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762198820507/3d6a8751-a1ab-48d5-83b7-48b8b3944c00.png align="center")

We can add a FILTER to our expression to filter out a specific ProductName.

```css
EVALUATE
FILTER (SELECTCOLUMNS (
    GENERATE (
        'Product',
        TOPN (
            2,
           ALLSELECTED (Customer[Name]),
            [Sales Amount],
            DESC
        )
),
'Product'[Product Name],
"Customer Name",Customer[Name],
"Sales Amount",[Sales Amount]),[Product Name] == "MGS Dal of Honor Airborne M150")
```

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762289997595/2f24be41-4888-46bc-8108-e14f1f92a1e9.png align="center")

As mentioned earlier, our DAX expression behaves similar to a OUTER APPLY which isn’t desirable.

One possible approach to fix this is by filtering out the BLANK values.

```css
EVALUATE
FILTER (
    SELECTCOLUMNS (
        GENERATE (
            'Product',
            TOPN ( 2, ALLSELECTED ( Customer[Name] ), [Sales Amount],DESC )
        ),
         "Customer Name", Customer[Name],
        "Product Name", 'Product'[Product Name],       
        "Sales Amount", [Sales Amount]
    ),
    NOT ( ISBLANK ( [Sales Amount] ) )
)
```

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762290703397/0eac64f7-ebf0-42c5-9fb2-c99b8a51c0ba.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">But beware the above approach is a performance disaster as we are not controlling the filter behavior during the expression evaluation by limiting the unqualified number of rows being returned.</div>
</div>

We can overcome that limitation by introducing SUMMARIZECOLUMNS in the inner expression with TOP N.

```css
EVALUATE
SELECTCOLUMNS (
    GENERATE (
        'Product',
        TOPN ( 2, 
                SUMMARIZECOLUMNS (Customer[Name], "Sales Amount", [Sales Amount]),
                [Sales Amount], DESC)
    ),
    "Customer", Customer[Name],
    "Product Name", 'Product'[Product Name],
    "Sales Amount", [Sales Amount]
)
```

The approach is to first get a summarized list of the Sales Amount of each customer and then fetch TOP 2 Product wise Sales Amount from that list for every customer.

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762290798695/f1231f5d-862c-4b43-8619-9b463b68dbcf.png align="center")

For a sharp eyed reader it might come as a puzzle to see that we haven’t used Product Name attribute in SUMMARIZEOLUMNS.

```css
EVALUATE
SUMMARIZECOLUMNS ( Customer[Name], "Sales Amount", [Sales Amount] )
```

If we summarize the Sales Amount for a Customer we get an aggregated Sales Amount for all customers without the product wise Sales Amount

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762280983376/a192dd67-1641-4bb1-b8e2-e2a4ccac8636.png align="center")

but then why we haven’t used it our expression (marked in red below) and still get the correct totals ?

![DAX GENERATE,TOP N function](https://cdn.hashnode.com/res/hashnode/image/upload/v1762290874810/86dcc23d-8212-4dcf-9c1b-c7f33defd47b.png align="center")

In SQL APPLY clause, if both the outer and inner queries produce columns with the same name, then we have to use aliases to disambiguate them or else it will raise a ambiguous column name error. If we use aliases then they wouldn’t error out.

So looking at our DAX expression, we already have a Product Name referenced (marked in green).

We could also create an alias in our DAX expression through ADDCOLUMNS but the expression looks very convoluted

```css
EVALUATE
SELECTCOLUMNS (
    GENERATE (
        'Product',
        TOPN ( 2, ADDCOLUMNS(
                SUMMARIZECOLUMNS (Customer[Name], "Sales Amount", [Sales Amount] ),
                "Inner ProductName", 'Product'[Product Name]),[Sales Amount],DESC)
    ),
    "Customer", Customer[Name],
    "Product Name", [Inner ProductName],
    "Sales Amount", [Sales Amount]
)
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1762298867030/1f74b46c-3dd8-4b01-81f3-10b4905542a4.png align="center")

In above expression we used an alias “Inner ProductName” for the attribute “Product Name” to be referenced in the final output.

So for better readability and maintenance we can stick to our approach through SUMMARIZECOLUMNS without aliases.

```css
EVALUATE
SELECTCOLUMNS (
    GENERATE (
        'Product',
        TOPN ( 2, 
                SUMMARIZECOLUMNS (Customer[Name], "Sales Amount", [Sales Amount]),
                [Sales Amount], DESC)
    ),
    "Customer", Customer[Name],
    "Product Name", 'Product'[Product Name],
    "Sales Amount", [Sales Amount]
)
```

### Conclusion

In this article, I have tried to replicate SQL’s APPLY clause behavior in DAX and highlight the major points to consider to prevent surprising performance issues and achieve the expected output.

Thanks for reading !!!