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

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


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.


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

Learn more about Power BI in this 3 hour training course.

Click here to start the Power BI course

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-07-29

Comments For This Article

Friday, May 17, 2024 - 8:21:33 AM - ANKITA CHAUHAN Back To Top (92246)
Can we do this same for column line chart . I am trying it is taking correct value for X and Column Y Axis but not for Line y axis

Wednesday, November 16, 2022 - 4:11:00 AM - Daniels Back To Top (90689)
Great article! Thank you! Have you figured out maybe why this doesn't work on Power BI's default Scatter Plot? :(
Sadly I can't attach a screenshot of my error but I receive the following errors in my Scatter Plots:
1) The field values in your x-axis are mismatched. Set all the fields in your x-axis to ‘Don't summarize’, or remove all fields except one.
2)Remove Play Axis to display x- and y-axis pairs, Alternatively, you can keep Play Axis and set a summarization for x- and y-axis, See details

get free sql tips
agree to terms