cancel
Showing results for
Did you mean: Frequent Visitor

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 ı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,

2 ACCEPTED SOLUTIONS  Super User

Hi, @Trist

Please try to create the mode like below, and write the calculated measure like 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.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim  Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
2 REPLIES 2  Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com  Super User

Hi, @Trist

Please try to create the mode like below, and write the calculated measure like 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.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim  