Forum Discussion

yfeng0's avatar
yfeng0
Frequent Visitor
6 years ago
Solved

[Direct Query] how to create calculated column in 1 table based on filtered value on another table?

Hello all,

 

I'm using direct query mode for these two tables, so I can't use Powerquery in this case.

 

MISSION_HEADER and MISSION_DATA

these two have a many to one relationship between column GKEY and HEADER_GKEY.


I want to create a calculated column in the MISSION_HEADER table

to get the difference of both times.
(MISSION_HEADER[Exit_Time]-MISSION_HEADER[ENTERED_TIME])
 
but I want to calculate only calculate when another column in the MISSION_DATA table.
MISSON_DATA[N4_MISSION_TYPE] = "RE"
 
 
Need Help
  • yfeng0's avatar
    yfeng0
    6 years ago

    I had found a solution

     

    if you use the average function from the Fileds panel, it will average the total.

    in order to average some transactions by filtering certain values, you have to use the AVERAGE function.

     

    first, create the calculated column of two times to get the differences.

    Turn Time-Mins = (MISSION_HEADER[Exit_Time]-MISSION_HEADER[ENTERED_YARD])*24*60
     
    then create a measure to average the transaction by filtering 
     
    RE AVG Turn time = calculate(average(MISSION_HEADER[Turn Time-Mins]),filter(MISSION_DATA,MISSION_DATA[N4_MISSION_TYPE]="RE"))

5 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi yfeng0 ,

     

    Based on having create relationship between the two tables, and change the Cross filter direction of the relationship from Single to Both , you can create column like DAX below.

     

    Turn Time-Mins = CALCULATE(MISSION_HEADER[Exit_Time]-MISSION_HEADER[ENTERED_TIME], FILTER(MISSION_DATA, MISSION_DATA[HEADER_GKEY]= MISSION_HEADER[GKEY] && MISSON_DATA[N4_MISSION_TYPE] = "RE"))

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • yfeng0's avatar
      yfeng0
      Frequent Visitor
      6 years ago

      Thank you Amy,

       

      I tried the measure

      but got an error message.

       

      • yfeng0's avatar
        yfeng0
        Frequent Visitor
        6 years ago

        I don't know what I did wrong the first time,

        this is my 2nd try,

        got another error message.

         

        is it because the exit time and entered time are both columns in the table?