In this short blog post, we will learn how to create a separate table for measures which are developed by citizen developers when creating a new report. This table will have an icon similar to an icon of a measure and will be placed on top in the list of all tables in the Fields pane.
Use case
Imagine the following use case. You are a Power BI expert who has created a dataset for your company. This dataset is used by many citizen DAX developers for creating reports via Live connection. You, as a dataset designer, can create new tables, columns or measures in DAX. However, a citizen developer can only create measures (when using a live connection). These measures have a special domain logic and are usually used only in one report. And one more important fact:
“A measure needs to be defined in a table. This is one of the requirements of the DAX language. However, the measure does not really belong to the table. In fact, you can move a measure from one table to another one without losing its functionality.” (Source: https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/)
It is a good practice to have a table just for the citizen developer’s measures.
How To
First, we need a new table for measures. Click on Enter data button in Power BI Desktop and create a table with one column and without any values like on the following screenshot.
By the way, I have also created another table called My Table just for showing you the difference of icons in the end of this article.
Next, we will create a new measure in the table Custom Report Measures.
Let us call the new measure Blank Measure.
When your new measure is created, there are two “items” in the Custom Report Measures table: a measure and a column. A table must have at least one “item” (it does not matter if it is a column or a measure). We can remove the column Column1 and the table will exist further.
We will get the following:
I have promised that there will be another icon for a table which contains only measures. To achieve that we have to refresh the content of the Fields pane. The simplest way is just clicking twice on the arrow in the right top corner.
Voilà, the new icon is there.
And a proof that these “measure tables” are always on top and sorted alphabetically:
Final notes
I use this measure table feature in this article as an aid for report specific measures. However, it can also be used for global measures — for measures that are defined in a dataset and used in many reports.