cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trist
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
ı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,

 

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors