Dax for Self Join Table

[pityman]

Hi,

How do i convert the following SQL to Dax?

select COUNT(*) from TDRep TDR , TDPSLink TDPSL where TDR.TDId = TDPSL.TDID AND TDR.ToStatus = '7' AND (TDPSL.StatusId != 12 OR TDPSL.StatusId != 10)

Thank you

[v-jayw-msft · Accepted solution]

Hi @pityman ,

Refer this measure.

Measure = CALCULATE(COUNT(TDRep[TDId]),FILTER(TDRep,TDRep[TDId] in VALUES(TDPSL[TDId])&&TDRep[ToStatus ]=7),FILTER(TDPSL,TDPSL[StatusId]<>12||TDPSL[StatusId]<>10))

Best Regards,

Jay

[amitchandak]

@pityman , Both tbale should have join in power bi on TDID, create a measure like

calculate(countrows(TDRep), filter(TDRep, TDRep[ToStatus] ="7"), filter(TDPSLink,TDPSLink[StatusId] <> 12 || TDPSLink[StatusId]<> 10))