A Power BI Dynamic Dashboard with Measures: Usage and Tips with a Dynamic Semantic Model

2/12/2026

Data Set Up and Power Query Editor
Title Page with Slicer
Category Page with Slicer and 4 level hierarchy for Products
Drill Through Page for Product Detail
Bookmark

Utilizing BI tools, such as Power BI, can add several levels to a company’s internal reporting and data insights. Interactive and dynamic dashboards can be composed relative to organizational departments such as supply chain, finance and accounting, operations, and marketing; but it can also be tailored to the level of the audience, from the managers “in-the-weeds” to the executives. However, in order to get the most out of the visualizations and analysis for the intended user, dynamics, scalability, minimal maintenance and integrity in calculations, a good deal of planning should be implemented to optimize your time in actual development.

This article will flow through some thoughts on the layout and planning of a dashboard, inclusive of dynamics and functional components such as a Drill Through on four-levels of products, a Bookmark, and several Measure calculations (afterall, if I was going to piece this together I also wanted to test some ideas). If you don’t use Power BI, that is okay, many of the planning and data organizational steps will apply to any BI or model development.

One thing about developing models and learning about the functionality of a BI tools is that you can always learn more. This is why I always encourage model questions and model comparisons. Don’t be scared to have someone question your model … questions, encourage thought and development. Likewise, don’t be scared to compare with other model builders. Learn from each other, it greatly increases your learning curve and capabilities

That said, I, personally, can always learn more about optimization of DAX formulas and calculations, best practices for data storage and extraction, transformation, and loading processes … remember there are no dynamic, scalable models without IT. I strive to continue to learn more and improve in every area of business knowledge and applications because it makes me better, so this article is, once again, just some thoughts on the usefulness of Power BI, functionality, and data planning for models.

For all the planning that goes into data flows, analysis, and even the choosing of the “right” charting methods, perhaps the biggest sticking point, first and foremost, for users is the color scheme! I kid you not, people are visual creatures. While there are Power BI contests for best color schemes, there are also contests for ugliest dashboards. With this in mind, before even address data retrieval, I will address color.

For a challenge, I let AI design a logo and color scheme for the Adventure Works company from the Microsoft data set, an outdoor gear, manufacturing and retail bike shop. It chose Pine Green, Earth Brown, and Burnt Orange. I think it is a very nice logo, but the colors are definitely a challenge for a presentation.

When designing a company presentation, whether dashboards or Power Point, I try to use the company colors and an associated palette of complementary colors. If you have never tried to use orange, brown and green … well … it can be a design challenge. There are several color palette generators out there that offer a complement of colors, and like Power Point, you can set your theme in Power BI.

I used ChatGPT and Canva to offer some color options.

ChatGPT:

With the addition of a couple of Canva’s suggestions I set the theme. Go to the

View > Theme Tab > Customize Current Theme

Most companies have templates and color themes for their Power Point presentations; these themes can be loaded into Power BI as JSON files. I could have also had AI generate the JSON, but a presentation uniformity is an important consideration that can’t be understated.

DATA Extraction-Transform-Load

If you want visualizations that are dynamic with minimal maintenance, then you must connect (link to) the data, but more importantly you should communicate with IT.

In a large organization, you may have a data warehouse that is a central location for all migrated data from all company applications. In mid-sized organizations you may have a relational database for a bulk of financial and operational data, but there may also be some data, particularly marketing, that is in a CRM application or on spreadsheets. In small organizations, you may have many departmental spreadsheets with only the accounting ledgers in an application. No matter the situation you need to locate, organize and ensure a method for cleaning the data. Remember GIGO! (Garbage-In, Garbage-Out).

Departmental spreadsheets that won’t be migrated to a database should be moved to central locations such as SharePoint folders. This offers controlled access and a single location to retrieve updated data. The spreadsheets can also be tailored to cell ‘Data Validation’ to maintain data integrity and consistency.

