Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all - I'm looking to create a "perfect invoice" measure in Power BI but I don't know the necessary DAX. Can someone help me?
Right now, I have a measure computing total number of invoices by running a DISTINCTCOUNT on the sales order reference numbers in my fact sales table.
From there, a "perfect invoice" is an invoice with no lines shorted or credited. I attempted to compute this using a CALCULATE function:
CALCULATE(
[Invoices],
fact_sales[shortquantity] = 0,
fact_sales[returnquantity] =0
)
However, this measure only removed the imperfect lines from the resulting table for calculation, and therefore counted any invoice with at least 1 perfect invoice line as entirely perfect.
Can anyone help me develop the correct version of this measure?
Hi @mpavlofsky
Is your issue solved?
If the issue has been solved, please adopt the solution to help others
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @mpavlofsky,
Could you provide sample data and expected output after removing sensitive data?
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mpavlofsky ,
Can you provide a small sample of your fact table data please?
I'm pretty sure the solution will be to use FILTER and && conditions, but need to understand your data format.
*EDIT* Try this:
_noofPerfectInvoices =
CALCULATE(
[Invoices],
FILTER(
fact_sales,
fact_sales[shortquantity] = 0
&& fact_sales[returnquantity] = 0
)
)
Thanks,
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |