The GENERATEALL function is similar to the GENERATE function: It returns a table formed by the Cartesian product of all the rows of the first table and the rows of the second table after being evaluated in the row context of each of the rows of the first one. The difference is that this Cartesian product will include all the rows of the first table even when the result of the evaluation of the second table for a certain row is an empty set, in which case it is added to the table to return the row of the first table and null values in the fields corresponding to the second table.
GENERATEALL(
table1,
table2
)
- table1: First table, whose rows will generate the row context.
- table2: Second table, whose rows are going to be contextualized before applying the Cartesian product.
The GENERATEALL function returns a table resulting from calculating the Cartesian product between the rows of the first table and the rows of the second table after being contextualized, even if they return an empty set.
Tables must have columns with mismatched names, otherwise the GENERATEALL function will return an error.
To see how this function works -and check the differences with the GENERATE function- we start from the following two tables:
If we impose as a condition that the names of the offices are the same, the result of applying the GENERATE function is as follows:
Tabla =
GENERATE(
Tabla1,
FILTER(
SELECTCOLUMNS(
Tabla2,
"Oficina2", Tabla2[Oficina],
"Compras", Tabla2[Compras]
);
Tabla1[Oficina] = [Oficina2]
)
)
We see that a row is not included for the Germany office (country present only in the first table), since the result of contextualizing the second table in the generated row context returns an empty set, so the result of the combination is also an empty set.
However, the result of applying the GENERATEALL function is slightly different:
Tabla =
GENERATEALL(
Tabla1,
FILTER(
SELECTCOLUMNS(
Tabla2,
"Oficina2", Tabla2[Oficina],
"Compras", Tabla2[Compras]
);
Tabla1[Oficina] = [Oficina2]
)
)
In this case, the row for Germany is included, with null values for the fields corresponding to the second table.
