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

tracy's avatar
tracy
Frequent Visitor
9 years ago

Hi Anonymous, 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi tracy,

     

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

     

    Regards,

    Xiaoxin Sheng

    • tracy's avatar
      tracy
      Frequent Visitor
      9 years ago

      Hi Anonymous, 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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