Convert SQL to Dax (Count Distinct using Join)

Anonymous — Sat, 04 May 2019 12:42:08 GMT

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.

Re: Convert SQL to Dax (Count Distinct using Join)

parry2k — Mon, 06 May 2019 16:03:56 GMT

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

topic Convert SQL to Dax (Count Distinct using Join) in DAX Commands and Tips

Convert SQL to Dax (Count Distinct using Join)

Re: Convert SQL to Dax (Count Distinct using Join)