Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

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 - 

UIDStart_DateEnd_Date
ID00112/17/201712/30/2017
ID00212/31/20171/13/2017
ID0031/14/20181/27/2018
ID0041/28/201810/2/2018
ID00511/2/20182/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-another/m-p/242294#M107546)

 

Requirement/Expected results should look like this:

DateDAX COLUMN (Please IGONRE this column, this is just for refernce)
1/15/2018ID003This date falls between TABLE_A record 3
1/1/2016  
12/30/2017ID001This matches record 1 on TABLE_A and fetches the UID
1/1/2016  
1/1/2016  
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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],
    ", "
)
  • Anonymous's avatar
    Anonymous
    8 years ago

    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!

     

     

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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],
    ", "
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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!

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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?