Ayuda con nested if/and/or

[JuanDtM23]

Best regards! I hope you can help me.

I have a table that contains service approval requests and I want to calculate the opportunity for each request, for this I use two columns:

the first is called "UBICACION_SOLICITUD" which contains the three possible types of services:

"EXTERNAL CONSULTATION"

"Nice stay"

"NICE STAY"

Likewise, the second column called "DIAS_APROBACION_" contains the number of days it took for the application to be approved.

De according to the type of request in the "REQUEST LOCATION" column there is a limited time for authorization to be timely, these are:

EXTERNAL CONSULTATION <TM 5

HOSPITALARY & EMERGENCY <1

In that order of ideas I want to calculate a column that tells me which records were appropriate, and which were not, I try as follows but get a syntax error.

Columna = if(

 [UBICACION_SOLICITUD] = "EXTERNAL CONSULTATION" && [DIAS_APROBACION_] <=5,

 "TIMELY AUTHORIZATION",

 IF(

 [UBICACION_APROBACION] = "HOSPITAL" || [UBICACION_APROBACION] = "URGENCIA" && [DIAS_APROBACION_] <> blank && [DIAS_APROBACION_] <=5,

 "TIMELY AUTHORIZATION",

 IF(

 [DIAS_APROBACION_] = blank,

 "NO APPROVAL DATE",0)

 )

)

I hope you can help me, Thank you very much in advance.

[AlB]

Hi @JuanDtM23 

What does the error say exactly?

Try BLANK() instead of BLANK only

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[JuanDtM23]

Hello @AlB

The error is as follows:

"The syntax is not correct"

and if I use blanck() instead of blank I get the following:

"the expressioons that produce variant data types cannot be used to define computed columns"

attached on . Pbix: https://mutualsereps-my.sharepoint.com/:u:/g/personal/jtorres_mutualser_org/EXU70n2B2dRNh94N-LUkvkoB...

[AlB · Accepted solution]

@JuanDtM23

The problem is exactly what the error message says. He's returning a mixture of text and numbers. That's not allowed,a column can have only one data type. The number is zero at the end. One option would be to return that zero as text:

Columna =
IF (
    [UBICACION_SOLICITUD] = "CONSULTA EXTERNA"
        && [DIAS_APROBACION_] <= 5,
    "AUTORIZACION OPORTUNA",
    IF (
        [UBICACION_APROBACION] = "HOSPITALARIO"
            || [UBICACION_APROBACION] = "URGENCIA"
                && [DIAS_APROBACION_] <> BLANK ()
                && [DIAS_APROBACION_] <= 5,
        "HOSPITALIZACION OPORTUNA",
        IF ( [DIAS_APROBACION_] = BLANK (), "SIN FECHA DE APROBACIÓN", "0" )
    )
)

Another option would be to return nothing instead of that zero. The column will have a blank space in that row:

Columna =
IF (
    [UBICACION_SOLICITUD] = "CONSULTA EXTERNA"
        && [DIAS_APROBACION_] <= 5,
    "AUTORIZACION OPORTUNA",
    IF (
        [UBICACION_APROBACION] = "HOSPITALARIO"
            || [UBICACION_APROBACION] = "URGENCIA"
                && [DIAS_APROBACION_] <> BLANK ()
                && [DIAS_APROBACION_] <= 5,
        "HOSPITALIZACION OPORTUNA",
        IF ( [DIAS_APROBACION_] = BLANK (), "SIN FECHA DE APROBACIÓN" )
    )
)

Other options are possible. The solution will depend on what you need.

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[JuanDtM23]

@AlB Thanks a lot!

[amitchandak]

@JuanDtM23 , Try like

switch( True(),
[UBICACION_SOLICITUD] = "EXTERNAL CONSULTATION" && [DIAS_APROBACION_] <=5,  "TIMELY AUTHORIZATION",
[UBICACION_APROBACION] in {"HOSPITAL" , "URGENCIA"} && not(isblank([DIAS_APROBACION_]))&& [DIAS_APROBACION_] <=5, "TIMELY AUTHORIZATION",
isblank([DIAS_APROBACION_]), "NO APPROVAL DATE",
"0")

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[JuanDtM23]

@amitchandak It only worked for external consultation didn't work with the others.

I then share the . Pbix

https://mutualsereps-my.sharepoint.com/:u:/g/personal/jtorres_mutualser_org/EXU70n2B2dRNh94N-LUkvkoB...