Forum Discussion

Elirot's avatar
Elirot
Frequent Visitor
7 years ago

Time difference based on two key columns

Hi guys,   I'm strugelling to get the duration in seconds between the minimum CompletedDate and maximum CompletedDate of the same AccountAppId and Team. If you look at the follwoing image of the d...
Need Help
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Elirot

 

Get a better way for you. You can get it by 2 calculated columns:

Sort =
IF (
    LOOKUPVALUE ( Table2[Team], Table2[Index], Table2[Index] - 1 )
        = Table2[Team],
    0,
    1
)
Column =
SUMX (
    FILTER ( Table2, Table2[Index] <= EARLIER ( Table2[Index] ) ),
    Table2[Sort]
)

 

Regards,

Cherie

  • Elirot's avatar
    Elirot
    Frequent Visitor
    7 years ago

    Hi Cherie,

    For the last week I was working on the solution you've suggested.

    Using your firts way to create the group I came out with odd results, i.e. I've got the same Group for different AccountAppId/Team.

    Using your second method, trying to create the second column (Column =
    SUMX (
    FILTER ( Table2, Table2[Index] <= EARLIER ( Table2[Index] ) ),
    Table2[Sort]
    ),

    the creation of it doesn't stop, i.e. it is already runs for days...

    So, I'm not sure what's wrong with it, but each table has around 1.6M records.

     

    Cheers

     

    Eli

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi Elirot

       

      It seems a measure is better than calculated column for your data. Here is the file for your reference.

      Group2 =
SUMX (
    FILTER ( ALL ( table2 ), Table2[Index] <= MAX ( Table2[Index] ) ),
    Table2[Sort]
)

      Regards,

      Cherie

      • Elirot's avatar
        Elirot
        Frequent Visitor
        7 years ago

        Hi Cherie,

         

        Well, I've tried with the measure as well, but unfortunately got an error message - lack of resources (see image), even if I filter it down only to the current year.

         

        Thanks

         

        Eli