How to convert this to measure?

[Anonymous]

Hi,
Here I have the qurey which is used to get count.
Now my taks is to create measure in Power BI for thsi below query.

select  count(*)
from factARInvoices
where Account_SK in (select Account_SK  from factARInvoices where InvoiceID='CAN005578INV' ) and
InvoiceDate <(select InvoiceDate from factARInvoices where InvoiceID='CAN005578INV' ) and GETDATE() > DueDate;

Can anyone please help me to do this.

Thanks in advance.

[amitchandak · Accepted solution]

@Anonymous , Try like

measure =
var _account = selectcolumns(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV"),"Account_SK" , factARInvoices[Account_SK])
var _date = maxx(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV"), factARInvoices[InvoiceDate])
return
calculate(countrows(factARInvoices), filter(factARInvoices, factARInvoices[Account_SK] in _account && factARInvoices[InvoiceDate] <_date && factARInvoices[DueDate] <=today())

or

measure =
var _account = maxx(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV") , factARInvoices[Account_SK])
var _date = maxx(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV"), factARInvoices[InvoiceDate])
return
calculate(countrows(factARInvoices), filter(factARInvoices, factARInvoices[Account_SK] = _account && factARInvoices[InvoiceDate] <_date && factARInvoices[DueDate] <=today())

[amitchandak · Accepted solution]

@Anonymous , Try like

measure =
var _account = selectcolumns(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV"),"Account_SK" , factARInvoices[Account_SK])
var _date = maxx(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV"), factARInvoices[InvoiceDate])
return
calculate(countrows(factARInvoices), filter(factARInvoices, factARInvoices[Account_SK] in _account && factARInvoices[InvoiceDate] <_date && factARInvoices[DueDate] <=today())

or

measure =
var _account = maxx(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV") , factARInvoices[Account_SK])
var _date = maxx(filter(factARInvoices,factARInvoices[InvoiceID]="CAN005578INV"), factARInvoices[InvoiceDate])
return
calculate(countrows(factARInvoices), filter(factARInvoices, factARInvoices[Account_SK] = _account && factARInvoices[InvoiceDate] <_date && factARInvoices[DueDate] <=today())

[Anonymous]

Thanks @amitchandak.
Its works fine but i want to do another feature in this, which is the InvoiceID was choosen dynamically by slicer.
If you know any kind of work around, Can you please help me on that.

[amitchandak · Accepted solution]

@Anonymous , in place of hardcoded invoice no 

use

= selectedvalue(invoice[invoiceno])

if more than one 

in allselected(invoice[invoiceno]) 

or

in values(invoice[invoiceno])

[Anonymous]

Thanks  once again @amitchandak , Values(invoiceno) works .