How-To: A Table for Custom Report Measures

[Nolock]

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.

[shyammayhs]

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

[Nolock]

Hello @shyammayhs,

I'm not sure, what you're asking for. You can use a measure as filter only at a single visual level. For more information please read the following article: https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

[shyammayhs]

Hi @Nolock 

I mean I have created a similar structure to you - where I have the data tables and then a table which holds the Measures. But when I add filters to the report, the measures do not filter down.