Power BI Dynamic X and Y Axis Using Groupings in Field Parameters

By:   |   Updated: 2022-07-29   |   Comments   |   Related: > Power BI Charts


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
Problem

One very common requirement by business users for Power BI reports is the ability to dynamically select what should be on the X or Y axis of a chart. The new Field Parameters feature makes this possible and easy to create in Power BI. Let's investigate how this can be achieved.

Solution

For this post, what we are trying to achieve is to select a value on a slicer, and dynamically change the field values on the X or Y axis on a chart. For instance, the diagram below has its numerical values on the Y-axis and the categorical values on the X-axis. We want to be able to dynamically change the field (measure) on the Y-axis as well as the X-axis.

Sample Power BI chart showing final solution.

To achieve this, we can use Field Parameters to create a slicer that enables us to dynamically change the field values on each axis. Let's see how we can do this.

To demonstrate, I have imported a dataset into Power BI as shown below. We want to dynamically change between each measure and the categorical field selected.

Sample dataset

For instance, we might want to see Total Sales Amount by Year, or dynamically switch to show Total Product Cost by Month on the same chart.

Within the DatesTable, I created a column for Year and Month. We want to use the Year, Month, and ProductKey columns as our categorical fields, and the Total Sales Amount and Total Product Cost measures as our numerical fields.

Next, we need to create measures for each of the dates. The sample measures can be created as follows.

Total Product Cost = SUM(Query1[TotalProductCost])
Total SalesAmount = SUM(Query1[SalesAmount])

In your case, there might be more or fewer measures to create, just adapt this to your business requirement. In all, the diagram below shows the measures and columns I have created for this demo.

Diagram showing created measures in Power BI Desktop.

Next, we need to use Field Parameters to group values we want to see on the X or Y axis as follows. First, click on the "Modeling" tab at the top of the Power BI desktop as seen below.

Diagram showing the modeling tab in Power BI Desktop.

Then, click on the dropdown "New Parameter" and select "Fields" from the options as shown below.

Diagram showing how to navigate to Filed Parameter feature in Power BI Desktop.

The Field Parameters window opens as seen below.

Diagram showing Field Parameter window.

You can choose to change the name of the Parameter to something else or leave it as the default. I have changed this to "Slicer" as seen in the diagram below. Next, I will expand my tables on the right-hand side and drag in the fields I need to use for my X and Y axis. You need to leave the "Add slicer to this page" check box enabled, so a slicer can be automatically created for you, then click "Create". See below.

Diagram showing Field Parameter window and completed entries.

The create action will automatically create a slicer with the fields included in the "Add and reorder fields" section in the diagram above.

Diagram showing Slicer visual auto-generated from Field Parameter.

Also, DAX code is generated as seen below.

Diagram showing DAX code generated from the Field Parameter configuration.

Next, we need to define which fields are on the X-axis and those that should be on the Y-axis. To do this, we need to add a grouping to the DAX code that was generated which would create an "X-axis" and "Y-axis" grouping.

From our understanding earlier, all fields for Y-axis should be numerical, thus we can include "Total Product Cost" and "Total Sales Amount" in the Y-axis group and the others which are categorical fields on the X-axis group.

There are two ways to achieve this, either we amend the code above or create new columns on the field parameter table created. Let's first look at how to approach it by amending the generated code as seen below.

Diagram showing DAX code generated from the Field Parameter configuration and added group column.

In the code above, I added a new column with two distinct values ("X Axis" and "Y Axis"). The new column is given a generic name as "Value4" as seen in the diagram below, but this name can be changed like any other column name.

Diagram showing new column to the Field Parameter table.

The other way to create this grouping is to create the column just like how other columns are created using DAX. To do this we can use the DAX IF function to create a new column as seen below.

Diagram showing simple DAX for creating a column.

The output of this DAX created column is seen below.

Diagram showing new Grouping column to the Field Parameter table 2.

Next, we need to use the "Slicer" ("Parameter" in your case if you have not changed the name earlier) in both the X and Y axis as shown in the diagram below. It will automatically recognize what fields go into what axis as we have defined earlier.

Diagram showing how to use the newly created Field Parameter for configuring dynamic x and y axis.

Next, we need to apply the grouping to the slicer visual we generated by dragging "Value4" or the "Grouping" column we created to the top of the "slicer" (or "Parameter" in your case) as seen in the diagram below. This should create the grouping by including all X-axis fields in the X-axis group and all Y-axis fields in Y-axis group.

Diagram showing how to use the newly created Field Parameter for configuring dynamic x and y axis 2.

We can now select a field on the X-Axis group, like Month Name, and Ctrl click on a field on the Y-Axis group, like Total Sales Amount, and see the rendered visual as shown below.

Diagram showing final output of dynamic x and y axis using field parameters.

When we need to change the fields on the X and Y axis, like Year on the X-axis and Total Product Cost on the Y-axis, we just need to click on Year on the X-axis group and Ctrl click on Total Product Cost on the Y-axis. The rendered visual should look as follows.

Diagram showing final output of dynamic x and y axis using field parameters 2.

In summary, there are several ways to approach this to meet your business requirements. There are other approaches I have seen that also solve the same business need, but some of those require creating more than one field parameter as well as more than one slicer visual. I have created this demo as simple as possible while also taking into consideration the fact that this approach ensures you only have a single slicer visual and a single field parameter on your model. This ensures the performance of your Power BI model is not impacted.

Next Steps



Request Your 30-Day Free Trial of Melissa Data Quality Components for SSIS Today




get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Microsoft Certified Data Analytics and BI Professional mostly in Microsoft BI stack of tools.

View all my tips


Article Last Updated: 2022-07-29

Comments For This Article





download














get free sql tips
agree to terms