Forum Discussion
Generate Table with dates and ID from another table
Hello!
I have a table called 'Organizations' where the primary key is the 'Organization_ID'. I also have a dates table that is using the calendar function.
I would like to generate a table that has a row for every Month in my Dates table for every Organization_Id in my org table.
Sample of my Organization_Table:
Sample of my Dates Table:
Desired Outcome:
The reason I need a month row for every organization is because I plan on relating a table to this table that I will use to bring in some monthly totals.
How would I accomplish this?
Anonymous
Sounds good. You could also do
Table=CROSSJOIN(ALL(Organizations[Id]); ALL('Date'[MonthYear]))
3 Replies
Hi Anonymous
Do you want the cartesian product of Month and Organization_ID?
Hi AlB ,
I apologize, but I do not know what a carterian product is.I believe I came up with a solution that works since posting the question, though. I created two tables. The first table is called ORGID and is just a SUMMARIZE function of the OrganizationID:
OrgID = SUMMARIZE(Organizations, Organizations[Id])
I then created another dates table as a variable and return just the months:
2018 = var FullCalendar = ADDCOLUMNS ( CALENDAR (DATE(2018,01,01), DATE(2018,12,31)), "JoinDate", FORMAT([Date], "MM-DD-YYYY"), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "MonthYear", FORMAT ( [Date], "MM-YYYY" ), "MonthYearShort", FORMAT ( [Date], "mmm-YYYY" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "QuarterNumber", FORMAT([Date], "Q"), "QuarterYear", "Q" & FORMAT ( [Date], "Q") & "-" & FORMAT ( [Date], "YYYY" ) ) return SUMMARIZE(FullCalendar,[Year], [MonthYear], [Monthnumber], [MonthNameShort], [MonthYearShort])
I then used GenerateALL to combine them, and ended up with a table that has month per row for every organizationID:
Anonymous
Sounds good. You could also do
Table=CROSSJOIN(ALL(Organizations[Id]); ALL('Date'[MonthYear]))