Forum Discussion

aar0n's avatar
aar0n
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Merging Two tables, one that has a continuous date column, and one that has a discrete date column

Hey guys,

 

I need to merge two tables in dax.. my "Online Date Table" Looks something like below..
Online Date:

NameOnline DateLocation
aaa1/1/2010x
aaa5/1/2012y
bbb4/1/2015x
ccc1/1/2011x
ccc3/1/2011 
ccc6/1/2011y

 

My Production Table looks like something below

 

Production Table:

NameValueDate
aaa101/1/2010
aaa22/1/2010
aaa43/1/2010
………
ccc51/1/2011
ccc22/1/2011
ccc33/1/2011
ccc74/1/2011
ccc55/1/2011
ccc46/1/2011
ccc27/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:

NameValueDateOnline DateLocation
aaa101/1/20101/1/2010x
aaa22/1/20101/1/2010x
aaa43/1/20101/1/2010x
……………
ccc51/1/20111/1/2011x
ccc22/1/20111/1/2011x
ccc33/1/20113/1/2011 
ccc74/1/20113/1/2011 
ccc55/1/20113/1/2011 
ccc46/1/20116/1/2011y
ccc27/1/20116/1/2011y
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      aar0n
      Icon for Advocate II rankAdvocate II
      8 years ago

      sorry for the late reply, but thank you very much! this is exactly what i was trying to do!