So far there has been no great solution for moving report-level measures (RLMs) from one or multiple live connected reports into a semantic model.
With the new version of Measure Killer (v2.5 or later) you copy all RLMs from all your thin reports into your semantic model in Power BI in an easy way.
UPDATE: Jan 2025
Go to the bottom of the page to find out how to avoid visuals breaking down after replacing the measures.
We have two options:
1) Measure Killer free
Requirements:
-The dataset file needs to be open in Power BI Desktop
-All live connected reports need to be downloaded (on my machine)
2) Measure Killer Enterprise (Power BI Service)
The only requirement is a Measure Killer license.
The semantic model and all reports can remain in the Power BI Service.
Option 1)
Open Measure Killer and make sure you have the semantic model open in Power BI Desktop.
Option 2)Â Run "Shared model online" of Measure Killer if you have a license.
to follow Option 2, please scroll down
Start of Option 1)
Run mode 2
Select the semantic model (it pre-selects the file you have open)
Click Next.
To "add the pages of the semantic model" is not related to the measures so whatever you click here is fine. This just checks in our general analysis if a measure or column might be used in any report pages of your semantic model.
Now the main window (see below) will have opened up. Here you can drag&drop your thin (live connection) reports from the Power BI Service or click on "Add reports" to select them.
Once added, click on Run on the top left.
Now we will get the standard Measure Killer analysis telling us which measures and columns are used and where they are used etc.
End of option 1) -> scroll down to general part below
Start of option 2)
Run mode "Share model online" either as developer or as tenant-admin in case you have these permissions.
We will run the developer version (left) as an example.
First a window will pop up to trigger you to authenticate with your Power BI / Microsoft account
Click on the account you want to authenticate with.
Next you need to click on a workspace and then select the semantic model you want to analyze then click next.
A new window will pop up which shows the workspace where connected thin (live connection) reports were found. In my case they are all in the same workspace as the model.
Now the main window will show me all connected reports it found. You need to have at least Viewer permissions in a workspace so Measure Killer can find a report connected to your model. If you do not have workspace-level permissions you can always download the .pbix/.pbip files and add them manually here. Use "Add local files" or just drag & drop them into the window.
You can also unselect reports if you do not care about them and their report-level measures. Once you are happy with the selection, click on Run at the top left.
You now might have to authenticate again for the XMLA connection.
Now Measure Killer will get all the metadata for the model and reports you selected and then run its analysis, this usually just takes a few seconds.
Once done, the main window should be visible with all the cool output we know. Let us go to the DAX expressions tab next.
End of option 2)
General part (same for both options)
If we open the DAX expressions tab we will see our report-level measures nicely with their expressions (when expanded).
the DEFINE statement that Measure Killer will generate will look like the code below:
I took out some of those measure references since they had an empty expression.
DEFINE
MEASURE '_Measures'[_AverageAge_copy] = 2023 - AVERAGE( people[birthYear] )
MEASURE 'ratings'[Measure 2_copy] = MAX( ratings[tconst] )We can run this code in the browser if you open the semantic model in the Power BI Service and click on "Write DAX queries"
If the "Write DAX queries" button is greyed out, make sure to have your workspace admin turn the setting above.
Now we can continue and paste the code from Measure Killer into the query view.
Just click on "Update model with changes" so the measures will be added to your semantic model.
UPDATE: Jan 2025
The issue until now was that all our visuals would break and we have to put the model measures into the visuals axis or whatever manually to fix everything.
There is way to do this quickly using the Enhanced Report Layout (.pbir).
This is a preview feature that you need to turn on in Power BI Desktop and you have to save your thin reports as a Power BI project (.pbip) for this to work.
I am using a new report and model to showcase this.
Open the .pbip report folder in any text editor like Visual Studio Code, even the notepad will suffice.
You need to have all the folders of your thin reports (live connection) on your machine.
We also need to have the update to our semantic model ready (last step with screenshot above).
As soon as we add the report-level measures to the semantic model, our reports will break so we have to keep the downtime to a minimum, so be careful and follow this guide step by step.
First, let us change the thin reports locally:
We need to change the code in this folder for two areas:
1) Removing the reportExtensions.json (in the definition folder)
You can do this if you want to take out all report-level measures, if you only want to remove part of them make sure to adjust the .json file accordingly.
2) Go into the definition -> pages - visuals folder and for each visual that will be affected we need to adjust that visual's .json file.
To find out which visuals will be affected, it is another good use case for Measure Killer. Run the tool to see where all report-level measures are used, like shown in the screenshot below.
I can see that I only need to adjust the table and pivotTable (matrix) on page 1 because my 4 report-level meaures are only used there.
Now I will go into those files like shown above (beginning of step 2) and remove every measure where it says
"Schema": "extension",
I will do a find + replace all and just replace it with nothing -> then I save my changes.
3) Now I will add the report-level measures to the model (we saw this earlier via DAX query view or any other way).
As soon as you do this, your thin reports in the Power BI Service will break down, so make sure you will be able to do step 4) and 5) quickly after that.
4) I will now open my local thin report where I changed the code. They should all open correctly without any visuals breaking.
The affected visuals of the reports in the Power BI Service at this point are broken though so we need to re-publish the local files (those have the code changed applied) as soon as possible.
5) Now we just need to publish the local Power BI files (thin reports) where we did our changes and all reports in the Power BI Service will be fixed.
Comments