Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating a calculated column based on several conditions

I have the table above. I need to add a column based on the Trade and StageOrder columns.

 

For example, if the trade is plumbing and the stageorder is between 2 and 6 then = ground

and if stageorder is between 7 and 12 then = topout

and if stageorder is 13 or higher then = trim

 

How can I write code to achieve this

Need Help

3 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    4 years ago

    Hi @ ,

     

    You acn write a DAX someting as below for the calculated column:

    newCalc =
IF (
    yourtable[Trade] = "Plumbing"
        && ( yourtable[StageOrder] >= 2
        && yourtable[StageOrder] <= 6 ),
    "ground",
    IF (
        yourtable[StageOrder] >= 7
            && yourtable[StageOrder] <= 12,
        "topout",
        IF ( yourtable[StageOrder] >= 13, "trim", "N/A" )
    )
)

    I have given a else condition as last one as N/A. You can modify it based on your requirement.

    Replace yourtablename in above DAX with your actual table name.

     

    Thanks,

    Pragati

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for your response.

     

    Why is the column not being recognized unless I use an aggregate? My data source is an excel workbook

     

    Thank you

     

     

     

     

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Anonymous ,

      Because the column "builderStageOrder" is not in the same table with the calculated column, so you can't quote it directly. Have you got the expected result by using an aggregate? If not, can you illustrate the relationship (cardinality and direction) of the "HousesLastMilestone" table and "Builder Stages" table?

      Best Regards,
      Community Support Team _ kalyj