Skip to main content

How to create a Measures table in Power BI

If you've learnt Power BI organically and developed your skills using 'real world' applications you've probably learnt a couple of things the hard way. One thing I realised on my journey with PowerBI was that its very easy to create many measures and then end up with a model that’s difficult to manage and maintain. Initially, I would create measures and place them in the table that felt like the best place to store them, this worked for a while but inevitably didn’t quite work. I also tried putting a 'special' character at the start of the name, in my case a # symbol, to easily differentiate a measure from a column, this also didn’t quite work. After doing some research, I found the best solution to this issue was to create a Measures table and put all of my measures in this table. In this post, I'll show you how I created the measures table.

 

There are a couple of methods to create a measures table, the most obvious one is to create a 'table' using excel or notepad and then import this into the data model and then store all the measures within this table. This is fine but there are easier methods.

 

Creating a table using 'Enter Data'

In the Power BI ribbon there is a button labelled 'Enter data' with tooltip 'Create a new table by typing or pasting in new content'. This option allows you to type or paste data into a new table that is then submitted to the data model. It's an (almost) a one-shot process where the data is entered and then available in the model.

If you click this button it'll display a simple interface which will allow you to type (or paste) some data. For the purpose of creating a new table to host measures, the only required pieces of information are a column title and a table name. I'd suggest changing the table name to 'AllMeasures' and the column name to 'blank'. Once you've done this, click 'Load' and after a short moment, you'll see the new table available in the fields pane in the Power BI window.



Creating a table using DAX

My preferred method is one that I discovered from Phil Seamark from the Customer Advisory Team at Microsoft when he tweeted about it some time back. I'd recommend checking out his Twitter feed for some great PowerBI related content!

The method is to create a new table using a single line of DAX via the 'Modeling' then 'new table' links in the ribbon. Once you've clicked this the formula editor text bar will appear and you'll need to enter the following line of DAX code.

AllMeasures  = {Blank()}

This will now create a new table called 'AllMeasures' that you can add additional measures to. 





There are a couple of things to note with this method

  1. If you want your table to appear at the top of the field list, you can add a space to the start of the tble name.
  2. If you want to title your table Measures this is possible by using square brackets before and after the table title, i.e. [ Measures]




Labels:

Comments

  1. BASANT KUMAR2 March 2022 at 20:26

    factorial hundred In the last few days, the “factorial of 100” is one of the top subjects and a lot of maths geeks compute it using voice assistants such as Alexa, Shiri, etc.
    auto-accident-compensation-claims-in-michigan When needing to hire a car accident lawyer, you want someone you can trust to see your case through to the end.
    car-accident-lawyer-san-fernando-valley When needing to hire a car accident lawyer, you want someone you can trust to see your case through to the end.

    factorial hundred In the last few days, the “factorial of 100” is one of the top subjects and a lot of maths geeks compute it using voice assistants such as Alexa, Shiri, etc.
    eiffel tower macau Aurecon was involved in the design and design of the unique new structure. We provided complete structural engineering advice for the project and assisted with keeping the original design.
    car-accident-lawyer-columbus-ga Although the aftermath of a car accident may only last for a second, the physical, financial, and emotional consequences of a wreck can last for the rest of your life.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...
Post a Comment
Read more

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...
Post a Comment
Read more

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article)  Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...
5 comments
Read more