Power BI - Specific Column Measure Switch

Utilizing Field Parameters

---

Problem

Within a Power BI table/matrix, there are specific columns that need to be switched from $ to % (or any other measure switches needed), while other columns cannot be switched.

Bookmarks are one way to accomplish this, but are hard to maintain and upkeep. Not to mention, they also take a bit of time to set up to begin with.

Field parameters would work great if all of the columns within the table used one measure and not a separate measure for each column.

For this example, we are going to look at Contoso Sales Sample dataset. We want to change the returned units and returned amount columns in the table to the right $ and % (of sales and units sold). We do not want any of the other columns to change though.

The end result will switch between:

• Returned Units $ and Return Units % of Units Sold

• Returned Amount $ and Returned Amount % of Sales

Solution

While it may not seem like field parameters are the solution, they are. It requires more than 1 field parameter though. It may take a bit more time to set up that a single bookmark would, but in the long run, maintaining the report will be a lot easier.

Please note: I assume that you understand how field parameters work as I do not cover how they work in depth in this post. This feature is also still in preview, so you will need to enable that in the option/settings.

Step 1:

Create measures for each variation and column needed. For example, if you are only switching for a $ to % for 12 columns, you will need 24 total measures.

Make sure that these measures are formatted correctly.

Based on our example, 4 measures have been created. Two

measures are $ while the other two are %.

Step 2:

Create field parameters for each combo of measures. For example, for 1 Month $ and 1 Month % would be grouped into a field parameter together.

Make sure that the order of $ and % (or any other switches) remains the same for all of the field parameters that you are creating. You can also rename the fields as to what you want to show on the table/matrix (ie. 1Mo $ and 1Mo %).

Shown to the right is one of the needed field parameters, another one is created similarly but with the Returned Amount.

Once all of the field parameters are created, you will see that stand alone tables are created with DAX.

If you ever need to make changes to the measures, names, order, etc. you can edit the DAX within the table after the field parameter has been created.

Field Parameter Created DAX Table:

Step 3:

Create a static table by "Entering Data". This table will only be two columns. The first column will have the measure switch names (ie. Dollars ($) and Percent (%)). The second column will be labeled Order and will start with 0, 1, etc. based on how many items you have in your field parameters.

Make sure the order is the same as how you set it up in the field parameters, we will be using this order column that was just created to act as a lookup to the field parameters' created order column in each table.

Note that in our example, the $ was the first measure in the field parameter, while the % was the second.

Step 4:

Join the lookup table that was just created to all of the field parameter tables that were created in Step 2. The join will be from the Order column in the manually created table to each order column in the field parameter tables.

Step 5:

In the table/matrix you will bring in the field parameter column from each table as columns into the visual.

The Returned Amount and Returned Units tables are the tables created by the Field Parameter. Please note that there are hidden columns within these table, DO NOT add those to the table/matrix.

As you do this, you will notice all switch options show up in the table - this will change within the next steps (as shown in the image below).

Step 6:

Add a slicer to the page with the text column from the manually created table from Step 3. Change this slicer to single selection only and select an item (the extra columns from Step 5 will go away and only the column you want to see with the switch will show).

You can set up the slicer however you would like to. Once the slicer is set up, you can toggle between the options.

In our example, we are able to toggle between $ and %.

Final Thoughts

While it may seem tedious to set up, the long term benefits out weigh the up front manual work. Bookmarks have their uses, but with the new field parameters, they can *almost* be a think of the past.

Lastly, this provides a lot more flexibility (I mean Field Parameters do in general) in how you can display multiple views within one visual.