Database and data warehouse maintenance and optimization will probably rest with the data engineer or IT. The way the data is stored will determine whether it may be beneficial to have some ‘Views’ structured for optimized queries from the BI platform. These ‘Views’ might include JOINs or calculations that aren’t in the source tables migrated from applications. While these could be done from Power BI’s Power Query Editor, perhaps your modeler isn’t as strong with SQL or it is optimal to offer the ‘View’ from the database.

Applications, such as ERPs and subscription applications that provide data, that are not yet migrated into a database generally offer an OData protocol connection for data extraction. Unfortunately, this can slow the ERP and the data is fed to your system ‘as-is’ and any cleanup must be done by your modeler. But with no other option this is an avenue to consider.

Data connections:

Depending on what you are going to connect, security gateways may require authentication for security reasons, but when connecting to a database you might get something like the following

Which brings me to the next consideration for planning … parameters and the organization of the queries.

As you can see, under the Advanced Options you can include a SQL statement for data ETL, but this can also be done from the Power Query Editor, seen in a moment. Right now I want to draw attention to the Dropdown letter box next to the Server and Database.

You can set up parameters to simplify connections.

By using parameters, any changes to names or locations can easily be made by adjusting the parameter and not every single query.

As you can see, I set up some parameters for the path to the spreadsheet files (Path_to_Dept_Files), a named spreadsheet (Budget_File), and the Access database name (AccessDB).

Going to the Power Query Editor will allow us to see how parameters can be used in other queries for ease of coding and organization. My editor already has my queries for this semantic model, but these queries can be organized in folders by right clicking the Query column and selecting new folder.

For this example I chose to display the queries in folders related to their source. The Staging Queries folder that I made contains the parameters I set up. The ‘Manage Parameters’ selection along the top bar allows you to create new parameters and edit exiting ones.

If we were to look at a query in M language, used by the Power Query,

You can see that I selected the ‘Customer’ table query and the ‘Source’ from the applied steps. Since I put the AccessDB on my desktop, the path is simply from the C-drive to the desktop. “C:\ …\Desktop\”. And the database name is “AccessDB.accdb”.

By creating parameters

               Path_to_Dept_Files =
                                             C:\ … \Desktop\
and
               AccessDB =
                                             AccessDB.accdb

I can use these as the Source.

I can use the parameters for every query that involves this database. If, at some point, the path is changed, then I only need to change the parameter. Folders and parameters can be useful as you pass models along to the next contributor.

The Power Query Editor can also be used for transformations, calculations, merging and appending of queries and data quality.

However, any of these steps that can be taken at the source, e.g. in the database ‘View’, should be done at the source. You would like to take some of the computational pressure off your laptop and utilize source resources. In some cases, the option to use the Power Query is the only option, such as if you would like to join a spreadsheet from a SharePoint folder and a ‘View’ from a SQL query. Understanding computational resources is just another area to consider, but perhaps process activities require a different order of steps and usage of each resource.

You may find that you may not want certain calculations in your ‘View’s because the changes are too restrictive to certain departments. You may find that you want to implement steps in the Power Query Editor, or even in Power BI’s calculated columns or calculated measures. You will also want to consider whether you would like to use Snowflake or Star Schemas for your model, each differing just slightly in the dimension tables, specifically with hierarchies.

This leads to one more consideration that I would like to bring up before going into the model. How many contributors will be developing models and what data will they require access to while still maintaining data integrity in calculations.

CENTRAL WORKSPACE FOR DATA INTEGRITY

One possible solution for data integrity at the loading phase for models is presented in several Power BI articles, that of the Power BI Workspace as a central location for the data tables that will be accessible be all contributors to build their models and reports.

Power BI offers the Desktop for model development, but the sharing and distribution of the information is done by ‘Publishing’ the model to the Service. Some companies use the Service Workspace as the source of the various tables of data that have undergone all the accepted transformations from the Database or Spreadsheets through the Editor and finally into data tables. These tables are then used as the source for all models.

