Forum Discussion
Change Y-Axis Format field based on a slicer or data value
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
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
I like it, Thanks!
10 Replies
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
I like it, Thanks!
I have tried several variations of this approach with MeasureID being in one table and Quantity being in an unrelated table (within a Tabular model).
The calculated column (measure) does not fail, but the slicer on MeasureID has no affect in Power BI. In other words, the value returned is always [TotalQuantity], even when MeasureID=2 is selected in the slicer. Am I missing something? Is it possible to slice a value from an unrelated table?
- Habib
Continued Contributor
Auto is the only option and it will work automatically based on the result set. You can't set the Axis values dynamically.
