topic Left Outer join with DAX in DAX Commands and Tips

Left Outer join with DAX

Earkkaperk — Thu, 05 Sep 2024 03:46:28 GMT

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

bhanu_gautam — Thu, 05 Sep 2024 06:30:32 GMT

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