Metrics tables are great! I argue that all of you measures should be moved into a Metrics table. Don’t know what a Metrics table is? Care to know why I think you should move all measures to that table? Please watch the video below and your questions will be answered.
My reasons:
- Makes it easier to train a user on where to find measures by not scattering them around multiple tables in the model
- A calculator icon next to the Metrics table name visually stands out and makes it easier to find
- If you have a lot of measures and your Power BI Desktop is too slow when it comes to creating more measures, so you decided to use a tool like Tabular Editor to create more, then having your measures in a BLANK table allows you to quickly refresh it and make those new measures appear in the Desktop view.
I disagree. My reasons:
1. Users can easily find measures by searching for them by name
2. Leaving measures on the fact table allows the user to leverage the relationships view to understand what the metric can be sliced by. If all metrics are on a single Metric table, the users will have no clues, except maybe by getting a cartesian product. This also makes it much easier and faster for other developers to debug when they have to maintain the model, otherwise, they will spend an obscene amount of time debugging why calculations are not working because the relationship context will not be readily apparent without opening the dataset and traversing the relationships of each calculation and any underlying calculations. Excel pivot tables also allow user to filter a fact table to see related dimensions. You take that ability away if you dump them all on one metric table.
3. If your fact table contains only measures and all the columns are hidden, your table will also get a calculator icon and float to the top
4. Measures are held in memory. They don’t require a table refresh to be visible.
I do agree that using Tabular Editor is helpful when your model is slow. (although sometimes a slow model in PBI Desktop may mean your model has too many dependencies causing circular references that make it hard for the PBI api to traverse the PBIX document structure efficiently)