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 minute...
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.

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