This method works well to conserve data queries through single source of truth, single point refreshes, and single point security gateways if accessing off-site databases. When your data flows for ETL is organized and cleaned to a single point, it is also much easier to track and trace discrepancies or breakdowns. Furthermore, this data flow only needs to be planned out once and the model developers then only need to concern themselves with the reporting and model development … which itself is still considerable.

POWER BI SEMANTIC MODEL

Let’s take a look at my cover page.

A quick view, high-level recap on revenue to budget, top product subcategories, location sales, gross margin, average unit price, delivery metric and new customer tally.

…And always remember, someone is going to want you to make changes so be flexible for colors, layout, time-period, slicers vs filters, and so on.

When laying out a design for a dashboard in Power BI there are a couple of structural items that need to be set up within your planning and design before getting into the visualizations. These structural items are slicers, filters and measures.

If you have ever created a Pivot Table, then you are probably familiar with a slicer. The slicer can act as an adjustable filter to a set of data. Within the BI model you can select which visualization will interact with a slicer, but you can also click on visualizations, and these can act as slicers as well. In the above top sheet, the Product Category slicer offers a quick category level filter of the data.

However, this brings me to filters. Filters can be set up in the filter column, expanded below. Filters can be set to an individual page or all pages in a report. You might put a dimension variable such as product category, subcategory, model type, or product name or a timeline variable such as the year and month as filters. You can also allow these filters to be used by the audience of the report, or you can set the filters and hide them so that the filter is permanently set. This affects slicers.

The slicers and filters can work in coordination with each other or against each other if you happen to forget that you have set filters up in your report. But there is one more item, the measures.

Measures are like Excel functions but written in the DAX language. Measures can be written to calculate values to use in visuals and these formulas can utilize or override slicers and filters. DAX adds a lot more versatility, functionality and dynamics to a Power BI report, but it does take a bit of a learning curve to use it. The most important concept to understand in the DAX language is the ROW concept vs the FILTER concept. Entire books are written on DAX so I can’t cover concepts in depth here, but I will show some of my calculations and perhaps it will encourage others to use it.

So, when I say that there are items to consider for the structure of a report it might go something like this. You know that your audience will only use the trailing 6 months of data and only desire a very high-level view of product categories and subcategory and location data only by region. The intended audience may not want to use the functionality of the filters and would like everything to be “push button”.

Your report set up may include hidden filters to include only the product category and subcategory and location data at the regional level. This way any slicers need to only distinguish between the two hierarchal layers of the product without any further details and any locations will already be filtered at the regional level with any slicing to be done amongst regions. Because the time-period is a trailing 6 months that will be rolling, the date-time used can be written either from a DAX measure within the report or in the Power Query Editor at the query level. The “push-button” is easily done using the button and linking features within Power BI, as will be seen. You can push a button to Bookmark a particular set of filters and visuals and even highlight specific visuals and then push another button to return everything back to the original state without ever needing to select a slicer or manually adjust a filter.

Of course, the data you set up, whether through query, slicer, filter, or measure, is the only data available for you to use. If you need more data, then you will have to adjust your planning and set up.

SOME FUNCTIONALITY AND USAGE

Selected below is the ‘Clothing’ category and you can get a direct view of how all the visualizations adjust to present data for the category. And note that clicking the ‘Clear All Slicers’ button will do exactly as advertised.

As mentioned, I used several measures in this report and a couple of slicers. Because visuals can act as interactive slicers and create drill down views into granular levels of data. I usually don’t use many page level or report level filters unless it will be structurally beneficial to the composition.

If you use Measures, you can set up an empty table to organize them or you can included them with the tables used in the measure. While I would need to add descriptions and consider aggregating some of these calculations for a cleaned up model, the list of measures is as follows

Below the logo, I like to use a DAX expression to track the refresh date.

