GENERATE vs GENERATEALL
I will be using these two tables to demonstrate the possibilities of creating a joined table using DAX.
source:
GENERATE, GENERATEALL
Syntax
GENERATE(<table1>, <table2>)
GENERATEALL(<table1>, <table2>)
Return value
A table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1
Notes:
All column names from table1 and table2 must be different or an error is returned.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Example 1
Both function return the same result, the cross product of two tables .
Example 2
GENARATE with CALCULATETABLE
CalculateTable is a filter function
syntax:
CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])
I created a left join, means all the rows from tblSales and the matching rows from tblCost. An existing relationship must be between these two tables, otherwise the cartesian product will be returned. GENERATEALL returns the same result.
Example 3
If I would switched the table, GENERATEALL returns all the rows including non matching rows, whereas GENERATE returns just the matching rows.
Example 4
with SUMMARIZE
Generate =
SUMMARIZE (
GENERATE ( '01_tblCost', CALCULATETABLE ( '02_tblSales' ) ),
'02_tblSales'[ShipCountry],
"Revenue total", SUM ( '02_tblSales'[Revenue] ),
"Cost total", SUM ( '01_tblCost'[Cost] )
)
In this example, first I created an inner join using the GENERATE function
Example 5
with SUMX as a virtual table
Profit =
SUMX (
SUMMARIZE (
GENERATE ( '01_tblCost', CALCULATETABLE ( '02_tblSales' ) ),
'02_tblSales'[ShipCountry],
"Total sales", SUM ( '02_tblSales'[Revenue] ),
"Total expenses", SUM ( '01_tblCost'[Cost] )
),
[Total sales] - [Total expenses]
)
NATURALINNERJOIN vs NATURALLEFTOUTERJOIN
Syntax:
NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)
NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>)
Example 1
Inner Join (NATURALINNERJOIN)
There must be a relationship in the data model created, otherwise you will get an error.
Example 2
Left Join or Right Join (NATURALLEFTOUTERJOIN)
Example 3
NATURALINNERJOIN and NATURALLEFTOUTERJOIN keep all the columns. If you want to retrieve just couple of specific columns, you can use the SELECTCOLUMNS function.
Example 4
with FILTER
Left join = FILTER( NATURALLEFTOUTERJOIN('02_tblSales','01_tblCost'), '02_tblSales'[ShipCountry]="Germany")
UNION
SYNTAX:
UNION(<table_expression1>, <table_expression2> [,<table_expression>]…)
The two tables must have the same number of columns.
Columns are combined by position in their respective tables.
The column names in the return table will match the column names in table_expression1.
Duplicate rows are retained.
Example 1
UNION does the same as Append query in the power query edtor.
Example 2
with FILTER
INTERSECT
Returns the row intersection of two tables (of two columns), retaining duplicates.
Syntax:
INTERSECT(<table1>, <table2>)
Return value
A table that contains all the rows in table1 that are also in table2.
Notes
Intersect(T1, T2) will have a different result set than Intersect(T2, T1).
Duplicate rows are retained
Example 1
In this example INTERSECT function returned all the rows from table1
and matching rows from table 2
Example 2
in this example I have 3 matching rows, the fourth row has a different values in the Number column.
Example 3
I want to calculate the customers who purchased some lovely product at least for two calendar weeks in the row. There are 3 of them: John, Steve, Harry. These 3 customers have purchased something every week. Using two tables DimDates containing the continues set of dates and FactSales, a transactional table with the sales for August 2021.
Step 1
calculate the total sales.
_TotalSales = SUM(FactSales[Sales])
Step 2
calculate last calendar week sales.
SaleLastWeek =
CALCULATE (
[_TotalSales],
FILTER (
ALL ( DimDates[Week of Year] ),
DimDates[Week of Year]
= MAX ( DimDates[Week of Year] ) - 1))
John, Steve, Harry have a purchase every week in August. Those transactions have been displayed .
Step 3
I want to create a measure with two virtual tables. First table will contain all the customers and the second table contains the customers who purchased something in the previous calendar week.
Step 4
After I created these two tables, I use them in the measure as a virtual tables to mark the returning customers using some login and INTERSECT function.
Intersect =
VAR AllCustomer = VALUES ( FactSales[Customer] )
VAR CustomerLastWeek =
CALCULATETABLE (
VALUES ( FactSales[Customer] ),
FILTER (
ALL ( DimDates[Week of Year] ),
DimDates[Week of Year]
= MAX ( DimDates[Week of Year] ) - 1
)
)
RETURN
COUNTROWS ( INTERSECT ( CustomerLastWeek, AllCustomer ) )
Step 5
creating the total sales of the repeat customers.
Intersect total=
VAR AllCustomer = VALUES ( FactSales[Customer] )
VAR CustomerLastWeek =
CALCULATETABLE (
VALUES ( FactSales[Customer] ),
FILTER (
ALL ( DimDates[Week of Year] ),
DimDates[Week of Year]
= MAX ( DimDates[Week of Year] ) - 1
)
)
RETURN
CALCULATE([_TotalSales]+[SaleLastWeek],
INTERSECT ( CustomerLastWeek, AllCustomer ) )
Steve, John and Harry have an existing transaction every calendar week.
