cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Dynamic constant line

Hi Guys,

 

I'm trying to figure out if there is a possibility to work with a dynamic constant line. My current visual is based on healthcare data. What I can show them right now is the running total based on a year. The constant line shows me the budget based on a hardcoded budget. See picture below:

 

Constant line.PNG

 

 

 

 

 

 

 

 

What I want, when I choose for another year in the filter, my budget (constant line) changes automatically. Is there a possibility to achieve this?

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@miltenburger,

 

I'm afraid this couldn't be achieved because the value of constant line is determined by constant value, it can't be changed by slicer. As a workaround. I would recommend you to create a measure using DAX like pattern:

 

Measure =
SELECTEDVALUE ( table[value] )

And then drag the measure to your chart.

 

Regards,

Jimmy Tao

View solution in original post

8 REPLIES 8
Highlighted
Community Support
Community Support

@miltenburger,

 


What I want, when I choose for another year in the filter, my budget (constant line) changes automatically. Is there a possibility to achieve this?

 


 

The "constant line" you mentioned is the constant line option in Analytics pane, right? If it is, what does "based on a hardcoded budget" mean? Could you clarify more details about the cuurent settings and your expected result?

 

Regards,

Jimmy Tao

Highlighted

Thanks @v-yuta-msft for your reply.

The constant line in the analytics pane that's right.

How i implemented the constant line right now:

 

Constant.PNG

 

 

 

 

 

 

 

 

 

 

My value in column "Waarde" is hardcoded. But actually I want this value to change dynamicaly when I change the year filter on my report. Is there a possibility for this?

 

Highlighted

@miltenburger,

 

I'm afraid this couldn't be achieved because the value of constant line is determined by constant value, it can't be changed by slicer. As a workaround. I would recommend you to create a measure using DAX like pattern:

 

Measure =
SELECTEDVALUE ( table[value] )

And then drag the measure to your chart.

 

Regards,

Jimmy Tao

View solution in original post

Highlighted
Advocate IV
Advocate IV

Another approach/trick that worked for me was:

  1. Create a measure (may work with a calculated column that will later be summarized as average, MAX, MIN, etc.). The measure (or aggregation of calculated column) will have the dynamic value of the constant line you want to display. In my case, I used a measure and I am showing a constant line that changes value depending on the current month.
  2. Create your visual without the constant line first. In my case, I am using the clustered bar chart.
  3. Add the measure with the constant value to your list of values of the visual. The value will appear in your visual. In my case, it appeared as another horizontal bar. You'll hide it in a later step.
  4. Go to the analytics tab of the visual and instead of adding a new Constant Line, add a Min Line, Max Line, or whatever line makes more sense for your measure, column, visual, etc. as long as the option you choose allows you to select the measure/column you added in the previous step. From the formatting options of the line you added, click on the dropdown labeled "Measure" and pick the measure that has the value of the constant line.
  5. Finish formatting the line as you want (title, text position, color, etc.).
  6. Go back to the Fields tab of the visual and drag the measure with the constant value from the Value group to the Tooltips group. The value will now disappear from the visual but the constant line should remain.
  7. (optional) If you do not want the value of the now dynamic constant line to show in the Tooltip of the visual, create a custom visual. However, keep in mind that if the user is viewing the report in a way that doesn't support report page tooltips, the user will see the default Tooltip including the value of the dynamic constant line you just created.
Highlighted
Anonymous
Not applicable

Thanks, I can't believe this worked and did exactly what I was looking for!  One point of clarification I would add is with step 3.  I had to set the value of the visual to the measure as I could not figure out how to "Add the measure with the constant value to your list of values of the visual".  It set all of the bars to the measure but, once I went to Analytics and set the max line to this measure, I was able to set the original value back in the visual and the max line stayed set to the measure.

Highlighted

Very clever!  

Highlighted
Anonymous
Not applicable

Hello @marcoselias I tried the steps as following. However the constant line has no value showing. The target should be 5.2bn. Any suggestions. thank you

 
 
Highlighted

Is the measure that you are going to use as a constant line evaluating to the 5.2bn that you are expecting? you can verify this with a card visual and dragging the measure to the Fields well. If it is evaluating correctly, did you drag the measure as a value of the visual (step 3 above) before going to the analytics tab to add a new Min Line, Max Line, etc. (step 4)? Also, double-check the formatting of the line in case its color blends with the background of your visual (step 5). Good luck! Don't forget to flag a a solution as it may help others.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors