Forum Discussion

tracy's avatar
tracy
Frequent Visitor
9 years ago

Unrelated Tables

Hi. I've tables that are not related as you can see in the picture below (no unique values in either one to create relationship). But, I need to pick a row from ItemTransaction table and check upon B...
Need Help
tracy's avatar
tracy
Frequent Visitor
9 years ago

The tables' sample is attached here. Both will return multiple rows when we filter by ScheduleID. Any suggestions on how to loop through row by row? 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi tracy,

     

    You can refer to below measure to calculate the total seconds:

     

    subtotal =
    SUMX(FILTER(ALL(ItemTransaction),ItemTransaction[ScheduleID]=MAX(BreakTime[ScheduleID])&&AND(MAX(BreakTime[BreakStartTime])<=ItemTransaction[StartTime],MAX(BreakTime[BreakEndTime])>=ItemTransaction[EndTime])),ItemTransaction[BundleTime])

     

    Tables:
    Breaktime.


     

    ItemTransaction.


     

    Result(add a calculate column to display the result):


     

    Notice: Since the sample data has no match records, I modified some records.

     

    Regards,
    Xiaoxin Sheng

    • tracy's avatar
      tracy
      Frequent Visitor
      9 years ago

      Hi Anonymous. Thanks for your reply. You have used MAX ItemTransaction[ScheduleID] = MAX(BreakTime[ScheduleID]) in this statement. Does that mean it will always pick the first row? Because somehow scheduleID will be the same for the selected rows in BreakTime table. 

      Sorry, I don't quite understand what your expression does. Do you mind explaining?

      I gave a sample condition only. But, in actual I've 4 conditions. Can I fit in them in the same measure?

       

      //If BreakTime is out of TrxTime, No BreakTime
if (vBreakTimeEnd <= pStartTime) { break; } 
if (vBreakTimeStart >= pEndTime) { break; }

//If TrxTime is between BreakTime
if (vBreakTimeStart <= pStartTime && vBreakTimeEnd >= pEndTime) 
   vTotalBreakTime += (pEndTime - pStartTime).TotalSeconds;
//if BreakTime is between TrxTime
else if (vBreakTimeStart >= pStartTime && vBreakTimeEnd <= pEndTime)
   vTotalBreakTime += (vBreakTimeEnd - vBreakTimeStart).TotalSeconds;
//if BreakTime starts before TrxTime
else if (vBreakTimeStart <= pStartTime && (vBreakTimeEnd >= pStartTime && vBreakTimeEnd <= pEndTime))
   vTotalBreakTime += (vBreakTimeEnd - pStartTime).TotalSeconds;
//if BreakTime starts after TrxStartTime
else if (vBreakTimeEnd >= pEndTime && (vBreakTimeStart >= pStartTime && vBreakTimeStart <= pEndTime))
   vTotalBreakTime += (pEndTime - vBreakTimeStart).TotalSeconds;

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi tracy,

         

        >>You have used MAX ItemTransaction[ScheduleID] = MAX(BreakTime[ScheduleID]) in this statement. Does that mean it will always pick the first row?


        It will get the current “scheduleID”, for more detail information about measure and calculate column, you can refer to below link:
        column vs measure

         

        According to your conditions, you want to get the “middle range” of tables and get the total minute of these records, right?

         

        You can try to use below formula to see if it works on your side.

        Measure:

        condition total = 
var currBreakStart=MAX(BreakTime[BreakStartTime])
var currBreakEnd=MAX(BreakTime[BreakEndTime])
var currScheduleID=LASTNONBLANK(BreakTime[ScheduleID],BreakTime[ScheduleID])
return
SUMX(FILTER(ALL(ItemTransaction),
ItemTransaction[ScheduleID]=currScheduleID&&AND(ItemTransaction[StartTime]<currBreakEnd,ItemTransaction[EndTime]>currBreakStart)),
DATEDIFF(MAX(ItemTransaction[StartTime],currBreakStart),MIN(ItemTransaction[EndTime],currBreakEnd),SECOND))

         

         

        If above is not help, please feel free to let me know.

         

        Regards,
        Xiaoxin Sheng