Forum Discussion

anongard's avatar
anongard
Helper I
9 years ago
Solved

Complicated criteria

Hello all,

 

I've got (what I believe to be) a tough one for you. I have a list of approximately 4 million records, all of which have several components. 

 

Backstory, because due to the nature of the data I cannot paste it here: 

 

There are several thousand accounts taking educational assignments, each of which has a unique company Account ID. This table contains how many Users are in each account, the account Activation Date, and the Date that each Assignment is completed. Each account has several (sometimes hundreds) of assignments it has completed, each of which is a new record.

 

We are trying to figure out the 'Time to Value' for each account, or how long it takes for an account to reach an arbitrary threshold of completed assignments. For our purposes testing this out, we are using .5*Users. 

 

Example: Company A has 100 Users, and over the course of the year they complete as many assignments as they like, each of which is recorded with a date stamp. How many days, from the Activation Date, does it take for Company A to complete .5*100=50 assignments? 

 

I have no idea how to set this up, but it seems like a complex string of very simple commands. Any and all help is appreciated. 

 

Thanks,

Alex

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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

8 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi anongard

     

    Why do you not create a calculated column in the Query Editor which can calculate how many days it has been since the Activation Date.

     

    Then load this data and then create a measure based on your criteria below.

    Create a second measure which is based on the Days (Activation Date)

     

    Once you have the two measure above you could then compare them and see how is above and who is below?

    • anongard's avatar
      anongard
      Helper I
      9 years ago

      What measure could I use that would give me the date of the .5*xth assignment, where x=user count, for each individual account? Maybe my DAX is not advanced enough, but I don't know how to get that far. 

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi anongard,

        For the .5*xth assignment, you can use the similar formula.

        =5*count(Table[userID])


        You also can create a calculated column to get result for each individual account using the formula like below. 

        =CALCULATE(5*count(Table[userID]),ALLEXCEPT(Table,Table[account]))


        In addition, I totally understand your data is private, you can create a sample table and list the expected result, so that we can provide the solution which is close to your requirement.

        Best Regards,
        Angelia