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 o...
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

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

anongard's avatar
anongard
Helper I
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      anongard
      Helper I
      9 years ago

      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's avatar
        OwenAuger
        Super User
        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