Clean up your DAX measures with MEASURE TABLES and FOLDERS in Power BI

let's clean up your power behind measures [Applause] i've created this report with a whole bunch of meshes and stuff is getting a little bit messy so it's time to clean it up and let's do this together so as a first step i want to make sure that all of our measures are consistently named so that everything that refers to the same field shows up right next to each other okay so if you go over here to the fct forecast table where you see our measures and you see for example that everything that relates to revenue shows up as total revenue forecast and then whether it's last year year and year etc so that it nicely shows up together and then over here for total units have done the same now if you don't do this for example here some units forecast you see it also refers to the units however this one shows the top and then a little bit down the list we have then again total units and it's not nice right it's much better if it shows up right next to each other so as a first step let's take this one rename it to total units as well and as soon as you rename it then you see it also sorts it alphabetically and therefore shows up right above the other total units measures okay so that's step one at this point our measures are still all over the place we have some measures for forecast table and then if i go to the sales table we have some more measures and but maybe is a little bit better is if we take all of the measures and put them in a separate measure table so to create a measured table is really simple you just go here to the home tab and then enter data and you just leave this dialog box here empty and the only thing that you change is the name now over here the first attempt that i tried was measures however this is a reserved name it doesn't let you use that one so what you could do instead is to save something like sales measures so metrics whatever you prefer so then you click on load and here on the right hand side we have now a separate table that is not connected to any of the other tables we have the name sales measures and basically has one column with just one row now as a next step we can assign our measures to this table because each measure is stored in what is called a home table now if you go for example two fct sales take total revenue actuals and then here under measure tools you have here on the left hand side home table which currently says fct sales but we can also change this now to sales measures you see once you have done this it shows up in the sales measures table and now to get rid of this column one if two options you can either hide it or you can also delete it just delete it only as soon as you have some measures placed in it otherwise it deletes the table so let's now also take all of the other measures and place them in our sales measures state now let's go to the modeling view and make sure that we see the properties and the fields on the right-hand side and then you can go to your measures let's say that are stored in the fct sales table and just select them by holding the shift key and now we can take our measures and place them in the sales measures table just drag and drop them right on top of it you see all of our measures now show up over here except the ones that relate to forecast so let's repeat this for those as well so go to here to my forecast demo select my measures and then just drag them on top of our sales methods table now if you have a measures table so meaning a table that has deleted all of the columns that are inside of that table which you still need to do is go back to the report view and then just hide the fields pane then open it up again so now you can see that i can change to measures icon and because we only have measures and no columns inside of this table so the next thing that i want to do is create fullness and subfolders to further organize our measures in the measure table so as a first step let's now take then all of the actual measures and put them in one folder and then we take the forecast measures and put them in a different folder so here at the top we have the first actual measures then you can go to your properties pane general and here you have the display folder now just put in the name that you want to give to this folder so let's say actuals you see it creates a folder with all of these measures that i had selected now if you want to add further measures to this folder you can simply select them and drag them on top of it so if i go here to total units actuals then i can just drag them on top of the actuals folder and they will be added to that folder okay now let's close that folder and do the same for all of our forecast measures so i'm going to select all of the other ones over here and then i go here to display folder and this one i'm going to call forecast and see all of the other measures are now here in my forecast folder now there's one that doesn't really belong in here which is total sales bigger than forecast which i use for conditional formatting so let's place that one into another folder just select it and then go to display folder and type another okay so let's now create further subfolders for example for revenue and units then you can go to your actual folder select the ones for which you want to create the subfolder so let's say total revenue you go again to display folder type in a backslash and then the name of that subfolder so here we want to have one for revenue you see we have now the revenue subfolder and we do the same for units so i select all of my total units measures then go here to display folder backslash and then units and the same thing i do for my forecast folder so totally have a new forecast select all of your measures go to display folder backslash and then over here we have the revenue then we do the same for the units okay so now let's see how that looks like if we are in the report view so i'm going to go back to my report view and you see we have now sales message table with different folders for actuals and then subfolders for revenues units and the same for forecast revenue and units and then all of the other kpis so you see it's much more organized now let's say that we also want to create another folder that only includes our favorite measures that we use all of the time so that we don't have to go through all of the folders and subfolders now to do this you can go back to the modeling view and let's say we want to have the ref actuals in there so let's take that one as a starting point what you do is you go also here to display folder and then you can type in semicolon and the second folder in which you want to show this measure so you want to have a favorite folder so i call this one favorites and you see that the magic gets now duplicated so we have it over here doloref actuals and over here total revenue actuals again and let's say we want to do the same also for total units actuals then i can go over here again display folder semicolon and i'm going to call this one as well favorites and you see it gets added to the favorites folder okay so if i'm in the report view and i close all of the folders then now i can go here to favorites where i only see those measures that i use a lot one thing still bothers me and that is that the favorites folder shows up somewhere in the middle because everything is always sorted in alphabetical order now what you can do is put a dot in front of favorites so that it always shows at the top however still i think that looks kind of ugly so instead of that i always number my folders so let's do this i'm gonna go back to my modeling view and favorites is gonna be my number one folder so let's right click rename and then put a one in front of it then we're gonna have our actual folder so that's going to be number two then we're gonna have also a forecast folder number three so you see in the report view everything now shows nicely in order that i wanted to show so first favorites then actuals and then forecast so this is how i keep my measures organized in power bi maybe you have some more tips then share it in the comment section below if you like this video and want to learn more about power bi consider subscribing to this channel and give us the thumbs up see you in the next video

Channel: How to Power BI

Views: 27,857

Keywords: measure table, measure table power bi, power bi measure table, dax measures, organize measures power bi, measure table in power bi, dax cleanup, too many dax measures, using measure table, folders power bi, power bi folders, create measure table, dax measure table, many dax measures, organizing measures, power bi, power bi tutorials for beginners, power bi desktop

Id: cGnWPua-cmo

Length: 9min 29sec (569 seconds)

Published: Wed Jul 15 2020

Reddit Comments

[u/NbdySpcl_00]

This really is a pretty nice channel. I thought I'd drop the channel link here:

https://www.youtube.com/channel/UCcfngi7_ASuo5jdWX0bNauQ

[u/dkuhry]

The folders in your measure table thing is awesome. I knew you could do it, I just could not figure out how. Awesome!