take data from another table

[Trist]

Hi Everybody, How are you? 

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

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

question:

dımensıon table
ıd	first_date	last_date	numbers
1	10.1.2021	22.1.2021	1
1	22.1.2021	23.2.2021	5
1	23.2.2021	30.3.2021	8
2	13.1.2021	20.2.2021	8
2	20.2.2021	30.5.2021	10

fact table
ıd	date	numbers
1	25.1.2021	?
2	30.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
ıd	date	numbers
1	25.1.2021	5
2	30.2.2021	10

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,

[Jihwan_Kim · Accepted solution]

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] )

)

)

https://www.dropbox.com/s/ph7bu6qmfetnrxf/trist.pbix?dl=0 

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 · Accepted solution]

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.

[Jihwan_Kim · Accepted solution]

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] )

)

)

https://www.dropbox.com/s/ph7bu6qmfetnrxf/trist.pbix?dl=0 

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.