Skip to main content

Going level beyond with Field Parameters

Image
By

Field parameters are widely used to change fields or measures dynamically in a visualization. If you are new to field parameters, we have covered the basics of field parameters in one of the earlier blogs.

Related Read: Mastering Field Parameters in Power BI

Field parameters are a powerful way to let users dynamically switch between different fields (like columns or measures) — no drill-down needed. This hands-on guide walks through how to use field parameters to create flexible visuals, build custom slicers, and improve report interactivity. You'll also learn tips on editing parameters, handling implicit vs. explicit measures, and avoiding common mistakes.

In this blog, we will take it to the next level. Before we get started, I am using Sample Superstore as my dummy data. So let's get started, and we are first creating a basic field parameter taking all basic measures - Total Sales, Total Quantity, and Profit. We are dragging this field parameter in a basic table visual.


Nothing new 🙁, there is a hidden surprise if you closely notice the field parameter called KPIs we have slightly modified the slicer settings and provided the Select all instead of selecting different fields one by one. Also, you can turn this slicer into a new button slicer to improve the UI for your reports.

Since you are warmed up now, let's focus on the real deal - so I would need all these KPIs as field parameters. Hang on but the surprise I also want to show Current Year (CY) and Last Year (LY) values too. So, if I select Sales as my KPI then the visual should show me Sales CY and Sales LY together in different columns 😵‍💫

How are we going to achieve this? Okay, this is where we need to go a level further with field parameters. First, we need to ensure we have two different field parameters for CY and LY. Basically, all CY measures in one field parameter and all LY measures in another field parameter.

Once you have it, then we need to focus on connecting these two field parameters somehow. To do so, we will be creating a small table, where we will just enter the KPI names like Sales, Quantity, and Profit. Let' create the table - go to the Enter data section from the top.


This will open a pop-up where we need to manually enter the KPIs - Sales, Quantity, and Profit. I have named this table "Bridge". Now, let's head to the data model and try to connect all three.


To connect our bridge table will act as a connecting table between the CY and LY field parameters. But wait, what is going to be the key for this join? Yes, great question, we need to create a key in both field parameters.

To do so, we need to head to the field parameter DAX and add another ',' right after the numeric values. We need to ensure we are providing the same names as we have in the Bridge table for the KPIs. Also, the names should be in double quotes since it's a text. Do the same for the LY field parameter.

Once you have this in both the field parameters, you will observe a Value 4 popping up in both field parameters and this Value 4 will become the key to connect with the bridge table. 


Time to see the final results, let's head to the visual and we have created a matrix and we need to use this bridge table as the only truth for the visual. Insert the KPIs from the bridge table to the matrix and add it as a button slicer too.


See, the magic happens. We have selected Profit and both Profit and Profit LY are popping in the matrix based on the sub-category. How awesome is it? At Analyst in Action we always strive for more action and this time we will take this one more level ahead. 

Now, we are showing only the sub-category as my dimension but what if your user wants to see the same CY and LY measures with different dimensions? To do so, we need to create another field parameter with all the dimensions and name it Select dimensions.


Once, we have it just need to drag it and see the magic. I am selecting the Category and sub-category together. Easy peasy lemon squeeze!! In rows, you need to use the select dimension field parameter and in the values, you will be using the KPI CY and KPI LY.


Value 4  in the field parameters helps a lot to achieve things like this. There are many more use cases where value 4 can become crucial. In this blog, we have restricted ourselves to matrix visuals, but you can even import visuals to enhance the user experience on your reports. 


Love this blog? Get even more with BI Bits!

I've launched a Power BI newsletter called BI Bits — your go-to for quick, practical tips and tricks to level up your dashboards and DAX skills.

Each edition is:

  • Short and actionable

  • Beginner- and intermediate-friendly

Let’s make Power BI simpler, one bit at a time 💡

Comments

Post a Comment

Popular posts from this blog

Copying Bookmarks from one Power BI report to another

By
Let's think of a scenario, where you want to copy the bookmarks from one report to another. Most obvious approach is to just do a copy paste of the bookmarks. What's wrong with this approach? This approach only works for all visuals but not for bookmarks and field parameters.  If you are not familiar with basics of bookmarks and field parameters do refer to the beginners guide for bookmarks  and introduction to field parameters . Then how do you copy the bookmarks? Power BI enhanced report format (PBIR) for Power BI Project files (PBIP) will help you in achieving this. Let's check it out, I have 2 reports one contains the bookmark called Bookmarks PBIR Test (origin) and other one is Rolling Average PBIR Test (destination) .  Before we get started, you have to enable Power BI Project save option under preview features. Once enabled, restart Power BI desktop. There is a TMDL icon appearing on the left pane. What is TMDL and what's in it for me? There's a lot of possi...
Post a Comment
Read more

Playing with Totals in Power BI

By
Are you a fan of matrix visual in Power BI? If you are as I am, I always struggle to get the correct totals and get something else instead of the totals it can be average. After a lot of research and going over different community posts, finally we have found 3 common scenarios that can elevate your matrix to the next level. To start with, I am using Sample Superstore data. Let's first explain the 3 different scenarios that we will tackle - We  would like  to show both Total Sales and Average Monthly Sales across different categories and different periods. We  would like  to show the Average Sales in the row subtotals and Total Sales in the column subtotals. Last and the  most interesting scenario is to show the Total sales excluding the furniture sales in the row subtotals and total sales in the column subtotals. Let's start by getting the correct totals in a matrix. Generally, if  use  basic Sum, Average... functions in your measures then most likely...
Post a Comment
Read more

Introduction to Power Ops

By
Power BI, combined with all external tools, at least the basic ones, can be a perfect BI tool to meet all your needs. Today's blog will focus on another external tool. What?? Not another one!! In my regular practice, I always rely on basic external tools such as DAX Studio, Tabular Editor, and Measure Killer. But what if I say this new tool is a transformer, combining all the basic tools in one place? If you are looking for an introductory guide for the basic external tools, we got you covered.  Beginner's Guide to DAX Studio Advanced Guide to DAX Studio Guide to Measure Killer Yes, you read it right and we will give you an introductory guide to all the functionalities that can be achieved with  Power Ops . Okay, first things first to download Power Ops you can visit their  website  and I would recommend exploring it with one of your reports. You can download the free version. It will be available under the external tools section in Power BI Desktop. Before we move f...
Post a Comment
Read more