LastRefresh =
VAR CurrDate = TODAY()
RETURN
"Refresh Date: " & CurrDate

And since I have organized the “Actuals v Budget” barchart with the ‘Current Quarter’, yet to be completed, I also included the number of working days left in the quarter.

If I hover over a column, you will see some of the details available relative to the data columns used to produce the visual.

You can also add further insights to this information through the ‘Tooltips’.

Now, if I click on the Actuals bar for the ‘Prior Quarter’ you will see that the Trailing 6 MOM highlights Oct, Nov and Dec and all totals from the tables are adjusted to reflect this time-period.

As mentioned, these visuals can be just as effective as a filter or slicer. In addition, you can go through the page and select which items will be filtered by a selection and which ones will not be filtered. Also note that the center square, the KPI, is blank. The KPI was a measure written to display only the current months actuals to budget.

MTD_RevenueBud =
SUMX(
VALUES(dimProductCategory[EnglishProductCategoryName]),
VAR ProdCatNames = dimProductCategory[EnglishProductCategoryName]
RETURN
CALCULATE(SUM(Budget[Budget]),Budget[CategoryName]=
ProdCatNames, DATESMTD(__Calendar[Date_Range]))
)

Because the current month is not in the ‘Prior Quarter’, the KPI is blank.

In the same fashion, if I was to select “Road Bikes” from the Subcategory table, we will see the inter-visual filtering with the subtle change that reflects the portion of the columns attributable to the Road Bikes subcategory.

None of these visualizations thus far have offered any granular drill down or drill through capabilities. For these capabilities, I used the product data. By clicking on the ‘Product Page’ button, it brings us to the product page.

First, note the bright red button. I marked this so that we could see the Bookmark capability. You can set any filters, slicers, and hierarchies and create a Bookmark that will immediately implement the settings so that you can display data that needs attention.

I created just one. By pressing the button the page adjusts to all of my settings.

While you can ‘Clear All Slicers’ from here, it is often easiest to have a ‘Reset’ button to return your page to the desired set position.

The Bookmark is available under the View Tab.

There are further options and settings for Bookmarks, but it is an added feather that can be useful for reporting.

A layered hierarchy adds to the granularity for analysis and dynamics of the visualizations of the report.

Take note of the Rows within the Build Panel. The columns used come from the Products dimension tables and allow for us to present the products from the category to subcategory, model, and finally specific product. This drill-down can be done for both the charts and the matrix tables as seen above. Just like before, we could also select a column and use this as a filtering element if our visualizations allowed it.

Finally, I want to show the Drill Through capabilities since this is another specific element that needs to be set up to use but can offer another useful method to display data.

To create a Drill Through page, you create a new report page that is usually hidden in the main report. Select the page type ‘Drill Through’ and input the hierarchal column to determine the level at which the user can select a drill-through from the data.

For our example, let’s say I drilled down to the Model level in the Product matrix and found the Touring-1000 at the top. I let my mouse hover over it a moment and the dropdown options appear.

When I select the Drill though it brings me to the Drill Through page, now adjusted for the Touring.

I set up the table to list all products by color/size that sold under the Touring-1000 model for the YTD.

CLOSING

This article was a bit longer, but it was mostly images. Afterall, what good is a discussion on dashboards and reporting if you don’t get to see some of the capabilities in action.

I obviously couldn’t cover everything in regard to Power BI data reporting, such as more information on table relationships and understanding one to many and many to many, or the creation of calculated tables or a deep dive into DAX, but Power BI modeling and reporting topics are covered in entire chapters and even books. It would be difficult to cover everything in a short article. What I do hope is that the article introduced some information to consider when structuring a model and introduced some useful capabilities in Power BI reporting.

Because the tool is quite extensive, I think a user will always feel like they are learning new things. The best way to get better with a tool is to practice.

0 Comments

A Power BI Dynamic Dashboard with Measures: Usage and Tips with a Dynamic Semantic Model

Leave a Reply.