How to Display Multiple Dynamic Vertical Lines with Corresponding Data Labels

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

 

 

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 🙂

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