Creating a calculated column based on several conditions

[Anonymous]

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

[Anonymous]

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]

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

[Pragati11]

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