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

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!