Translate IF, ELSE IF, ELSE in DAX langage

[PBIBeginner2022]

Hello everybody,

I am new to Power BI with DAX language, can you help me to translate this into DAX language please.

IF (Category1 = "true") {

              IF ( type = "type1"  && release date < extract date) {

                            Status = "Status1"

              }

              ELSE { Status = "Status2"

}

              IF ( type = "type2") {

                            Status = "Status3"

              }

              IF (type = "type3") {

                            Status = "Status4"

              }

}

ELSE {

              IF (type = "type1" && release date < extract date) {

                            Status = "Status3"

              }

              IF(type= "type2) {

                            Status = "Status3"

              }

              IF(type = "type3") {

                            Status = "Status2"

}

}

I will be grateful for any help.

Sincerely,

[vojtechsima]

Hi, @PBIBeginner2022 
Hi you can nest SWITCH(TRUE() into each other like this:

SWITCH(TRUE(),

    SWITCH(TRUE(),
        'Table'[Column] = "type1" && 'Table'[Column] < 'Table'[Column], "Status 1",
        "Status 2"
    ),
     SWITCH(TRUE(),
        'Table'[Column] = "type1" && 'Table'[Column] < 'Table'[Column], "Status 3",
        'Table'[Column] = "type1" && 'Table'[Column] < 'Table'[Column], "Status 4",
        "Status 5"
    )
)

This is the baseline, but you can take this and apply it to your need. 

[PBIBeginner2022]

I don't understand why you need to add "true()" after the switch function. I need to verify if category = "True" before in my example

[vojtechsima]

@PBIBeginner2022 
Basically, you asking if the statements that you write are true, if yes, you return the result, if not, you continue into next line.

Or you can write it in your case as 

IF (Evalution,
(SWITCH(TRUE(), conditions, result)) // <- if evaluation true,
(SWITCH(TRUE(), conditions, results)) //<- if evalution false
)

[PBIBeginner2022]

I can rephrase my message. I want to display in a column the status of my orders. There are 4 different status. I want to create a string variable called Status_Order with the VAR function of the DAX language. I want the value of my Status_Order variable to be according with the conditions below in a calculated column. I attach here a screenshot of what I want to achieve in DAX language.

To know which status is associated with an order, there are 3 conditions:

• Category1 = "True" or "False
• Type = type1 or type2 or type3 or type4 or type5
• release date < extraction date or release date > extraction date.

And at the end of the condition, I want the Status_Order take the value of Status1 or Status2 or Status3 or Status4.

Thanks in advance,

Best regards,

[vojtechsima]

@PBIBeginner2022 
Hi, 
I think I know what you want, please give me copyable data in text form that I can paste into Power BI and deliver a tailor-made solution for you with a description of how it works.

Thanks

[PBIBeginner2022]

I tried to do my best, please let me know if this works for you.

The columns are :

• Order
• Category1
• Extraction date
• Release date
• Type
• Status_order (This is what I am trying to calculate with the table above)

Order	CATEGORY1	EXTRACTION DATE	RELEASE DATE	TYPE	Status_order
ORD1	TRUE	01/01/2022	01/02/2022	TYPE1
ORD2	FALSE	01/02/2022	01/03/2022	TYPE2
ORD3	FALSE	01/02/2022	01/01/2022	TYPE3
ORD4	TRUE	01/01/2022	01/03/2022	TYPE2
ORD5	TRUE	01/01/2022	01/02/2022	TYPE1
ORD6	FALSE	01/04/2022	01/01/2022	TYPE2
ORD7	FALSE	01/04/2022	01/04/2022	TYPE3
ORD8	FALSE	01/01/2022	01/02/2022	TYPE2
ORD9	FALSE	01/03/2022	01/01/2022	TYPE2
ORD10	TRUE	01/02/2022	01/01/2022	TYPE1
ORD11	FALSE	01/04/2022	01/01/2022	TYPE1

[vojtechsima · Accepted solution]

Hi, @PBIBeginner2022 
Could you please check that it is correct?

Status_Order_Calculated = 

IF ( IF_Else[CATEGORY1] = TRUE(),
    SWITCH(TRUE(),
        IF_Else[TYPE] = "TYPE2", "Status3",
        IF_Else[TYPE] = "TYPE3", "Status4",
        IF_Else[TYPE] = "TYPE1" && IF_Else[RELEASE DATE] < IF_Else[EXTRACTION DATE], "Status1",
        "Status2"
    ),
    SWITCH(TRUE(),
        IF_Else[TYPE] = "TYPE2", "Status3",
        IF_Else[TYPE] = "TYPE3", "Status2",
        IF_Else[TYPE] = "TYPE1" && IF_Else[RELEASE DATE] < IF_Else[EXTRACTION DATE], "Status3"
    )
)

 OrderCATEGORY1EXTRACTION DATERELEASE DATETYPEStatus_orderStatus_Order_Calculated

ORD1	True	01.01.2022	01.02.2022	TYPE1		Status2
ORD2	False	01.02.2022	01.03.2022	TYPE2		Status3
ORD3	False	01.02.2022	01.01.2022	TYPE3		Status2
ORD4	True	01.01.2022	01.03.2022	TYPE2		Status3
ORD5	True	01.01.2022	01.02.2022	TYPE1		Status2
ORD6	False	01.04.2022	01.01.2022	TYPE2		Status3
ORD7	False	01.04.2022	01.04.2022	TYPE3		Status2
ORD8	False	01.01.2022	01.02.2022	TYPE2		Status3
ORD9	False	01.03.2022	01.01.2022	TYPE2		Status3
ORD10	True	01.02.2022	01.01.2022	TYPE1		Status1
ORD11	False	01.04.2022	01.01.2022	TYPE1		Status3

[PBIBeginner2022]

Hi @vojtechsima ,

When you use IF_ELSE, is it a DAX function ? Because Power BI don't recognize it.

[vojtechsima]

 @PBIBeginner2022
"Else" isn'T DAX function, however, if you want to do it in power query M, then there's IF ELSE syntax.
Also, please check my solution, did you manage to solve it using my post?

IF_Else[TYPE] = "TYPE2", "Status3",

 The "IF_Else" here is name of Table and [TYPE] is name of a column in that table.

[PBIBeginner2022]

Thanks @vojtechsima , I think it's almost good. I don't see the case when "Catégorie1 = FALSE" in your solution

[vojtechsima]

Hi, @PBIBeginner2022 
This highlighted area is the "FALSE", since you can have only two states of the column, it first checks if it'S "TRUE" and then if not (e.g. FALSE) it does the highlighted thing.

[PBIBeginner2022]

Thanks for all @vojtechsima . Could you juste explain me the function with SWITCH(TRUE()) please, I need to explain my code to people

[vojtechsima]

Hi, @PBIBeginner2022 
The SWITCH Evaluates an expression against a list of values and returns one of multiple possible result expressions. This is the original use and it's quite limited, that's why you can add as an expression "TRUE()" and then you can write multiple conditions and evaluate each of them.

Basically, the switch look at first value and evaluate if it's true, if it's true it return the Result you mention and exit the function. If the first row/value isn't true it evaluates the next row.
If non of the rows are TRUE, it can return a default value.

SWITCH(TRUE(),
'Table'[Column] = "Green", "This is a green color",
'Table'[Column] = "Black", "This is a black color",
"This is a red color")

This will take column named Column from table called Table an evaluate if the current value that is being evalued equals to Green, if it's true, it return "this is green color", if it's not and it's black it will return the next row, if the color isn't either green or black, it return default value "red".

Article about Switch TRUE():
https://blog.enterprisedna.co/using-switch-true-logic-in-power-bi-dax-concepts/

If I helped you in any way, please consider giving me Kudos (thumbs up) and mark my message as the solution.
THank you