Complicated criteria

anongard

I would suggest the following:
(sample pbix here)

1. Normalize your data to avoid duplication of Company attributes, so that you have
   • A Company table containing Company ID, Authorized Users, Activation date
   • An Assignment table containing Company ID, Assignment and Assignment Completion Date
   • These are related on the Company ID columns.
     (note my date formats below are d/mm/yyyy)
     Company table

      

      

     Assignment table

      

2. Create measures as follows:
   

   Threshold Reached On (Date) = 
   IF (
       // Evaluate only for one company
       HASONEVALUE ( Company[Company ID] ),
       VAR Threshold =
           VALUES ( Company[Authorized Users] ) * 0.5
       RETURN
           // Find the earliest date such that the threshold has been reached.
           // If the threshold is never reached, BLANK is returned.
           MINX (
               FILTER (
                   VALUES ( Assignment[Assignment Completion Date] ),
                   VAR CurrentRowCompletionDate = Assignment[Assignment Completion Date]
                   RETURN
                       CALCULATE (
                           COUNTROWS ( Assignment ),
                           Assignment[Assignment Completion Date] <= CurrentRowCompletionDate
                       )
                       >= Threshold
               ),
               Assignment[Assignment Completion Date]
           )
   )

   Threshold Reached On (Text) = 
   IF (
       HASONEVALUE ( Company[Company ID] ),
       VAR ThresholdReachdOn = [Threshold Reached On (Date)]
       RETURN
           IF (
               ISBLANK ( ThresholdReachdOn ),
               "Did Not Reach",
               FORMAT ( ThresholdReachdOn, "M/DD/YYYY" )
           )
   )

3. Then the measures produce these results (the final text measure formatted as m/dd/yyyy):

    

    

   You could do the same thing without normalizing, and the DAX would be pretty much the same apart from just having a single table name. I just think it's a good safeguard to ensure you don't by chance have different Company attributes for the same company on different rows.

Regards,

Owen