cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tracy
Frequent Visitor

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

 

9 REPLIES 9
tracy
Frequent Visitor

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? 

Tables.jpg

 

v-shex-msft
Community Support
Community Support

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.

Capture.PNG
 

ItemTransaction.

Capture2.PNG
 

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

Capture3.PNG
 

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

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft. 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;

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft, I created a measure as you had given. But, the calculated column using your previous expression (TotalSecond = IF([Condition Total]=BLANK(),0,[Condition Total])) throws this error:
A circular dependency was detected: BreakTime[Column], BreakTime[TotalSecond], BreakTime[Column]

 

Also, I actually want to update the total seconds in ItemTransaction table like below. I want the calculated break time beside each employee's record. Is this possible? 
***Very importantly, can you advise me if we can put the other conditions in the same measure pleaseee?

ConditionalLogic.jpg

v-shex-msft
Community Support
Community Support

Hi @tracy,

 

Below is my pbix file, please check it if it works on your side.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft, I'm just enlightening you that I want the break time total seconds to be put into ItemTransaction table as per what I've stated above. Is that possible or not? Please advise. 

 

And FYI, when I tested your measure at my end, my result is different from yours. See here.ItemTransaction.jpg

v-shex-msft
Community Support
Community Support

Hi @tracy,

 

>> I'm just enlightening you that I want the break time total seconds to be put into ItemTransaction table as per what I've stated above. Is that possible or not?

 

Yes, it is possible.  In my opinion, dax query not good at replace values with multiple conditions. It is hard to loop multiple tables and replace the value at same time. Perhaps you can use power query.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft, I don't mind using power query too. Is there any basic or simple example that shows looping through conditions for unrelated tables? I really need to find a solution to produce break time into the ItemTransaction table.

As far as I browsed through, I see that Power Query is used to customize columns and its values. 

Please redirect me if you know of any sample. It'll be very helpful to me. Thanks. 

 

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors