Data-Driven Heaven in Power BI Using Field Parameters, Bookmarks, CSS, and Expression-Based Formatting

This week it just happened that two of my consulting clients needed a date dimension table so I started revising my base Power Query template to make sure it was ready to share. After I improved my M code to better handle various fiscal year ending months, my mind started getting sucked into advanced scenarios to let the end user:

• Choose whether to use civil or fiscal periods in charts (obviously for a fiscal year that doesn’t end in December).
• Benefit from a year/quarter/month hierarchy.
• Choose which metric to display.
• Choose which reference line to display.
• Apply dynamic conditional formatting to series, data labels, axis labels, chart title, and even the canvas background.

At first sight you’d think field parameters would check most of these boxes, but it turns out that they can’t be fed a hierarchy. But when there’s a will, there’s a way, right? After several hours of trial and error combining field parameters with bookmarks, DAX-driven formatting, and CSS backgrounds, I built the mother of all dynamically formatted reports:

The embed doesn’t give it justice, it’s best seen in full screen. To experience some of the slick behavior that’s built in:

• Click back and forth on the Civil/Fiscal buttons to see how the entire report page is transformed. Don’t click on the “Field Parameters for Period” checkboxes, I left them for reference. Well, you can click on them, but that doesn’t always behave the way you might expect.
• Look how the Civil/Fiscal bookmarks (via the underlying field parameters) affect the x-axis content and format.
• Now click on individual months and see how the page’s background subtly changes depending on the select value. Is your mind blown yet?

The key trick here to emulate hierarchies is to create bookmarks that save multiple selections in field parameters as one state, i.e. the Year > Quarter > Month field parameter selection is saved as the Civil bookmark while FY > FQ > FM is the Fiscal bookmark.

Other techniques applied here:

• Min/Max formatting applied to data labels, a new feature of the August 2022 Power BI Desktop release, based on parameter-selected measures. This is combined with selective use of transparency (by adding “00” at the end of your RGB values) to hide the other labels.
• Series formatting that changes with each quarter as well as color-codes current and future periods differently. The end of the fiscal year is an M parameter loaded into the model while past/present/future status is part of the date dimension. In other words, we’re leveraging our M code for further use in DAX to improve the visualization layer. All components of Power BI, unite!
• All color-coding is done via DAX expressions so the whole thing is scalable across charts without manually setting rules in each chart. Thinking like a developer will give you productivity gains and make your reports more future proof and flexible.

The pseudo hierarchy in the field parameters for period selection is a bit clunky if you’re interacting with it directly as opposed to via bookmarks, but otherwise things are looking pretty good. At first changing between Civil and Fiscal reset the Metric and Reference Line selection, but then I figured out that I needed to exclude these two slicers from the Civil and Fiscal bookmarks. Now the user selection is preserved, which makes me very happy indeed.

Also, we’re still running into Power BI seemingly only randomly allowing expression-driven formatting. I’ve been told by Microsoft staffers than fully supporting DAX in formatting requires significant rewrites. So no dynamic control of the reference line’s color or transparency for you, Olivier! Clearly they’re after me personally.

These quirks aside, I haven’t seen anything else quite like it. I have to say I’m quite happy with myself as these days I work much more on architecture and data modeling than on the visual side, so I hadn’t done much with field parameters yet. I wish field parameters get improved on the DAX side, as you have to resort to hacks to be able to use them programmatically.

Of course the data displayed in this example comes from the date dimension which is not terribly useful. This is just a proof of concept, in the real world you’ll apply these techniques to your fact tables.