Forum Discussion
Create table (not a pivot table) from Data Model
- May 09, 2024
Most probably you can do that. You may check
Using join functions in DAX - SQLBI
From SQL to DAX: Joining Tables - SQLBI
for the details, plus lot of other materials.
As for the Power BI not sure what do you mean. In general that's the same data model (aka dataset, semantic model) to work with which DAX queries are used; and same Power Query to prepare external data sources (dataflows) or internal one. If you have Power BI Pro or higher licenses you may do all preparations in Power BI Services creating dataflows and/or semantic model data from which will be loaded to Excel. Refresh is scheduled one or initiated by Power Automate. Or manual.
SergeiBaklan Thank you so much! I am a novice when it comes to DAX, but I did manage to successfully create a table based on inner join. But, now I need to find how to write a DAX query to pull data from all 4 tables. It has to:
1. Complete inner join with table a and b
2. Add columns from table c based on left outer join.
3. Add columns from table d based on left out join.
Is all of this easier to do in Power BI? I have requested the software from our IT Dept., but have been waiting for weeks.
One more point. If your source is SQL Server and all tables you use are in the same database, with some conditions query folding could work. That means all operations will be done on the server and you need to load only resulting table.
Alternatively you may use native database query Import data from a database using native database query - Power Query | Microsoft Learn with SQL statement within the query. With that also all transformation will be done on the server.
- TxtcherMay 10, 2024Copper ContributorThank you. Unfortunately, the data we work with comes from multiple sources, ODBC, SalesForce, and Access databases on a server. We have some Access databases that import data from all three of these sources at once.