Forum Discussion

Earkkaperk's avatar
Earkkaperk
Frequent Visitor
1 year ago

Left Outer join with DAX

I have 3 table here

 

This is what I get in PBI when I created relationship with 1-many ( machine status to facttable) and (alarmcode to facttable)

but I miss the result of loss duration in some parts (ex. 9.08AM)

 

This is when I drag Machinestatus out, you can see the result of 9.08 AM

 

 

So I think I need to do some leftouterjoin but I cannot do this with power query

Is there any solution for this?

Thank you

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Earkkaperk , You can try

     

    You can create a calculated column in your fact table that calculates the loss duration based on the relationships you have set up

    LossDuration =
       RELATED(MachineStatus[Duration])
     
    Create a measure for aggregation
    TotalLossDuration =
       SUM(FactTable[LossDuration])
     
    Than you can use DAX for left outer join
     
     LossDuration =
       LOOKUPVALUE(MachineStatus[Duration], MachineStatus[MachineID], FactTable[MachineID])