Adding a table multiple times to the same dataset

[KevinSJC]

I need a way to query data from one common table multiple times, looking for different results. Yes, I could use a subreport, but for other reasons, it won't help. Here's a example of what it is I'm trying to do. 

• Table 1: CourseRegistrations
• Table 2: People

I have one table for course registrations where the student ID, the faculty ID and the coach ID are all stored in each record as foreign keys. All foreign keys point to the People table, as it houses all students, staff, and faculty. To pull a record for each student with the faculty member instructing the course, and the coach involved for whatever reason, I need that table included 3 times in the dataset to pull the needed data.

In Crystal Reports (CR), I could include the "People" table 3 times as I would access information about students, faculty, and coaches from it. CR would rename each table instance with aliases that I could even rename, all originating from one table, People. For example:

• Table 1: CourseRegistrations
• Table 2: People_Student
• Table 3: People_FacultyMember
• Table 4: People_AthleticsCoach

In CR to pull data, I'd simply join registration's foreign keys to the People aliases to pull names for the 3 types I needed for each record sent to the report. I have yet been able to find a similar function in Report Builder to accomplish the same results. 

Any thoughts?

Thanks, Kevin

[v-janeyg-msft]

Hi, @KevinSJC 

For your needs, the easiest way to implement should be to connect the model in the desktop first, then create the relationship, and the corresponding table, then copy the query to the report builder.

You can patiently check the following documents, which are very detailed:

Reference:

Create a paginated report with a Power BI shared dataset - Power BI Report Builder - Power BI | Micr...

Solved: Paginated report - nested tables with different da... - Microsoft Power BI Community

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[KevinSJC · Accepted solution]

Hi!

I figured it out on my own. I tripped over the LOOKUP function and played around with it. I was able to use it to solve my problem. Thanks all for your help!

Kevin

[KevinSJC]

Hi Janey!

I now question if I'm in the correct forum as I don't use Power BI. I am developing reports on SSRS using MS Report Builder. When I went to their help section looking for documentation, there was a community link that brought me here. I just figured Power BI must be a rebranding campaign, but I'm not so sure based on yours and the other response I received. Is there a better forum I should be in for MS Report Builder?

[KevinSJC]

Hi!

I'm a little confused by your question as the data is coming from an SQL DB. The issue  with 4 datasets is that I cannot combine data from different datesets into one common table of results, thus why I am stuck.

Please let me know if I missed something!

Thanks, Kevin

[Les111]

If you have the data in SQL then it may be easiest to use a SQL query to structure your data first, so you could build it into one query and use this in power bi. 

You can also merge queries in power query to combine multiple datasets into one, if you prefer.

[Les111]

You don't say where your data is coming from, so it's difficult to advise exactly what you need, but what is stopping you from creating 4 datasets - registrations, students, faculties and coaches?