cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anongard
Helper I
Helper I

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

1 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 tableCompany table

       

       

      Assignment 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):Capture output.PNG

     

     

    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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

8 REPLIES 8
GilbertQ
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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. 

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

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! 

 

help.JPG

 

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. 

Hi @anongard,

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

1.PNG

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

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

@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 tableCompany table

       

       

      Assignment 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):Capture output.PNG

     

     

    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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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! 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.