Change Y-Axis Format field based on a slicer or data value

[JohnAdv]

Hi Fellow PBI explorers,

Simply, i want to change the Y-axis Start and End when i click on a slicer choice.

Generally, Power BI at this point appears to not allow us to change Format values (such as Axis Start/End,

Data colors) dynamically based on data values. (I haven't explored custom visual development yet.)

I need to have a line or bar chart change the Start and End values for the Y-axis based on data  in a table or slicer.

In other ords, dynamically change the Y-axis scale.

one value on a slicer will show chart values between 0 and 1 while another option on the slicer will cause

the chart to show values between 0 and 100.

John

[CheenuSing · Accepted solution]

@JohnAdv

I am assuming what you want to show on the y-axis is different measures depending on the selection in slicer. 

1. Let us assume you have a SalesTransaction table that has both Quantity and SalesValue.

2. We have a slicer that displays Quantity and SalesValue as a selection.

3. Based on the selectionn in the slicer we want to display Quantity or SalesValue in the Y-Axis of the graph.

4. To acheive the above follow the steps below.

5. Create a static table called SelectMeasure  ( not connected to anyother table in the model)  having columns 

     MeasureID and MeasureName

6. Rows for the example will be

      1. Quantity

      2. SalesValue

7.  Create a slicer using the MeasureName. 

8.  Create two measures called TotalQuantity = Sum(Quantity) and TotalValue = Sum(SalesValue)

9.   Create a measure called DisplayMeasure as

      DisplayMeasure= switch (TRUE,
                                                     Min ( 'SelectMeasure'[MeasureID] ) = 1,
                                                                      ( [TotalQuantity] ),
                                                     Min ( 'SelectMeasure'[MeasureID] ) = 2,
                                                   ( [TotalValue] ) )

10. In your chart use this DisplayMeasure as the Value column.

11. Depending on the selection in the slicer the Y-axis will change according to the values of Quantity or SalesValue.

Try this and if this works please accept it as a solution and also give Kudos.

Cheers

Cheeusing