For today’s blog, I’ll cover measure tables and subfolders. We’re going to set up measure tables, and then I’ll show you how to group measures and subfolders to help you organize measure tables that can get pretty crowded. You may watch the full video of this tutorial at the bottom of this blog.
But before I do that, let’s go to the Enterprise DNA forum.
Once you get to the forum, click on Latest. This will take you to the latest questions that are being asked on the forum.
The forum is a great place to not only answer questions but also learn from all the questions that are being asked in the forum.
One of the things that I see in the questions being posted in the Enterprise DNA forum is measure tables that contain lots of measures inside them. It can get very hard to distinguish and understand which measure is which.
Let’s go inside the Power BI desktop and go to the Modelling view. On the left side, my data model consists of a Date table and Jobs table. These are my fact tables.
On the right side are a couple of measure tables that have no active or inactive relationships.
On the selection pane, you can see all of the measures tables and fact tables that I have.
In my fact table, which is called the Jobs table, there are multiple dates involved. I’ve created a bunch of measures based on the dates inside of that fact table.
If we go over to the TREATAS measures tables, we can see that I have a bunch of measures. I have four measures for each date field: the Closed Date, Date Invoiced, Estimate Approved, Received Date, and Start Date.
I know it’s been around for a while and I’m sure a lot of you already know how to do this, but what I’m going to do is create another measures table and then organize these into the subfolders.
We go to the main ribbon and click on Enter Data.
I’ll type in Key Measures as the name of this new table and click on the Load button.
Once it’s loaded into the table, I’ll move those measures from the TREATAS measures table over to the Key Measures table. The quickest way to move these measures is to highlight the Closed $$ v2 measure, hold down the Shift key, and click on the Start Estimates at the very bottom. This step will highlight all of these measures.
Now, all I have to do is just hold down my mouse and move them from the TREATAS Measures table to the Key Measures table.
We’ll go back to the desktop, and you’ll see that we don’t have the symbol for the measures table yet.
So what I’ll do is go to the Key Measures table and delete Column 1 first.
Once Column 1 has been deleted, all I need to do is click the Fields arrow, and then click it again.
Now, we can see that the Key Measures table is at the very top of our Selection pane.
Creating Subfolders For Your Measure Tables
At this point, we can now create those subfolders to help organize the measures inside the Key Measures table. I’ll go back to the modelling tab and open up the Key Measures tables. Then I’ll hold down the Control key and click all of the Closed Date measures.
I’ll enter the subfolder name in the Display folder section. I’ll just call this subfolder Closed date and then click on somewhere else to save the folder.
In this example, I’ll just click on the Synonyms box and it will now create that subfolder and all those measures will be inside of the folder.
Now let’s do the same thing for the Date Invoiced measures.
And then we’ll do the Estimate Approved measures.
We’ll also create a subfolder for the Received Date measures.
The last subfolder would be for the Start Date measures.
Now we have all of our measures organized within subfolders here.
Let’s see what these subfolders look like in the model by switching back to the desktop view. You can now see the subfolders inside our Key Measures table in an organized way.
Some of you may already know how to do this. But I think this is a great tip for those people who are not aware of how to do it. Organizing your measures table is quite simple; it will also save you a lot of time in the future.
If you haven’t subscribed to the Enterprise DNA channel, make sure to hit that subscribe button. That’s all for today.