DAX Workout 022 - Control your Axis

[Paul.Gerber]

In this workout, you are to show how to gain full control over the y-axis in your visualizations. Demonstrate how to create manual control buttons and synchronize the y-axis across different visualizations with the help of a parameter. Also showcase the creation of a slicer to switch between automatic and manual axis control, and update the axis max and min accordingly.

You will need to create a disconnected table with options for automatic and manual axis control and make a slicer to switch between them. Then update the axis max and min based on the selected option, using an if statement in a measure, and disable the range slicer when the automatic option is selected. This allows for a synchronized axis when the manual option is selected and automatic scaling based on visible values when the automatic option is selected, with the range slicer enabled only for the manual option.

Submission

Load the supplied data file into a new Power BI file, create your solution, and reply to this post. Upload a screenshot of your solution along with the DAX measure. Please format your DAX code and blur it or place it in a hidden section.

Period
This workout will be released on Thursday, June 1, 2023, and will end on Sunday, June 4, 2023. But you can always come back to any of the workouts and solve them.

Practice Dataset.xlsx (815.3 KB)
DAX _22 - ControlYourAxis.pbix (890.1 KB)

[Gustaw]

Hi Paul, awesome workout! Thank you for your effort to put this into the real task and opportunity to learn more!

Please find below my entry.

DAX _22 - ControlYourAxis - GD.pbix (899.0 KB)

[Paul.Gerber]

@Gustaw great job and thanks for doing the workout. Excellent results

[HufferD]

@Paul.Gerber,

Thanks for putting this together. The tricky bit is filtering the Axis Parameter slicer and allowing the Axis Control slicer to interact with it.

I used these measures

slicer_isManual = 
IF ( SELECTEDVALUE ( 'Axis Control-Type'[Axis Control ID] ) = 1, 1, 0 )

axisY_U = 
IF (
    SELECTEDVALUE ( 'Axis Control-Type'[Axis Control Description] ) = "Manual",
    param_ManualY[param_ManualY Value],
    [axis_limitY_Upper (channel)]
)

axis_limitY_Upper (channel) = 
CEILING (
    CALCULATE (
        MAXX ( VALUES ( 'Date'[End of Month] ), [Total Sales] ),
        ALLSELECTED ( 'Date' )
    ),
    10000
)

DAX _22 - ControlYourAxis -hufferd.pbix (902.4 KB)

[mbraun]

@Paul.Gerber another good one.

[shahadathossen.shajj]

DAX Measure

This text will be hidden

Param Value =
SWITCH (
    TRUE (),
    VALUES ( 'Y Axis'[Type] ) = "Manual", SELECTEDVALUE ( 'Y Scale Range'[Y Scale Range] ),
    VALUES ( 'Y Axis'[Type] ) = "Automatic",
        CALCULATE (
            MAXX ( VALUES ( 'Date'[Month & Year] ), [Total Sales] ),
            Channels[Channel Name] = "Distributor",
            ALLSELECTED ( Sales )
        ) * 1.15,
    BLANK ()
)