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 BreakTime table to find under which breaktime this transaction time (StartTime & EndTime) falls into based on the ScheduleID. Is there a way to do this in Power BI desktop? Since, the tables are not connected, my collegue used Visual Studio coding to compute the logic. Somehow, I'm trying to apply this logic with Power BI. Is that possible? Please help.

foreach (DataRow dr in drArray)
{
//If TrxTime is between BreakTime
if (vBreakTimeStart <= pStartTime && vBreakTimeEnd >= pEndTime) 
vTotalBreakTime += (pEndTime - pStartTime).TotalSeconds;
}

 

Need Help

9 Replies

  • 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;