Complicated criteria

[anongard]

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

[GilbertQ]

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]

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]

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

[anongard]

Thank you for the feedback! I don't think I was clear in my initial post, so I've created a sample table that I think may illuminate what it is I'm looking for. From the table below, I have all of the data except the "Threshold Reached On" column, which ideally I could create as a new table. The formulas that you gave me in the previous post did not really help, unfortunately. I don't know what you meant when you said [account], but trying a variety of my inputs - Account ID, # of Users, etc. yielded nothing very helpful. If you can clarify what you meant by that, as well as what exactly it's supposed to measure, I would appreciate it. Thank you! 

P.S. I don't seem to be able to post tables because of HTML formatting errors. If there's a better way to post them (other than unformatted mess), let me know. 

[v-huizhn-msft]

Hi @anongard,

If you want to get the following results, please click New Table->Under Modeling on Home page, type the following formula.

New=SUMMARIZE(Table,Table[CompanyID],"Threshold Researched on",MAX(Table[Assignment Completion Date]))

 For the issue above: 'does it take for Company A to complete .5*100=50 assignments?' I still confuse, if you still have other problems, please feel free to ask.

Best Regards,
Angelia

[anongard]

Thank you, this is very helpful! It is still not quite the whole picture. I'm sorry if I wasn't clear with what I said. What I mean is this:

Say a company has 50 authorized users. The company as a whole has completed 100 assignments over six months. They each have timestamps. I want to know when the 25th one out of 100 was completed. I get the 25th by multiplying 50*.5. So I need the date that the company has completed [user count]*.5 number of courses. 

Each company has a different number of users and has completed a different number of courses. If you take a look at the picture I posted earlier, you can see that the "Threshold Reached On" column shows the dates that each company reached [user count]*.5 number of courses completed. Does that makes sense? 

Alex

[OwenAuger · Accepted solution]

@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 tableAssignment 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

[anongard]

Thank you so much!! This worked perfectly, it was exactly what I was looking for. I just don't know enough DAX to be able to code that on my own. I appreciate your help!