Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Loop through rows under condition and count difference

I have timestamps under each other in one column, have detected the rows where full time period starts and ends thanks to indexing. Now, how do I lopp through the column and sum the whole time in bet...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    Based on my simplified sample data, the measure could get the datediff between the start and end time.

    Measure = var last_start=CALCULATE(MAX('Time'[time]),FILTER(ALL('Time'),'Time'[time]<=MAX('Time'[time])&&'Time'[end/start]="start")) return IF(MAX('Time'[end/start])="end",DATEDIFF(last_start,MAX('Time'[time]),HOUR))

    If you need column,you may try below dax.

    Column = var last_start=CALCULATE(MAX('Time'[time]),FILTER('Time','Time'[time]<=EARLIER('Time'[time])&&'Time'[end/start]="start")) return IF('Time'[end/start]="end",DATEDIFF(last_start,'Time'[time],HOUR))

    Regards,

  • Anonymous's avatar
    Anonymous
    7 years ago

    The measure you have posted did not work for me, but I've used a different approach - adding new index column to the table and then merging the table on itself.

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

Hi Anonymous 

You may try to create a measure like below.

Measure =
VAR last_start =
    CALCULATE (
        MAX ( 'Time'[time] ),
        FILTER (
            ALL ( 'Time' ),
            'Time'[time] <= MAX ( 'Time'[time] )
                && 'Time'[end/start] = "start"
        )
    )
RETURN
    IF (
        MAX ( 'Time'[end/start] ) = "end",
        DATEDIFF ( last_start, MAX ( 'Time'[time] ), HOUR )
    )

Regards,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you for the help. However, the solution does not work for me. How can you make this measure iterate over the  whole table with more than one start-end period possible for every id? v-cherch-msft 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Because the column looks like this, with more specified periods for one id. v-cherch-msft More sample rows

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      The measure you've provided just does not do the job. I've separated the times into two columns and just need do substract them from each other. If there is end time provided, substract start time from it otherwise from now time substract start time. As in pictures provided.

       

      snippetmeasure

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

        Hi Anonymous 

        If you have resolved the issue, please mark the right reply as answer, If not, please share more details for us so that we could help further on it.

        Regards,