Perfect Invoice measure?

[mpavlofsky]

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? 

[v-xulin-mstf]

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!

[v-xulin-mstf]

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.

[BA_Pete]

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