Copying Measures between Power BI datasets and Power BI files

Measures in Power BI document are DAX based aggregations for slicing and dicing the tabular datasets using evaluate and filter context and and data relationships. Short hand, measures are DAX formulas stored in Power BI as part the dataset.

For showing how easy (once you know) is to copy measures between different dataset, I have created a sample Power BI with diamonds dataset (also available on Github).

2020-02-11 21_17_59-

Measures have little calculators in front of the name: denoting its purpose.Now let’s do the copying part 🙂

1.Copy dataset

The first part we need to do, is to copy dataset in Power BI. There are many ways to copy a dataset, I have chosen the following way:

Go to Modeling -> Select New Table -> and and simply write in the DAX line the name of the dataset; Table = diamonds. I have later renamed the new dataset from “Table” to “diamonds_2”.

2020-02-11 21_18_59-Normal_PBI_with_Measures - Power BI Desktop

You will notice that none of the measures have had been copied, leaving the new dataset free of measures. So now, we need to get the measures in the new dataset.

2. Save as PBIT

The second step is to save the Power BI document as Template.  Go to File -> Save As -> and select Save as Type: Power BI Template File (*.pbit).

2020-02-11 21_20_04-Normal_PBI_with_Measures - Power BI Desktop

The biggest difference between your normal PBIX file and PBIT – template file – is, that the latter does not hold data. But what does PBIT file hold is :

  • Query definitions, query parameters, M and DAX language
  • Holds all information about data model definition
  • preserves all the visuals, elements, bookmarks, filters, etc.

And it can be easily shared in collaborative environment, since it is usually very small file.

3. Open Tabular Editor

Now, download the Tabular Editor from Github. It is a small but powerful program, and once the installation process has completed, open the program,  and open PBIT (template) file, saved in previous step.

2020-02-11 21_21_02-Tabular Editor 2.9.1

You will be presented with semantic description of your dataset, model and all the definitions. You can see, there are two datasets, one holding measures and the other one not.

2020-02-11 21_23_30-C__DataTK_git_DAX_Functions_03_Measures_Copy_Normal_PBI_with_Measures.pbit - Tab

4. Copy measures

Once in Tabular Editor, select all (they should be high-lightened)  the measures you want to copy and right-click on the mouse and select Duplicate 3 measures.

2020-02-11 21_23_47-C__DataTK_git_DAX_Functions_03_Measures_Copy_Normal_PBI_with_Measures.pbit - Tab

 

Program had generated exact copies of your selected measures. Select the copied measures (make sure they are high-lightened).

2020-02-11 21_24_08-C__DataTK_git_DAX_Functions_03_Measures_Copy_Normal_PBI_with_Measures.pbit_ - Ta

And now simply drag them into the new dataset (dataset: diamonds_2).

Alternatively, you can use CTRL + C and CTRL + V (copy/paste) and delete copies in original dataset. Both ways are very straight-forward.

2020-02-11 21_24_36-C__DataTK_git_DAX_Functions_03_Measures_Copy_Normal_PBI_with_Measures.pbit_ - Ta

 

5. Editing the measures

Last touches to be done is, to change the datasets for each of the measures, yes manually.  For each of the measures, under the expression, I need to change the dataset from diamonds -> diamonds_2.

2020-02-11 21_27_11-C__DataTK_git_DAX_Functions_03_Measures_Copy_Normal_PBI_with_Measures.pbit_ - Ta

Or to put it in DAX perspective; from:

IF(SUM(diamonds[price])>1000000 ,SUM('diamonds'[price])*1.2, 
SUM('diamonds'[price]))

to:

IF(SUM(diamonds_2[price])>1000000 ,SUM('diamonds_2'[price])*1.2, 
SUM('diamonds_2'[price]))

 

And so you are finished.  If you want to do bulk export of all the measures, I strongly recommend to use DAX Studio.

Comparing the original and copied measure definitions, you will see that the copied process added some brackets.

2020-02-11 21_32_36-Presentation1 - PowerPoint

 

As always, the files (PBIX, PBIT and CSV) are available at Github.

 

Happy Power BI-ing 🙂

 

Tagged with: , , , , , , ,
Posted in Uncategorized
One comment on “Copying Measures between Power BI datasets and Power BI files
  1. […] Tomaz Kastrun walks us through copying measures between datasets and files in Power BI: […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Programs I Use: SQL Search
Programs I Use: R Studio
Programs I Use: Plan Explorer
Programs I use: Scraper API
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

€2.00

Top SQL Server Bloggers 2018
TomazTsql

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

tenbulls.co.uk

attaining enlightenment with sql server, .net, azure devops, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

R-bloggers

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by hundreds of R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

DevOps could be the disease you die with, but don’t die of.

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

%d bloggers like this: