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.
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.
Elegance is over rated. If you need to hammer in a finish nail and all you have is a framing hammer, make it work.
Thanks a bunch Greg_Deckler. You always have the best, simple answers for me.