Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
54 | |
47 | |
19 | |
16 | |
15 |
User | Count |
---|---|
114 | |
44 | |
44 | |
28 | |
22 |