Forum Discussion

Trist's avatar
Trist
Frequent Visitor
5 years ago
Solved

take data from another table

Hi Everybody, How are you? 

ı am new at power bi and have question. ı am waiting for your helps.

-----------------------------

question:

dımensıon table
ıdfirst_datelast_datenumbers
110.1.202122.1.20211
122.1.202123.2.20215
123.2.202130.3.20218
213.1.202120.2.20218
220.2.202130.5.202110

 

fact table
ıddatenumbers
125.1.2021?
230.2.2021?

 ı have two table which has connection each other by ID column.  What ı want to do is, bring "numbers" value from dımensıon table which row 'fact_table'.[date] between 'dim_table'.[first_Date] and 'dim_table'.[last_Date]. 

 

so target will be like this: 

fact table
ıddatenumbers
125.1.20215
230.2.202110

how to work- >for first row(ıd=1) ->

firstly look at the ıd column and then go to dımensıon table, fınd the ıd=1 rows  and then date=25.1.2020 and check to rows which  25.1.2021 is between first date and last date and then fınd to correct row and take to number value which is '5' . ı want to do this . 

how to do that?

 ı really need help.

Thanks,

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Trist 

    Please try to create the mode like below, and write the calculated measure like below.

    Please check the sample pbix file, link down below.

     

     

    Find Numbers =
    CALCULATE (
    SELECTEDVALUE ( 'dimension table'[numbers] ),
    FILTER (
    'dimension table',
    'dimension table'[last_date] >= SELECTEDVALUE ( 'fact table'[date] )
    && 'dimension table'[first_date] <= SELECTEDVALUE ( 'fact table'[date] )
    )
    )
     
     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    In Table2, write this calculated column formula

    =calculate(sum(table1[numbers]),filter(table1,table1[first_date]<=earlier(table2[date])&&table1[last_date]>=earlier(table2[date])))

    Hope this helps.

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Trist 

    Please try to create the mode like below, and write the calculated measure like below.

    Please check the sample pbix file, link down below.

     

     

    Find Numbers =
    CALCULATE (
    SELECTEDVALUE ( 'dimension table'[numbers] ),
    FILTER (
    'dimension table',
    'dimension table'[last_date] >= SELECTEDVALUE ( 'fact table'[date] )
    && 'dimension table'[first_date] <= SELECTEDVALUE ( 'fact table'[date] )
    )
    )
     
     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    In Table2, write this calculated column formula

    =calculate(sum(table1[numbers]),filter(table1,table1[first_date]<=earlier(table2[date])&&table1[last_date]>=earlier(table2[date])))

    Hope this helps.