Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey guys,
I need to merge two tables in dax.. my "Online Date Table" Looks something like below..
Online Date:
Name | Online Date | Location |
aaa | 1/1/2010 | x |
aaa | 5/1/2012 | y |
bbb | 4/1/2015 | x |
ccc | 1/1/2011 | x |
ccc | 3/1/2011 | |
ccc | 6/1/2011 | y |
My Production Table looks like something below
Production Table:
Name | Value | Date |
aaa | 10 | 1/1/2010 |
aaa | 2 | 2/1/2010 |
aaa | 4 | 3/1/2010 |
… | … | … |
ccc | 5 | 1/1/2011 |
ccc | 2 | 2/1/2011 |
ccc | 3 | 3/1/2011 |
ccc | 7 | 4/1/2011 |
ccc | 5 | 5/1/2011 |
ccc | 4 | 6/1/2011 |
ccc | 2 | 7/1/2011 |
What i need to do is to merge both, so that i can see the "Online Date" and "Location" inside of my Production Table. i need to have that value show up for every "Name" and "Date" up until a new "Online Date" exceeds the True "Date" column.
for example,
Example of Merged Table:
Name | Value | Date | Online Date | Location |
aaa | 10 | 1/1/2010 | 1/1/2010 | x |
aaa | 2 | 2/1/2010 | 1/1/2010 | x |
aaa | 4 | 3/1/2010 | 1/1/2010 | x |
… | … | … | … | … |
ccc | 5 | 1/1/2011 | 1/1/2011 | x |
ccc | 2 | 2/1/2011 | 1/1/2011 | x |
ccc | 3 | 3/1/2011 | 3/1/2011 | |
ccc | 7 | 4/1/2011 | 3/1/2011 | |
ccc | 5 | 5/1/2011 | 3/1/2011 | |
ccc | 4 | 6/1/2011 | 6/1/2011 | y |
ccc | 2 | 7/1/2011 | 6/1/2011 | y |
Solved! Go to Solution.
@aar0n,
Create new table using DAX below.
Merge = GENERATEALL ( Production, VAR Tdate =Production[Date] RETURN SELECTCOLUMNS ( CALCULATETABLE ( 'Online Date', 'Online Date'[online date] = Tdate ), "online date",'Online Date'[online date], "Location", 'Online Date'[location] ) )
Create the following columns in the new table below.
New onlinedate = IF ( Merge[online date]= BLANK (), CALCULATE ( LASTNONBLANK ( Merge[online date], Merge[online date] ), FILTER ( ALLEXCEPT ( Merge, Merge[Name] ), Merge[Date] <= EARLIER ( Merge[Date]) ) ), Merge[online date] )
New Location = IF ( Merge[Location] = BLANK (), CALCULATE ( LASTNONBLANK ( Merge[Location], Merge[Location] ), FILTER ( ALLEXCEPT ( Merge, Merge[Name] ), Merge[Date] <= EARLIER ( Merge[Date])&&Merge[New onlinedate]=EARLIER(Merge[New onlinedate]) ) ), Merge[Location] )
Regards,
Lydia
@aar0n,
Create new table using DAX below.
Merge = GENERATEALL ( Production, VAR Tdate =Production[Date] RETURN SELECTCOLUMNS ( CALCULATETABLE ( 'Online Date', 'Online Date'[online date] = Tdate ), "online date",'Online Date'[online date], "Location", 'Online Date'[location] ) )
Create the following columns in the new table below.
New onlinedate = IF ( Merge[online date]= BLANK (), CALCULATE ( LASTNONBLANK ( Merge[online date], Merge[online date] ), FILTER ( ALLEXCEPT ( Merge, Merge[Name] ), Merge[Date] <= EARLIER ( Merge[Date]) ) ), Merge[online date] )
New Location = IF ( Merge[Location] = BLANK (), CALCULATE ( LASTNONBLANK ( Merge[Location], Merge[Location] ), FILTER ( ALLEXCEPT ( Merge, Merge[Name] ), Merge[Date] <= EARLIER ( Merge[Date])&&Merge[New onlinedate]=EARLIER(Merge[New onlinedate]) ) ), Merge[Location] )
Regards,
Lydia
sorry for the late reply, but thank you very much! this is exactly what i was trying to do!