Convert SQL to Dax (Count Distinct using Join)

[Anonymous]

I want perform follwing sql query operation in Powerbi using Measure. Also i want to filter result of these query based on date using date slicer

Query 1 :

select distinct( DocNo) from InvoiceHistory where StatusDescription IN ('3-WAY MATCH ERROR')
and statusid = '9e'
and ManualProcessingFlag <> '1' and DocNo Not in
(select distinct(DocNo) from InvoiceHistory where StatusDescription IN ('NEW','SUCCESSFULLY PROCESSED')
))

I have tried following measure to achieve similar result.

Measure = CALCULATE(
DISTINCTCOUNT(InvoiceHistory[DocNo]),
FILTER((InvoiceHistory), [StatusDescription]="3-WAY MATCH ERROR"
&& InvoiceHistory[StatusID]="9e"
&& InvoiceHistory[UpdatedDate] <= max(InvoiceHistory[DocDate])
&&InvoiceHistory[ManualProcessingFlag]<>1
&& NOT( (Invoice[DocNo] in
( CALCULATETABLE
(DISTINCT(Invoice[DocNo]),
FILTER((Invoice),(invoice[StatusDescription]="New" || Invoice [StatusDescription] ="SUCCESSFULLY PROCESSED")
)))))))
 

Please help.

[parry2k]

@Anonymous Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.