Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jhaast
Helper II
Helper II

Access calculated table in multiple reports

Hi People,

 

Currently I'm struggling with the problem of repeating calculated tables in multiple reports.

I have used a few datasets from a SQL sever and Excel files to calculate the final dataset, which is used for the visualisations in different reports.

So dataset A + dataset B + dataset C are used to calculate the final dataset D and the final dataset D is necessary to make some visualisations in different reports.

The final dataset D is calculated with DAX / calculated tables.

However, I have to copy those dataset scripts and the calculated table inclusive the DAX measures for every report. This is a very time consuming process and results in a high risk for errors.

Does somebody know an altenative to calculate the final dataset D only once and access that dataset in multiple reports?

Thank you in advance!

6 REPLIES 6
jhaast
Helper II
Helper II

Thank you for your responses. 

I will give additional explanation of the problem with an example:

Note: all tables are imported in PBI desktop and afterwards the report is published to the PBI webservice.

The purpose of this report is to define which bonus you can expect from a vendor at the end of a year.

Table 1 (based on a SQL script)

VendorDate Turnover
vendor A1-1-2020€10000
Vendor B 1-2-2020€5000
Vendor A 1-2-2020€5000
vendor A1-3-2020€10000
Vendor B 1-4-2020€5000
Vendor A 1-3-2020€7500

 

Table 2 (Excel file):

Vendor Zone 1Zone 2Zone 3Result zone 1Result zone 2Result zone 3
vendor A€10000€20000€4000005%7,50%
Vendor B €20000€50000€70000010%11,50%

 

Calculatedtable1 (calculated table, which is created after the import of table 1 and 2 / after the query editor):

VendorTurnoverBonus
Vendor A €       32.500 €             2.438
Vendor B €       10.000 €                    -  

 

This table is created based on the following script:

CalculateTable1 = SUMMARIZE(Tabel1;Tabel1[Vendor];"Turnover";CALCULATE(sum(Tabel1[Turnover])))

 

The bonus column is a calculated column based on the following script:

Bonus = SWITCH(TRUE();
CalculateTable1[Turnover] < LOOKUPVALUE(Tabel2[Zone 1];Tabel2[Vendor];CalculateTable1[Vendor]);CalculateTable1[Turnover] * LOOKUPVALUE(Tabel2[Result zone 1];Tabel2[Vendor];CalculateTable1[Vendor]);
CalculateTable1[Turnover] < LOOKUPVALUE(Tabel2[Zone 2];Tabel2[Vendor];CalculateTable1[Vendor]);CalculateTable1[Turnover] * LOOKUPVALUE(Tabel2[Result zone 2];Tabel2[Vendor];CalculateTable1[Vendor]);
CalculateTable1[Turnover] < LOOKUPVALUE(Tabel2[Zone 3];Tabel2[Vendor];CalculateTable1[Vendor]);CalculateTable1[Turnover] * LOOKUPVALUE(Tabel2[Result zone 3];Tabel2[Vendor];CalculateTable1[Vendor]))
 
This is the simple version of the real calculated table.
 
So the problem is, for every report I have to repeat the import process of the SQL script and the Excel file and afterwards I have to recreate the calculated table and the calculated columns.
 
Is it possible to share the calculated table or the complete import process with other reports? And is it possible to also add other datasets to that second report?
I have tried the option "Power BI-datasets" in Power BI Desktop, but it is not possible to add additional datasources in the report.

Does somebody else know a solution?

wait for the release of the composite data model where you can mix import, direct query and live connections ad libitum.

Do you know when the composite data model will be released?

v-shex-msft
Community Support
Community Support

HI @jhaast,

Did these datasets has similar data structure? If this is a case, you can parameterize your connection string and save it as template.
Then you can change the connection string to use template generate the different reports with similar DAX formulas.

Deep Dive into Query Parameters and Power BI Templates 

Power BI Desktop Query Parameters, Part 1 
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

Let's make sure the terminology is clear here.

 

A .pbix file is a collection of queries that point to data sources.  

 

Together with the data model these queries combine into a dataset that you load into the workspace.

 

What you can do at this moment is share the dataset , both to reports inside the workspace/app, but also to reports in other workspaces. (assuming workspaces are "new", v2)

 

This gives you control over data acquisition, ETL, and the data model, and gives the subscribers to the shared dataset the flexibility to create their own visualizations, all off the same common dataset.

 

Next step is to promote and certify the dataset so more developers know about it or are forced to use it.

 

Next step is to use an actual data modeling tool (like CDM) to ensure cross-company consistency.

 

Yes, I know, wishful thinking. One can but dream.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors