cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Calculated Column with multiple IF Else Then

Hi All,

I have two calculated Columns which I created in the Data view from other Columns. The Calculated Columns are [Number of Days] and [Action]

 

Number of DaysActionResult
42Green
62Yellow
82Red
153Blue
164Orange

 

I want to Have another Calculated Column , [Result]in the Data based upon following conditions;

If [Number of Days]<=5 and Action<=2 THEN Green

ELSE IF [Number of Days]>5 and  [Number of Days]<8 and Action<=2 THEN Yellow

ELSE IF [Number of Days]>=8 and Action<=2 THEN Red

ELSE IF [Number of Days]<=15 and Action<=3 THEN BLUE

ELSE IF [Number of Days]>15 and Action>3 THEN Orange

ELSE Violet

END

 

Please Help in writing the above to get [Result] Column values

Thanks

Deepak

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Calculated Column with multiple IF Else Then

@drrai66

The DAX is like

Column =
SWITCH (
    TRUE (),
    yourTable[Number of Days] < 5
        && yourTable[Action] <= 2, "Green",
    yourTable[Number of Days] < 8
        && yourTable[Action] <= 2, "Yellow",
    yourTable[Number of Days] >= 8
        && yourTable[Action] <= 2, "Red",
    yourTable[Number of Days] <= 15
        && yourTable[Action] <= 3, "blue",
    yourTable[Number of Days] >= 15
        && yourTable[Action] > 3, "Orange",
    "Violet"
)

View solution in original post

5 REPLIES 5
Highlighted
Resolver II
Resolver II

Re: Calculated Column with multiple IF Else Then

You can use IF and SWITCH function in DAX to do this. You can either use nested IFs (which should map pretty easily for your sample condition) or SWITCH with first parameter as TRUE and each condition representing an arbitrary condition expression. 

Highlighted
Resolver I
Resolver I

Re: Calculated Column with multiple IF Else Then

Hi SRINIVT,

I am Totally new to Power BI and I am stuck on this. Can you please do one more favour and write the Formula. I had tried earlier but could not do, so I posted here.

Thanks

Deepak

Highlighted
Microsoft
Microsoft

Re: Calculated Column with multiple IF Else Then

@drrai66

The DAX is like

Column =
SWITCH (
    TRUE (),
    yourTable[Number of Days] < 5
        && yourTable[Action] <= 2, "Green",
    yourTable[Number of Days] < 8
        && yourTable[Action] <= 2, "Yellow",
    yourTable[Number of Days] >= 8
        && yourTable[Action] <= 2, "Red",
    yourTable[Number of Days] <= 15
        && yourTable[Action] <= 3, "blue",
    yourTable[Number of Days] >= 15
        && yourTable[Action] > 3, "Orange",
    "Violet"
)

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Calculated Column with multiple IF Else Then

Thanks and Many Many Thanks. Learnt something new from you and Solved my Problem. I used your Formula at Many many places in my dataset and it works like Charm!!!

Thanks

Deepak

Highlighted
Microsoft
Microsoft

Re: Calculated Column with multiple IF Else Then

Glad to help. 🙂

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors