Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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 , in place of hardcoded invoice no
use
= selectedvalue(invoice[invoiceno])
if more than one
in allselected(invoice[invoiceno])
or
in values(invoice[invoiceno])
@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())
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.
@Anonymous , in place of hardcoded invoice no
use
= selectedvalue(invoice[invoiceno])
if more than one
in allselected(invoice[invoiceno])
or
in values(invoice[invoiceno])
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |