Skip to main content

What is the difference between GENERATE and CROSSJOIN

Table of Contents

RANK and RANKX should be not taken as synonyms with functions like SUM or SUMX, and there is a significant difference between them. Just like other functions such as WINDOW, OFFSET, and INDEX, it speaks the language of relationships, ordering, and partitioning similarly. RANK, which came into being in April 2023, is meant especially to be used in measures.

On the contrary, RANKX is a relatively older function in Power BI that can be used only with measures, hence, it lacks this versatility.

RANK: Ties are ranked in a blank. RANK is used by the current context to rank within a given partition by the sort order provided. If there is no match found, blank is returned for the rank. The default order for RANK is ascending.
Although RANK does not directly replace RANKX, going forward it is the preferred choice because it corrects the tie and blank handling issues.

CROSSJOIN Function:

CROSSJOIN returns a table that contains the Cartesian product of all rows from all tables provided as arguments.
The resulting table includes all the columns from all the argument tables.

When to Use GENERATE vs. CROSSJOIN:

Use GENERATE when you want to apply filters or evaluate expressions for each row in an existing table. For instance, generating dates between a start and end date for each row is more efficiently done with GENERATE.

Use CROSSJOIN when you want to create a table that displays all possible combinations of values from two or more tables, without applying any specific filters or expressions.

However, in the following case, CROSSJOIN may not be the best choice:

In summary, GENERATE is preferable when you need to apply filters or expressions, while CROSSJOIN is suitable for generating tables that represent all possible combinations of values from multiple tables, without additional filtering or evaluation.

Add comment