Forum Discussion
How to filter distinct count
- 10 years ago
If I understand the problem correctly, you could do a rather brute force and unelegant way.
Have a separate table for your Sales Orders which is all unique values. Relate your Invoices table to your Sales Orders table. In your Sales Orders table, create a column that is essentially (psuedo-code):
Number of Invoices = COUNTROWS(RELATED([Invoices]))
You could also do this as a measure.
Now, create another column that is:
Number of Same Day Invoices = CALCULATE(COUNTROWS(RELATED([Invoices])),[Order Date] = [Invoice Date])
This could also be a measure that instead of repeating the formula just referenced your previous measure.
Create a final column/measure like:
All Same Day = IF([Number of Invoices] = [Number of Same Day Invoices], "Y", "N")
Now you should be able to easily determine how many Sales Orders had all of their associated Invoices post on the same day.
Do you have 1 table with this structure:
Sale Order Invoice Item OrderDate PostingDate
SO001 INV1 1 01/01/2016 01/01/2016
SO001 INV1 2 02/01/2016 03/01/2016
SO001 INV1 3 01/01/2016 01/01/2016
SO002 INV2 1 01/01/2016 01/01/2016
SO002 INV3 1 01/01/2016 01/01/2016
SO003 INV4 1 01/01/2016 01/01/2016
SO003 INV4 2 01/01/2016 01/01/2016
SO003 INV5 1 01/01/2016 02/01/2016
Or have 2 tables Sale Orders and Invoices