Forum Discussion

kcantor's avatar
kcantor
Community Champion
10 years ago
Solved

How to filter distinct count

Greg_Deckler and MattAllington You are the first to come to mind on this question. It should be fairly simple for those of your ability levels . . . I have a sales order number on my table with an ...
  • Greg_Deckler's avatar
    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.