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.
- kcantor10 years agoCommunity Champion
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.