Forum Discussion
N_R_000
4 years agoHelper I
Merging Tables with no shared columns
I need to 'merge' 2 tables in BI, using DAX only to create a new table, containing every row from each of the 2 tables.
Table 1 is a DAX Dates table, dynamically populated with the following:
Ref_Dates =
VAR MinDate = YEAR(MIN(Tasks[ProcessedDate]))
VAR MaxDate = YEAR(MAX(Tasks[ProcessedDate]))
RETURN
ADDCOLUMNS(
FILTER(
CALENDARAUTO(),
YEAR([Date]) >= MinDate &&
YEAR([Date]) <= MaxDate),
"Year", YEAR( [Date]),
"Month Year", FORMAT([Date], "mmm yyyy"),
"Month Name", FORMAT([Date], "mmmm"),
"Month Number", MONTH([Date] ),
"Day Name",
VAR Wd = WEEKDAY([Date], 2)
RETURN DATE (1900, 1, 7 + Wd + (7 * (Wd < 1))),
"Day of Month Number", DAY([Date]),
"IsWeekday", IF(WEEKDAY([Date], 2) IN {6,7}, 0, 1),
"Date as Text", FORMAT([Date], "dd mmm yyyy"),
"Start of Month", DATE(YEAR([Date]), MONTH([Date]), 01),
"Start of Week", [Date]-WEEKDAY([Date],2)+1
)
Table 2 is a users table, looks like this:
How can I merge the 2 tables, to end up with this:
Hi N_R_000 ,
You could use crossjoin:
Table 2 = CROSSJOIN('Table',SELECTCOLUMNS(Ref_Dates,"Date",Ref_Dates[Date]))Did I answer your question? Mark my post as a solution!
Best RegardsLucien
2 Replies
- HotChilliCommunity Champion
In DAX , use the GENERATE function.
- v-luwang-msftCommunity Support
Hi N_R_000 ,
You could use crossjoin:
Table 2 = CROSSJOIN('Table',SELECTCOLUMNS(Ref_Dates,"Date",Ref_Dates[Date]))Did I answer your question? Mark my post as a solution!
Best RegardsLucien