How to Display Multiple Dynamic Vertical Lines with Corresponding Data Labels

[Benoo876]

Hi! I'm working on a line chart in Power BI where I need to display multiple dynamic vertical constant lines. The line chart has Time points (in minutes) on x-axis and Values on Y-axis.

Constant lines should correspond to the start times of increased RPM (field Increased_RPM_begining) in my dataset. Additionally, if even possible, I want to display data labels next to each vertical line that show the corresponding Method label (field Method_label) value from the same row, aggregated with Increased_RPM_duration.

• Time: X-axis
• Value: Y-axis
• Increased_RPM_begining (Start of Increased RPM): The position for vertical lines
• Method_label (Method): The label to be displayed next to each vertical line
• Increased_RPM_duration (Duration of Increased RPM): Method_label + Increased_RPM_duration  = whole label show next to line (I can prepare this with calculated column in advance)
  

How can I dynamically add multiple vertical lines to my line chart, each positioned at the Increased_RPM_begining values? I can do that for one line with simple measure VerticalLinePosition = SELECTEDVALUE('Results'[Increased_RPM_begining]) but I was wondering if this is possible to achive for multiple (up to 3) lines? If I could have a dynamic label somewhere next to the line (Method) that would be additional plus.

[OwenAuger · Accepted solution]

Hi @Benoo876 

I think a line chart with error bars and data labels with be a good way to handle this.

I've mocked up an example - attached.

Source table:

Increased_RPM_beginning	Method_label	Increased_RPM_duration	Method Duration Label
1/01/2024 1:50:00 pm	Method A	3	Method A: 3
1/01/2024 2:00:00 pm	Method B	5	Method B: 5
1/01/2024 2:30:00 pm	Method C	10	Method C: 10

Measures:

Increased RPM Flag = 
-- This measure should return 1 if there is an Increased_RPM_beginning value preesnt.
-- May need to be rewritten based on your actual data.
IF (
    NOT ISEMPTY ( Data ),
    0
)

Increased RPM Error Bar = 
IF (
    NOT ISBLANK ( [Increased RPM Flag] ),
    1
)

Data Label = 
VAR SpaceChar =
    UNICHAR ( 8193 )
VAR SpaceCharRep =
    REPT ( SpaceChar, 1 )
VAR LabelValue =
    SELECTEDVALUE ( Data[Method Duration Label] )
VAR LabelFormatted =
    SpaceCharRep & LabelValue & SpaceCharRep
RETURN
    LabelFormatted

Set up the line chart with:

• Increased RPM Flag on Y-axis
• Increased_RPM_Beginning on x-axis (in reality should use date/time dimension table)
• Increased RPM Error Bar as an error bar
• Data Label as data label
• The line itself is set to zero-width to hide it.

There's probably a bit of effort needed to get the formatting looking right with your actual data but hopefully that's a start 🙂

Regards

[OwenAuger · Accepted solution]

Hi again @Benoo876 

I had a chance to look at this a bit more closely - PBIX attached.

Below are some suggestions with this being the final output.

1. Restructure the data so that the Increased_RPM columns are "joined" to the Time_point/Value data, rather than a column containing the Increase_RPM_Time_beginning column.

If the Increased_RPM doesn't correspond to an existing Time_point, interpolate Value to create a Time_point (I've set this up in Power Query).

For example, Trial 3 changes from this:

to this:

2. Create a Time dimension related to fact table.

3. Set up measures as follows:

Value Average = 
AVERAGE ( Sheet1[Value] )
--------------------------------------------------------------------------------------
Increased RPM Flag = 
-- returns 1 if at least on Increased RPM occurred at a given time
MAX ( Sheet1[Increased_RPM_flag] ) 
--------------------------------------------------------------------------------------
Error Bar Positive = 
VAR Increased_RPM_Flag =
    [Increased RPM Flag]
VAR Result =
    IF (
        NOT ISBLANK ( Increased_RPM_Flag ),
        VAR  MaxAllselected =
            CALCULATE (
                MAXX (
                    SUMMARIZECOLUMNS (
                        Time[Time_point],
                        Sheet1[Trail_label],
                        "@Value", [Value Average]
                    ),
                    [@Value]
                ),
                ALLSELECTED ()
            )
        RETURN
            MaxAllselected
    )
RETURN
    Result
--------------------------------------------------------------------------------------
Error Bar Negative = 
VAR Increased_RPM_Flag =
    [Increased RPM Flag]
VAR Result =
    IF (
        NOT ISBLANK ( Increased_RPM_Flag ),
        0
    )
RETURN
    Result
--------------------------------------------------------------------------------------
Data Label = 
MAX ( Sheet1[Increased_RPM_label] )

