Forum Discussion

barragan82's avatar
barragan82
Icon for Helper II rankHelper II
11 months ago
Solved

DAX Measure for a Specific If and Then

Hello,

I’m hoping to get help with a DAX measure that can do all of the following.

  1. [Assignments]>=110,*.1 (ex. If the sum is 236, multiply by 0.1 to render 23.6)
  2. [Assignments]>=10&&[Assignments]<110,10 (ex. If the sum is 86, render the number 10)
  3. [Assignments]>=5&&[Assignments]<10,*1 (ex If the sum is 8, multiply by 1 to render 8.)
  4. [Assignments]<5,0 (ex. If the sum is 2, render 0)
  5. Provide a sum of the results from the “Measure” column, rather than applying the parameters above to the “Assignments” total. For example, render 41.6 rather than 33.2.

Below is a table with an example of the results I’d like to render (“Measure” column). The column “Assignments” is summarized by Sum (ie there are multiple Course As that sum to 236, etc.). I hope this all makes sense. Please let me know if more clarification is needed. I’ve been trying to create a DAX measure that could do this but have not had any success. Thank you for your help!

 

 

 

Need Help
  • Praful_Potphode's avatar
    Praful_Potphode
    11 months ago

    Hi barragan82  

    Please try below dax measure:

    Measure = 
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Course],
        "AssignmentsTotal", SUM ( 'Table'[Assignments] )
    ),
    VAR AssignmentsTotal = [AssignmentsTotal]
    var a=    IF(
        AssignmentsTotal>= 110, AssignmentsTotal * .1,
     IF(   AssignmentsTotal >= 10, 10,
   IF(     AssignmentsTotal>= 5, AssignmentsTotal,
        0
    )
)
    )
    RETURN a
)

    Please give Kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    11 months ago

    Hi barragan82 

    Try this:

    SUMX (
    VALUES ( table[course] ),
    SWITCH (
        TRUE (),
        [Assignments] >= 110, [Assignments] * .1,
        [Assignments] >= 10, 10,
        [Assignments] >= 5, [Assignments],
        0
    )
)
    • barragan82's avatar
      barragan82
      Icon for Helper II rankHelper II
      11 months ago

      Hi danextian , 

       

      Thank you for this DAX measure! Currently, the measure is applying the parameters to each individual row of data, not the sum. For example, there are three rows for Course A with Assignments values of 200, 30, and 6 to total 236. I am trying to apply the paramaters to the sum to get 23.6. Instead the parameters are being applied to each row to produce 20, 10, and 6 to get a total of 36. Any suggestions? Thank you!

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    11 months ago

    Hi barragan82  

    Please try below dax measure:

    Measure = 
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Course],
        "AssignmentsTotal", SUM ( 'Table'[Assignments] )
    ),
    VAR AssignmentsTotal = [AssignmentsTotal]
    var a=    IF(
        AssignmentsTotal>= 110, AssignmentsTotal * .1,
     IF(   AssignmentsTotal >= 10, 10,
   IF(     AssignmentsTotal>= 5, AssignmentsTotal,
        0
    )
)
    )
    RETURN a
)

    Please give Kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi barragan82 ,

    I would also take a moment to thank danextian  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions