cancel
Showing results for 
Search instead for 
Did you mean: 
Nolock

How-To: A Table for Custom Report Measures

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.

08.png

 

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.

01.png

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.

03.png

 

Next, we will create a new measure in the table Custom Report Measures.

04.png

 

Let us call the new measure Blank Measure.

05.png

 

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.

06.png

 

We will get the following:

07.png

 

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.

08.png

 

And a proof that these “measure tables” are always on top and sorted alphabetically:

09.png

 

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.

 

Comments

Hi, Thanks for this. Am I able to use filters on the measure table? If so, how have you implemented this.