Error Bar Positive is just designed to retrieve the "max" point on the chart to determine the end point of the error bar.

4. Set up Error Bars:

5. Set Data Labels:

No doubt some tweaking required but sharing just in case it helps!
Owen 🙂

[Benoo876]

This is a well thought out and creative solution, thank you. But my problem is a bit more complex since those dynamic constant lines are just addition to existing line chart.

Original chart has Time_points  (field in my dataset, can vary between different data series: one series will have point 0-10-20-30 while anothe 0-10-30-40 and another 0-60-120 etc) on x series (points are descibed as int values, for example 0 - 5 -10 - 20 - 30 and not as date/time series), Value is on y series.

I guess I can overlap those charts with no background of constant lines one. However, since the X axis are not the same (first chart being Time_points and the second one is Increased_RPM_Beginning which is one of those timepoints) they do not align well.

[OwenAuger]

@Benoo876 

Understood, if the existing lines are based on multiple measures, then the new measure should be able to be added alongside them (updated example attached).

However, if your existing lines are a single measure with a Legend field, it could be a little more complicated. Are the existing lines based on a single measure with a legend, or several measures?

[Benoo876]

Only one at the moment. Sharing example of my dataset (forum doesn't let share pbix, so I am sendig wetransfer link)
https://we.tl/t-SSUn11Gbqz

[Benoo876]

@OwenAugerdo you think anything can be done here in my case where I am having one field per y-axis and using Legend as well.

Seems the solution is to align the x-axis by adding Time-points to the Legend of Error bar chart and then superimposing both charts. I am not 100% sure if this is the best solution, I will do some more testing and hope that the alignment doesn't go off by some coincedence.

[OwenAuger]

@Benoo876  Thanks for the updates - just catching up on this now 🙂

1. Ideally the x-axis fields should be based on a consistent dimension (whether date/time values or integers). You can create additional columns to transform them into the correct values if necessary.
2. I might have to play around a bit to see if we can get the error bars working with lines that are based on a single measure with legend field. Let me get back to you on that using your sample data soon.

In the sample data, should I assume that the Increased_RPM_beginning column values are consistent with the Time_point column values?

[Benoo876]

Time_point values always start with 0 but can go up to 360.

Increased_RPM_beginning

• is always within range of min(Time_point) and max(Time_point) of the given Trial_label
• and in 99,99% cases also one of the values within Time_points for a given Trial_label

[OwenAuger · Accepted solution]

Hi again @Benoo876 

I had a chance to look at this a bit more closely - PBIX attached.

Below are some suggestions with this being the final output.

1. Restructure the data so that the Increased_RPM columns are "joined" to the Time_point/Value data, rather than a column containing the Increase_RPM_Time_beginning column.

If the Increased_RPM doesn't correspond to an existing Time_point, interpolate Value to create a Time_point (I've set this up in Power Query).

For example, Trial 3 changes from this:

to this:

2. Create a Time dimension related to fact table.

3. Set up measures as follows:

Value Average = 
AVERAGE ( Sheet1[Value] )
--------------------------------------------------------------------------------------
Increased RPM Flag = 
-- returns 1 if at least on Increased RPM occurred at a given time
MAX ( Sheet1[Increased_RPM_flag] ) 
--------------------------------------------------------------------------------------
Error Bar Positive = 
VAR Increased_RPM_Flag =
    [Increased RPM Flag]
VAR Result =
    IF (
        NOT ISBLANK ( Increased_RPM_Flag ),
        VAR  MaxAllselected =
            CALCULATE (
                MAXX (
                    SUMMARIZECOLUMNS (
                        Time[Time_point],
                        Sheet1[Trail_label],
                        "@Value", [Value Average]
                    ),
                    [@Value]
                ),
                ALLSELECTED ()
            )
        RETURN
            MaxAllselected
    )
RETURN
    Result
--------------------------------------------------------------------------------------
Error Bar Negative = 
VAR Increased_RPM_Flag =
    [Increased RPM Flag]
VAR Result =
    IF (
        NOT ISBLANK ( Increased_RPM_Flag ),
        0
    )
RETURN
    Result
--------------------------------------------------------------------------------------
Data Label = 
MAX ( Sheet1[Increased_RPM_label] )

Error Bar Positive is just designed to retrieve the "max" point on the chart to determine the end point of the error bar.

4. Set up Error Bars:

5. Set Data Labels:

No doubt some tweaking required but sharing just in case it helps!
Owen 🙂

[Benoo876]

The only issuse I still have here is that data labels are shown for each series i.e. Trial_label. It means I get same Data_label shown three times at same Time_point. 

