Order Log / Chart improved visualization

[jonasdedual23]

hi There

I hope someone can help me out with that. 

I have the following szenario. I have the following tables: candle_1m and Order_Log. And I have two dimension tables: Dim_symbol and Dim_Timestamp. They are connected as the following:

I have now the below chart:

so far this works. I've got all details visible wen selecting a symbol. However, I am struggling to find a solution that is highlighting the price and timestamp when there was an opening and when there was a closing of a position in the chart. I always have to check the timestamp and price. 

Below is a drawing what would be a perfect solution:

I tried a couple approaches to get a solution but none of them worked. 

I would be very thankful if someone can provide me a solution.

If there is a partial solution available e.g. showing only the timestamp on the x axis it would also be helpful to me.

[jonasdedual23]

@burakkaragoz 

Sorry about all the confusion. 

After trying it today again, your first solution was working perfectly. I was not able to see the measures in the lines and markers section because there was no filter to open or close positon so it  was not even showing up in the settings pane. 

A bit confusing in power bi but now it works perfectly fine.

[jonasdedual23]

Thank you for your response. 

I' have added the measures. however, i am not able to see anything in the linechart when puting them on the y Chart.

What I have also noticed ist that the timestamp in candle_1m is rounded to minutes but the order_log has timestamps for every second. Therefore I have created this column:

timestamp_rounded = 
DATE(
    YEAR('Order_Log'[timestamp]),
    MONTH('Order_Log'[timestamp]),
    DAY('Order_Log'[timestamp])
) + 
TIME(
    HOUR('Order_Log'[timestamp]),
    MINUTE('Order_Log'[timestamp]),
    0
)

I have then connected dim_timestamp to order_log with the new rounded calculated column: 

However, the measure you have provided still doesn't show any data.

[AshokKunwar]

I'm so glad it helped! Please mark this as an Accepted Solution so it stays at the top of the forum for others facing these performance issues."

[AshokKunwar]

Hii @jonasdedual23 

In a standard line chart with many data points (like 1-minute candles), it is difficult to identify exactly where a trade was opened or closed without manually checking the Order_Log timestamps.

​The Solution

​We will create a measure that returns the price only for timestamps present in the Order_Log. By adding this as a second series to your line chart and disabling the line connecting them, you get "floating" markers that highlight your trades.

​Step 1: Create the Trade Highlighting Measure

​Create the following DAX measure to find the price at the moment of a trade

Trade Marker = 
VAR CurrentTime = SELECTEDVALUE('Dim_Timestamp'[timestamp])
VAR IsTrade = NOT ISBLANK(LOOKUPVALUE('Order_Log'[price], 'Order_Log'[timestamp], CurrentTime))
RETURN
IF(IsTrade, [Average Price], BLANK())

(Note: Replace [Average Price] with your existing Y-axis measure for the candle price).

​Step 2: Add to Visual and Format

1. ​Add to Y-Axis: Drag the Trade Marker measure into the Y-axis field well of your existing line chart (so you have two measures there).
2. ​Turn on Markers: * Go to Format visual > Visual > Markers.
   • ​Switch Markers to ON.
   • ​In the Apply settings to dropdown, select Trade Marker.
   • ​Set the Shape to a Square or Diamond and increase the Size to 8 or 10.
3. ​Hide the Connecting Line:
   • ​Go to Format visual > Visual > Lines.
   • ​In the Apply settings to dropdown, select Trade Marker.
   • ​Set the Stroke width to 0 px. This makes the line disappear, leaving only the markers on top of your price line.

​Step 3: Labeling the X-Axis

​To make the timestamps easier to read as requested:

• ​X-Axis Type: Ensure the X-axis Type is set to Categorical in the formatting pane if you want to see specific labels for every point, or keep it Continuous and enable Zoom Sliders to dive into the exact trade minute.
• ​Secondary Labels: You can turn on Data Labels for the Trade Marker series only (Format > Data labels > Apply settings to > Trade Marker) to show the price or timestamp text directly above the point.

​Why this works:

• ​Dynamic: Because your tables are connected via Dim_Timestamp, the markers will move and update automatically if you filter by a specific symbol or date.
• ​Visual Clarity: It solves the "manual checking" problem by providing a clear visual anchor (the marker) at the exact moment of the trade.

​If this solves your trade visualization, please mark this as the "Accepted Solution" so it helps others in the trading community!

[burakkaragoz · Accepted solution]

Hi @jonasdedual23 ,

This is a great visualization challenge. Your data model is already perfectly set up for this (Star Schema with a shared Timestamp dimension), which makes the solution straightforward.

To achieve that "Green/Red Cross" effect overlaying your price line, you need to create two specific measures that return a value only when an order occurs and return BLANK() everywhere else. By adding these to your line chart and formatting them as markers (without lines), you will get the exact result you sketched.

Here is the step-by-step implementation:

1. Create the Marker Measures

Since your Order_Log table likely doesn't have the exact "execution price" (or even if it does, you want the dot to sit perfectly on your price line), we will fetch the price from your candle_1m table whenever an order is detected.

Measure 1: Open Position Marker (Adjust the "open" text to match exactly what is in your Order_Log[action] column)

Trade Open Marker = 
VAR IsOpen = 
    CALCULATE(
        COUNTROWS('Order_Log'), 
        'Order_Log'[action] = "open"  -- Update "open" to match your data text
    ) > 0
RETURN
    IF(
        IsOpen, 
        MAX('candle_1m'[close]), -- Plots the dot at the closing price of that candle
        BLANK()
    )

Measure 2: Close Position Marker

Trade Close Marker = 
VAR IsClose = 
    CALCULATE(
        COUNTROWS('Order_Log'), 
        'Order_Log'[action] = "close" 
    ) > 0
RETURN
    IF(
        IsClose, 
        MAX('candle_1m'[close]), 
        BLANK()
    )

2. Configure the Chart

1. Select your Line Chart.

2. Drag both [Trade Open Marker] and [Trade Close Marker] into the Y-axis (alongside your existing High/Low/MA lines).

3. Go to the Format Pane (paintbrush icon).

3. The "Invisible Line" Trick

Now you need to hide the lines for these new measures and only show the markers.

1. Go to Lines:

   • Select Apply Settings to: Trade Open Marker.

   • Set Stroke Width to 0 px.

   • Repeat for Trade Close Marker.

2. Go to Markers:

   • Toggle Shape to On.

   • Select Apply Settings to: Trade Open Marker.

   • Shape: Select + (Cross) or Circle.

   • Size: Increase it (e.g., 7 or 10) to make it stand out.

   • Color: Set to Green.

   • Repeat for Trade Close Marker (Select shape x or Square, Color Red).

4. Result

Your chart will now show a continuous line for the price, but whenever a timestamp has a matching row in Order_Log, a distinct Green or Red marker will appear exactly at that price point, matching your desired sketch.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.