Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Help with DAX

Hi i'm trying to create a point calculation with this formula: 

1. Total Point = Distinct Count of Style#-Season * Base Point * Complexity
2. Complexity = 2 when Style Merch = B1 or O1, or when Model_Construction = S1 or S2 or S3, otherwise it's factor 1 ( no change)

3. If Marketing Type = P, add 3 points to Base Point, otherwise add 0 (no change)

I have a table like below:

Style#SeasonStyle#-SeasonFitLine Style_MerchModel_ConstructionMarketing TypeBase Point
ABC101Spring 23ABC101-Spring23Western R1B1S1P5
ABC102Summer 23ABC102-Summer23Asian R2O1S2not P4
ABC102Fall 23ABC102-Fall23GlobalR3Not B1 or O1S3not P3
ABC103Holiday 23ABC103-Holiday23PlusR4Not B1 or O1Not S1 or S2 or S3P2
ABC103Spring 23ABC103-Spring23WesternR5Not B1 or O1Not S1 or S2 or S3P1

 

Need Help

11 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    Anonymous 

    below is the formula for the 2&3.  I am still not clear about the first one. could you pls provide elaberate on that? What's the output based on the sample data you provided?

     

    _2 = if('Table'[Style_Merch] in {"B1","O1"}|| 'Table'[Model_Construction] in {"S1","S2","S3"},2,1)
    _3 = if('Table'[Markeing Type]="P",3,0)
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you! 

      1 Should be the output whereby it computes the total point. 

       

      The formula should be: distinct count of style#-season * base point (which should take into account #3) * complexity, i wish to have a measure that can sum this up. 


  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    create a measure for 

    Complexity =
    IF( MAX('Table'[Style_Merch]) IN {"B1","O1"},2,IF(MAX('Table'[Model_Construction]) IN {"S1","S2","S3"},2,1))

    After That create a measure for Total Points
    Total Point =
     var a =  DISTINCTCOUNT('Table'[Style#-Season])
     return a*MAX('Table'[Base Point])*[Complexity]
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you! Can you explain how do I add the criteria of Marketing Type into the measure as well?

      3. If Marketing Type = P, add 3 points to Base Point, otherwise add 0 (no change)

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Anonymous use below measure for Total Point

        Total Point =
         var a =  DISTINCTCOUNT('Table'[Style#-Season])
         var basePoint = MAX('Table'[Base Point])
         var addTbase = IF(MAX('Table'[Markeing Type])="P",3,0) + basePoint
         return a*addTbase*[Complexity]