CODEWITHSUNDEEP
excelpowerbiformuladax
Victor York
Victor York

Reputation: 1681

DAX Error: Expressions that yield variant data-type cannot be used to define calculated columns

Im back again guys, with a new issue... I dont understand this power bi, its so easy on excel haha.

Im getting the following error:

Expressions that yield variant data-type cannot be used to define calculated columns.

SLASERV1 = IF('DIARIO Backlog de incidencias'[Prioridad];"";
IF(OR('DIARIO Backlog de incidencias'[Prioridad]="Crítica";'DIARIO Backlog de incidencias'[Prioridad]="Máxima");4-('DIARIO Backlog de incidencias'[Horas totales sin Pending]*24);
IF(OR('DIARIO Backlog de incidencias'[Prioridad]="Alta";'DIARIO Backlog de incidencias'[Prioridad]="High");8-('DIARIO Backlog de incidencias'[Horas totales sin Pending]*24);
IF(OR('DIARIO Backlog de incidencias'[Prioridad]="Media";'DIARIO Backlog de incidencias'[Prioridad]="Medium");24-('DIARIO Backlog de incidencias'[Horas totales sin Pending]*24);
IF(OR('DIARIO Backlog de incidencias'[Prioridad]="Baja";'DIARIO Backlog de incidencias'[Prioridad]="Low");48-('DIARIO Backlog de incidencias'[Horas totales sin Pending]*24)
)))))

POWER BI COLUMNS

enter image description here enter image description here

How its done in excel (Column P is the result I need):

enter image description here

=IF(K3<>"";IF(E3="";"";
     IF(OR(E3="Crítica";E3="Máxima";E3="Urgent");4-(Q3*24);
        IF(OR(E3="Alta";E3="High");8-(Q3*24);
           IF(OR(E3="Media";E3="Medium");24-(Q3*24);
              IF(OR(E3="Baja";E3="Low");48-(Q3*24)
 ))))))

What am I doing wrong?

Upvotes: 1

Views: 7047

Answers (2)

Olly
Olly

Reputation: 7891

The error appears to be with the first IF statement:

SLASERV1 = IF('DIARIO Backlog de incidencias'[Prioridad];"";

You're returning text in some cases, and a number in others.

However: nesting IF statements like that is not the best way to do it - a SWITCH statement would be clearer. Something like:

SLASERV1 = 
VAR SLA_Horas = 
    SWITCH ( 
        'DIARIO Backlog de incidencias'[Prioridad],
        "Crítica", 4,
        "Máxima", 4, 
        "Alta", 8,
        "High", 8,
        "Media", 24,
        "Medium", 24,
        "Baja", 48,
        "Low", 48,
        BLANK()
    )
RETURN
    IF ( 
        ISBLANK ( SLA_Horas ),
        BLANK(),
        SLA_Horas - ( 'DIARIO Backlog de incidencias'[Horas totales sin Pending] * 24 )
    )

What would be better still would be to have a separate table to maintain Priorities / SLA Hours. Something like:

enter image description here

Create a relationship between this table and your fact table on field Prioridad, then you can create a calculated column using:

SLASERV2 = RELATED ( SLA[SLA Hours] ) - ( 'DIARIO Backlog de incidencias'[Horas totales sin Pending] * 24 )

See https://pwrbi.com/so_55396655/ for an example PBIX file.

Upvotes: 2

Pavel Klammert
Pavel Klammert

Reputation: 315

that error is quite self-explanatory, isn't it?

Powerbi column expects singe datatype, ie Number OR text. Can't do both.

If you want a single column, wrap the else part in Format function like this:

format(48-('DIARIO Backlog de incidencias'[Horas totales sin Pending]*24), "")

More about format here: https://learn.microsoft.com/en-us/dax/format-function-dax

But I would suggest you rather create two columns, one with label and second with the number

Upvotes: 0

Related Questions