topic Re: Access calculated table in multiple reports in Developer

Access calculated table in multiple reports

jhaast — Wed, 01 Jul 2020 14:04:38 GMT

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!

Re: Access calculated table in multiple reports

lbendlin — Wed, 01 Jul 2020 20:35:58 GMT

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.

Re: Access calculated table in multiple reports

Anonymous — Thu, 02 Jul 2020 07:37:56 GMT

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

Re: Access calculated table in multiple reports

jhaast — Thu, 02 Jul 2020 11:16:42 GMT

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)

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

Table 2 (Excel file):

Vendor	Zone 1	Zone 2	Zone 3	Result zone 1	Result zone 2	Result zone 3
vendor A	€10000	€20000	€40000	0	5%	7,50%
Vendor B	€20000	€50000	€70000	0	10%	11,50%

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

Vendor	Turnover	Bonus
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.

Re: Access calculated table in multiple reports

jhaast — Tue, 07 Jul 2020 06:10:17 GMT

Does somebody else know a solution?

Re: Access calculated table in multiple reports

lbendlin — Tue, 07 Jul 2020 11:41:14 GMT

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

Re: Access calculated table in multiple reports

jhaast — Tue, 07 Jul 2020 12:15:06 GMT

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