The goal would be to only show one label (doesn't matter for which Trial label) in cases where the Data_labels are the same at the same Time_point.

So far I was unable to filter out those "duplicates" with DAX. 

[OwenAuger]

Understood 🙂

I've attached a suggested update.

We can adjust the data labels to remove duplicates by adding Data Label Base and redefining Data Label as follows:

Data Label Base = 
MAX ( Sheet1[Increased_RPM_label] )
------------------------------------------
Data Label = 
CALCULATE (
    [Data Label Base],
    KEEPFILTERS (
        CALCULATETABLE (
            FIRSTNONBLANK ( ALLSELECTED ( Sheet1[Trail_label] ), [Data Label Base] )
        )
    )
)

Is this the sort of thing you were looking for?

Alternatively, I'm thinking Deneb might be an interesting option to explore for this visual as well 🙂

[Benoo876]

Thank you. This is almost exactly what I was aiming for - even more ideal solution would be not to take firstnonblank but just remove the labels that are the same at the given Time_point.

[OwenAuger]

Hehe, I realised the same thing shortly after posting that, and just came back and saw your reply 🙂

One method is to use INDEX to select one representative Trail per Time_point/Label combination:

Data Label =
-- Could change 'Time'[Time_point] => Sheet1[Time_point]
VAR DataLabelCombinations =
    CALCULATETABLE (
        SUMMARIZE (
            Sheet1,
            Sheet1[Trail_label],
            'Time'[Time_point],
            Sheet1[Increased_RPM_label]
        ),
        ALLSELECTED ( Sheet1[Trail_label] )
    )
VAR CombinationsToKeep =
    INDEX (
        1,
        DataLabelCombinations,
        ORDERBY ( Sheet1[Trail_label], ASC ),
        PARTITIONBY ( 'Time'[Time_point], Sheet1[Increased_RPM_label] )
    )
RETURN
    CALCULATE ( [Data Label Base], KEEPFILTERS ( CombinationsToKeep ) )

[Benoo876]

Thank you. Today I found out that Error bar positive solution works in Desktop but not in Service. Do you think this is a bug in Service or is there a difference how dax is intepretated between Desktop and Service?

[OwenAuger · Accepted solution]

Thanks for pointing that out! 🙂
It appears that I may have jumped the gun by using SUMMARIZECOLUMNS rather than SUMMARIZE.

Despite some recent updates, SUMMARIZECOLUMNS doesn't seem to be fully supported in measures in the Service.

This should work instead for now (PBIX reattached) 🙂

Error Bar Positive = 
VAR Increased_RPM_Flag =
    [Increased RPM Flag]
VAR Result =
    IF (
        NOT ISBLANK ( Increased_RPM_Flag ),
        CALCULATE (
            MAXX (
                SUMMARIZE (
                    Sheet1,
                    Time[Time_point],
                    Sheet1[Trail_label]
                ),
                [Value Average]
            ),
            ALLSELECTED ()
        )
    )
RETURN
    Result

[Benoo876]

This is truly amazing work and advanced thinking. Thank you!

[OwenAuger · Accepted solution]

Hi @Benoo876 

I think a line chart with error bars and data labels with be a good way to handle this.

I've mocked up an example - attached.

Source table:

Increased_RPM_beginning	Method_label	Increased_RPM_duration	Method Duration Label
1/01/2024 1:50:00 pm	Method A	3	Method A: 3
1/01/2024 2:00:00 pm	Method B	5	Method B: 5
1/01/2024 2:30:00 pm	Method C	10	Method C: 10

Measures:

Increased RPM Flag = 
-- This measure should return 1 if there is an Increased_RPM_beginning value preesnt.
-- May need to be rewritten based on your actual data.
IF (
    NOT ISEMPTY ( Data ),
    0
)

Increased RPM Error Bar = 
IF (
    NOT ISBLANK ( [Increased RPM Flag] ),
    1
)

Data Label = 
VAR SpaceChar =
    UNICHAR ( 8193 )
VAR SpaceCharRep =
    REPT ( SpaceChar, 1 )
VAR LabelValue =
    SELECTEDVALUE ( Data[Method Duration Label] )
VAR LabelFormatted =
    SpaceCharRep & LabelValue & SpaceCharRep
RETURN
    LabelFormatted

Set up the line chart with:

• Increased RPM Flag on Y-axis
• Increased_RPM_Beginning on x-axis (in reality should use date/time dimension table)
• Increased RPM Error Bar as an error bar
• Data Label as data label
• The line itself is set to zero-width to hide it.

There's probably a bit of effort needed to get the formatting looking right with your actual data but hopefully that's a start 🙂

Regards