web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Joining multiple data ...
Finance | Project Operations, Human Resources, ...
Unanswered

Joining multiple data sources to make a customized entity

(0) ShareShare
ReportReport
Posted on by Roohan Amir - BI De... 25

Hi
I'm currently working to translate current views in SQL, made from D365 FO tables (on-prem) into entites for our transition to cloud instance.

Normally, I would create entities, get their data in Power BI, make joins and get the desired results.
Would it be possible to make joins within an entity created, say 7 datasources, so it may save processing time in Power BI?

I have the same question (0)
  • Roohan Amir - BI Developer Profile Picture
    Roohan Amir - BI De... 25 on at

    Can you explain explicitly?

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,565 Moderator on at

    You can add datasource ranges and table relations for where clause and group by is also available in data entity.

  • Roohan Amir - BI Developer Profile Picture
    Roohan Amir - BI De... 25 on at

    I see

    What about translating WHERE and GROUP BY clause? Is it possible?

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,565 Moderator on at

    Hi, I can't tell all limitations but there are certain SQL functions which can't be applied in Data entity. For instance, keyword COALESCE, you can do use aggregation functions in select statement but can't apply same in Data entity directly. For that, computed column has to be created.

    This is the SQL Script that I used to convert into Data entity.

    pastedimage1683866870671v2.png

  • Roohan Amir - BI Developer Profile Picture
    Roohan Amir - BI De... 25 on at

    Thank you

    Can you tell me specific limitation(s)?

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,565 Moderator on at

    Hi Roohan, I have done exactly same task where I got SQL queries having multiple tables and created data entity based on those SQl queries adding datasources,  ranges etc. You  can do it and to ensure the data entity is created correctly, you can execute SQL queries/views and your custom data entity in SQL Server and check if number of records are matching, all fields have same data (check for few records)

  • Anton Venter Profile Picture
    Anton Venter 20,410 Super User 2025 Season 2 on at

    Yes and there any examples in the standard application (data entities) that do this. You have to keep in mind however that there are limitations in Dynamics 365 Finance in making joins, grouping etc. You do not have all the same possibilties as SQL Server query lanugage.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Abhilash Warrier Profile Picture

Abhilash Warrier 867 Super User 2025 Season 2

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 514 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 348 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans