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).
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”.
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).
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.
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.
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.
Program had generated exact copies of your selected measures. Select the copied measures (make sure they are high-lightened).
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.
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.
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.
As always, the files (PBIX, PBIT and CSV) are available at Github.
Happy Power BI-ing 🙂
[…] Tomaz Kastrun walks us through copying measures between datasets and files in Power BI: […]
LikeLike