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,

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

    • anongard's avatar
      anongard
      Helper I
      9 years ago

      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!