Creating a Dynamic Axis in Power BI

What does a Dynamic Axis do?

Dynamic axis scaling is an interesting visualization pattern that makes it possible to allow users to make a slicer selection that dynamically updates the x-axis. The idea would be to present the end user with a slicer on the report page with options of example Year, Quarter, Month and or Day and when the user selects one of the options, visuals react accordingly and display the appropriate aggregated values.

Why use this pattern?

It makes it possible to use one chart and show two visuals for example, sales by year and sales by area.

How it can be done

There are many ways to do this.

One idea is to it is with Power Query and DAX.

Duplicate your table and rename it to TableNameUnpivot
Select the 2 columns (Attributes) that you want to dynamically switch the axis between.
Make sure the two columns that you are unpivoting are the same data type, then select the columns and unpivot them.
Click close and apply.
Go to report tab and change Page View to Actual Size from the Desktop View tab.
Add Attribute as a slicer, and make a bar chart with Values, but there’s a problem, all the sales values are the same
To fix it we’ll create a new measure and call it Value Dynamic with the following DAX.

Value Dynamic = CALCULATE('Sales Fact'[Sales],CROSSFILTER('TableName'[ProductKey],TableNameUnpivot[ProductKey],Both))

You can now use Value Dynamic to make your dynamic axis Chart

Another way of how it can be done is by using a Bridging Table.

Create a Region-Year table which would be the product of Region and Year LookUp tables.
Duplicate each row of the Region table and add an Axis Dimension column, which is “Region” for half of the rows and “Year” value for each row depending on the Axis Dimension value.
Make a relation between Region and Year to Region-Year using inactive bidirectional relationships

Create an Axis Dimension Selected measure to get values of Axis Dimension

Axis Dimension Selected =
IF (
 ISFILTERED ( RegionYear[Axis Dimension] ),
    IF (
        CALCULATE ( HASONEVALUE ( RegionYear[Axis Dimension] ), ALLSELECTED () ),
        VALUES ( RegionYear[Axis Dimension] )
    )
)

Create a Sales Amount Flexible Axis measure with the following DAX assuming Sales Amount is the normal measure.

Sales Amount Flexible Axis = 
IF (
    NOT ( ISBLANK ( [Axis Dimension Selected] ) ),
    SWITCH (
        [Axis Dimension Selected],
        "Region",
        CALCULATE (
            [Sales Amount],
            USERELATIONSHIP ( RegionYear[Region], Region[Region] )
        ),
        "Year",
        CALCULATE (
            [Sales Amount],
            USERELATIONSHIP ( RegionYear[Year], 'Year'[Year] )
        )
    )
)

Now you can create visualisations using Region-Year[Axis Value] and [Sales Amount Flexible Axis]

Blog