Making a reationship without making a huge table

[Ricardo_Schmidt]

Hello everyone,

I'm starting using Power BI now and maybe my doubt is not that hard, but I need some help. Anyway I'm learning.

I have a table like this one that I will use to make my graphs.

Date	Number	Activity	Horas
28/01/2004	223638	A	4,5
07/11/2003	243451	A	2,5
30/04/2008	275816	A	9
05/03/2001	587967	B	8,37
20/05/2005	589254	B	9
06/08/2017	590244	C	9
06/06/2001	551066	C	5
05/03/2001	590684	C	5
22/09/2008	590687	A	9
14/11/2017	223280	A	1,5
16/08/2004	591977	B	9
05/02/2009	594034	B	3
05/04/2009	590043	B	3,5
09/12/2001	593646	C	7,87

And I have another table that brings me the efficiency of te activities during a period (it changes with time).

Activity	Beginning Date	Finish Date	Efficiency
A	01/03/1997	30/06/2000	0,88
A	01/07/2000	31/03/2004	0,91
A	01/04/2004	30/09/2006	0,8822
A	01/10/2006	01/01/2020	0,91
B	01/10/2006	01/01/2020	0,91
B	01/01/1980	28/02/1997	0,86
B	01/03/1997	30/06/2000	0,88
B	01/07/2000	31/03/2004	0,88
B	01/04/2004	30/09/2006	0,85
C	01/01/1980	28/02/1997	0,88
C	01/03/1997	30/06/2000	0,9
C	01/07/2000	31/03/2004	0,91
C	01/04/2004	30/09/2006	0,8672
C	01/10/2006	01/01/2020	0,895
D	01/08/2011	01/01/2020	0,88

I just need to put the efficiency in the first table using the activity and the dates condition. I could make a huge table with all the dates and activities or a very large IF condition. Is there an easier way to make this relation?

Thank you guys

[edhans]

How do you want to merge the tables, Based on the start date, end date, or in between the dates? And if in between, what about duplicates? Or are you sure there can be none?

[edhans · Accepted solution]

Here is the data based on the date range concept. See this file.

What I did is this:

1. In your 2nd table, I called Activity, I added a list of dates between Start and Finish dates, then expanded that.
2. In the 1st table, called Table, I merged the Activity and Date to the Date Range
   1. 
3. Then I expanded the efficiency field.
4. I made sure Activity was not set to load
   1. 
5. Made sure Table was set to load.

This is what is now in the data model.

If that isn't what you want, be very very clear in your follow up. THanks!

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Ricardo_Schmidt]

That's exactly what I need. Between the dates, with the reference being the activity and the date from table 1.

I was unable to use Power BI because computer problems, but I'll try this solution now.

Thanks a lot.