How to get the Column value from one table to another comparing on date ranges

[Anonymous]

Hi, Can some one help on how to get the Column value from one table to another comparing on date ranges and there is no relationship between these two table. For example there are two different tables which should look like below

TABLE_A - 

UID	Start_Date	End_Date
ID001	12/17/2017	12/30/2017
ID002	12/31/2017	1/13/2017
ID003	1/14/2018	1/27/2018
ID004	1/28/2018	10/2/2018
ID005	11/2/2018	2/24/2018

TABLE_B

Date

1/15/2018

1/1/2016

12/30/2017

1/1/2016

1/1/2016

Also I came accross with this solution article but it is for measure value, I am confused now and not sure if I am missing something

(https://community.powerbi.com/t5/Desktop/Best-way-to-use-a-Date-value-from-one-table-to-look-up-anot...)

Requirement/Expected results should look like this:

Date	DAX COLUMN	(Please IGONRE this column, this is just for refernce)
1/15/2018	ID003	This date falls between TABLE_A record 3
1/1/2016
12/30/2017	ID001	This matches record 1 on TABLE_A and fetches the UID
1/1/2016
1/1/2016

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this column

DAX Column =
CALCULATE (
    FIRSTNONBLANK ( Table_A[UID], 1 ),
    FILTER (
        Table_A,
        Table_A[Start_Date] <= Table_B[Date]
            && Table_A[End_Date] >= Table_B[Date]
    )
)

[Zubair_Muhammad · Accepted solution]

@Anonymous

Another way . This is more appropriate... in case you might have more than one ID for a date

Dax Column =
CONCATENATEX (
    FILTER (
        Table_A,
        Table_A[Start_Date] <= Table_B[Date]
            && Table_A[End_Date] >= Table_B[Date]
    ),
    Table_A[UID],
    ", "
)

[Ashish_Mathur]

Hi @Anonymous,

There could be another way of solving it.  Please confirm if you will have duplicate date entries in Table B.  You have show the second last one being repeated in Table B.  Can that actually be the case?

[Anonymous · Accepted solution]

@Zubair_Muhammad Awsome! Appreciate your quick response and also providing appropriate suggestions with multiple options. For me currently there are no multiple IDs and the 1st solution worked like charm, but 2nd one is a good start for me to impliment best standards and a learnign too.

Thanks!

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this column

DAX Column =
CALCULATE (
    FIRSTNONBLANK ( Table_A[UID], 1 ),
    FILTER (
        Table_A,
        Table_A[Start_Date] <= Table_B[Date]
            && Table_A[End_Date] >= Table_B[Date]
    )
)

[Zubair_Muhammad · Accepted solution]

@Anonymous

Another way . This is more appropriate... in case you might have more than one ID for a date

Dax Column =
CONCATENATEX (
    FILTER (
        Table_A,
        Table_A[Start_Date] <= Table_B[Date]
            && Table_A[End_Date] >= Table_B[Date]
    ),
    Table_A[UID],
    ", "
)