Forum Discussion

Diaze2108's avatar
Diaze2108
Frequent Visitor
1 year ago
Solved

Displaying Latest Timestamp per Location in Table Visual with DirectQuery

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!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Diaze2108
      Frequent Visitor
      1 year ago

      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"
    )