Forum Discussion

slown_surelearn's avatar
slown_surelearn
Frequent Visitor
1 year ago

Filter timestamp based on condition and previous value

Hello team

 

Can you help me with this? I need to select a record, a timestamp, based on when this timestamp changed of Owner team, but only if the previous team has certain value, in this case "alpha":

 

CHANGE_DATE_TIMETEAM
07/03/2025 07:39:03ALPHA
10/03/2025 08:35:06ALPHA
10/03/2025 08:45:58ALPHA
10/03/2025 08:53:07ALPHA
10/03/2025 08:53:33Beta
10/03/2025 14:33:30Beta
10/03/2025 14:33:39Beta
11/03/2025 07:42:17Beta

 

so for this case, I would need to retrieve the value of the timestamp "10/03/2025 08:53:33", wich would be easy using the MIN function, but I need to check if the previous value for the team its actually "alpha", if not then should go for the next min.

Any ideas? Thanks!!!

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    None of your sample data says "alpha". Remember that Power Query is case sensitive.

     

    Please provide sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      1 year ago

      Hi slown_surelearn 
      Thanks for reaching out to Microsoft Fabric Community Forum.
      Based on the above data,

      let
    Source = Excel.Workbook(File.Contents("C:\Users\v-aatheeque\OneDrive - Microsoft\alphathread.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CHANGE_DATE_TIME", type datetime}, {"TEAM", type text}})
in
    #"Changed Type"

       

      For your example data, this will return "10/03/2025 08:53:33", as this is the first timestamp where the team transitions from "ALPHA" to "Beta".
      Here is the expected output :

       

      If you still persists the issue as suggested lbendlin  share the sample data and expected output.

      If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

      If you continue to face issues, feel free to reach out to us for further assistance!



    • slown_surelearn's avatar
      slown_surelearn
      Frequent Visitor
      1 year ago

       

      Thank you. this is a better example I think:

       

      CHANGE_DATE_TIMEResponsable team
      03/03/2025 10:22:48Charlie
      03/03/2025 10:24:40Charlie
      06/03/2025 08:56:44Beta
      06/03/2025 11:07:45Alpha
      06/03/2025 11:50:43Alpha
      06/03/2025 12:05:11Alpha
      07/03/2025 07:39:00Alpha
      07/03/2025 07:39:03Alpha
      10/03/2025 08:35:06Alpha
      10/03/2025 08:53:33Beta
      10/03/2025 14:33:30Beta
      10/03/2025 14:33:39Beta
      11/03/2025 07:42:17Beta

       

      Im looking for the first value of the Change date when the responsable team is Beta, with the condition that the previuous team is Alpha.

       

      In this case, the expected value is "10/03/2025 08:53:33"

       

      I have to use Directimport mode and therefore I cannot use powerquery.

       

      Thanks in advance.

       

      Carlos

       

       

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        1 year ago

        Hi slown_surelearn 

        Yes, since you're using DirectQuery mode, you cannot use Power Query (M language) for transformations. However, you can achieve this in DAX within Power BI.

        You can create a mesure to find the change time date to when the team changes ALPHA to BETA.

        First_Change_To_Beta =
VAR PreviousAlphaDate =
    CALCULATE(
        MAX('Table'[CHANGE_DATE_TIME]),
        'Table'[Responsable team] = "Alpha",
        'Table'[CHANGE_DATE_TIME] < 
            MINX(
                FILTER('Table', 'Table'[Responsable team] = "Beta"),
                'Table'[CHANGE_DATE_TIME]
            )
    )
VAR FirstBetaDate =
    CALCULATE(
        MIN('Table'[CHANGE_DATE_TIME]),
        'Table'[Responsable team] = "Beta",
        'Table'[CHANGE_DATE_TIME] > PreviousAlphaDate
    )
RETURN
    FirstBetaDate



        If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!


         

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi slown_surelearn ,

    To retrieve the timestamp when the owner team changes from "ALPHA" to another team, you need to identify the exact point of transition. Simply using MIN won’t be enough, because it would return the earliest timestamp for any team change, regardless of what the previous team was. To handle this, we first need to compare each record with the one immediately before it to check if the previous team was "ALPHA" and the current team is different. Once we identify these transition points, we can filter the data accordingly and extract the earliest valid timestamp. This logic can be implemented using DAX by adding a calculated column to flag such transitions, and then using a measure to retrieve the minimum timestamp where the condition is met.

    Calculated Column (Assuming an Index Column exists):

    IsAlphaToOther = 
VAR CurrentTeam = 'YourTable'[TEAM]
VAR CurrentIndex = 'YourTable'[Index]
VAR PreviousTeam = 
    CALCULATE(
        MAX('YourTable'[TEAM]),
        FILTER(
            'YourTable',
            'YourTable'[Index] = CurrentIndex - 1
        )
    )
RETURN
IF(PreviousTeam = "ALPHA" && CurrentTeam <> "ALPHA", 1, 0)

    Measure to Get the Timestamp:

    FirstChangeFromAlpha = 
CALCULATE(
    MIN('YourTable'[CHANGE_DATE_TIME]),
    FILTER(
        'YourTable',
        'YourTable'[IsAlphaToOther] = 1
    )
)