Forum Discussion
Making a reationship without making a huge table
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
Here is the data based on the date range concept. See this file.
What I did is this:
- In your 2nd table, I called Activity, I added a list of dates between Start and Finish dates, then expanded that.
- In the 1st table, called Table, I merged the Activity and Date to the Date Range
- Then I expanded the efficiency field.
- I made sure Activity was not set to load
- 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
3 Replies
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?
Here is the data based on the date range concept. See this file.
What I did is this:
- In your 2nd table, I called Activity, I added a list of dates between Start and Finish dates, then expanded that.
- In the 1st table, called Table, I merged the Activity and Date to the Date Range
- Then I expanded the efficiency field.
- I made sure Activity was not set to load
- 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 ForumThat'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.
