Displaying Latest Timestamp per Location in Table Visual with DirectQuery

[Diaze2108]

Hi everyone,

I'm facing a challenge in Power BI and would appreciate any assistance or suggestions you might have.

Background:

I have a dataset where data snapshots are collected every 5 minutes from various locations (e.g., warehouses, logistic centers). These snapshots are stored in a table with a timestamp. However, not all locations provide data at the exact same time due to network latency or other factors. For example:

• Location 1: Data available at 12:00
• Location 2: Data available at 12:05

This means that at any given time, the latest available data for each location might have different timestamps.

What I'm Trying to Achieve:

I want to create a table visual in Power BI that displays the latest available data for each location. Essentially, I want the table to show the most recent snapshot per location, even if the timestamps differ between locations.

The Issue:

When I apply a Top N filter (Top 1) on the timestamp in the table visual, Power BI filters the entire table to only show data from the single latest timestamp across all locations. In the example above, it would only display data from 12:05, which means I only see data for Location 2. Location 1's data from 12:00 is not displayed because it's not the latest timestamp overall.

This approach doesn't meet my requirement since I need the latest data for each location, not just the overall latest timestamp.

Constraints:

• I'm working in DirectQuery mode due to data size and refresh requirements.
• I cannot create calculated columns or tables that are not supported in DirectQuery.
• Functions like RANKX in calculated columns are not available in DirectQuery mode.
• I'm looking for a solution that doesn't involve complex measures that might impact performance or aren't supported in DirectQuery.

What I've Tried:

• Applying Top N Filter: Using the Top N filter on the timestamp in the visual filters, but as mentioned, it filters globally rather than per group.
• Creating Measures: Attempted to create measures to identify the latest timestamp per location, but couldn't use them effectively in the visual filters.
• Exploring Visual-Level Filters: Tried to find a way to filter within the visual to get Top 1 per location but haven't found a solution.

Is there a way to configure the table visual (or any other visual) in Power BI to display the latest available timestamp per location while working within the limitations of DirectQuery? I'm aiming to avoid complex calculations or unsupported functions due to the constraints mentioned.

Any suggestions, workarounds, or alternative approaches would be greatly appreciated!

Thank you in advance for your help!

[Anonymous · Accepted solution]

Hi @Diaze2108 

Please try using this measure below, I hope it helps.

LatestData = 
VAR LatestTimestamp = 
    CALCULATE(
        MAX(Snapshots[Timestamp]),
        ALLEXCEPT(Snapshots, Snapshots[Location])
    )
RETURN
    CALCULATE(
        MAX(Snapshots[DataColumn]),  //Replace with your data columns
        FILTER(
            Snapshots,
            Snapshots[Timestamp] = LatestTimestamp
        )
    )

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Diaze2108]

Thank you! It finally worked!

I just had to add a second Measure to be able to Filter the Visual properly. At first I had- IF True = True, False =False and I the Filter option for the measure wouldn't open. After I changed it to 1 and 0, it worked. 

Also- I was getting OECDB error because of exceeding 1000000 rows, so I had to use [Timestamp] as filter first. TopN = top 5 of Timestamp. This decreased the size of the calculation and it worked 🙂 

IsLatestTimestamp =
VAR LatestTimestamp =
    CALCULATE(
        MAX(Snapshots[TimeStamp]),
        ALLEXCEPT(Snapshots, Snapshots[Location])
    )
RETURN
    IF(
        Snapshots[LatestData] = LatestTimestamp,
        "1",
        "0"
    )

[Rupak_bi]

Hi @Diaze2108 
A measure will work along with groupby categories. the syntex will be as below
calculate(max(table,[value]),allexcept(table,table [category],table [subcategory]), table [date/time] = max(table[date/time])).

if doesn't works, plz share sample data.