undefined

[Anonymous]

Hi there,

I have an error occuring when visualizing following quickmeasure in a table + the error is "input[x]: the value "undef." of type text is not convertable to type number. Here is the formula:

IF(ABS(

IFERROR(

CALCULATE(SUM('input'[X1]) / SUM('input'[Y1]) - IFERROR(SUM('input'[X2]) / SUM('input'[Y2]),0), FILTER(input,'input'[Date]=MAX('Date 1'[Date 1])))

-

CALCULATE(SUM('input'[X1]) / SUM('input'[Y1]) - IFERROR(SUM('input'[X2]) / SUM('input'[Y2]),0), FILTER(input,'input'[Date]=MAX('Date 2'[Date 2]))),

"undef."))>100,"undef.",

IFERROR(

CALCULATE(SUM('input'[X1]) / SUM('input'[Y1]) - IFERROR(SUM('input'[X2]) / SUM('input'[Y2]),0), FILTER(input,'input'[Date]=MAX('Date 1'[Date 1])))

-

CALCULATE(SUM('input'[X1]) / SUM('input'[Y1]) - IFERROR(SUM('input'[X2]) / SUM('input'[Y2]),0), FILTER(input,'input'[Date]=MAX('Date 2'[Date 2]))),

"undef."))

x1, x2, y1, and y2 are all figures [-500; 500]

Thanks in advance!

[amitchandak]

@Anonymous for divide use divide function and then try , not need to check is error after that

example

divide(SUM('input'[X2]) ,SUM('input'[Y2]) )

[AlexisOlson]

You can also use variables to reduce redundancy. For example:

VAR _1 =
    CALCULATE (
        DIVIDE ( SUM ( 'input'[X1] ), SUM ( 'input'[Y1] ) ) -
        DIVIDE ( SUM ( 'input'[X2] ), SUM ( 'input'[Y2] ) ),
        FILTER ( input, 'input'[Date] = MAX ( 'Date 1'[Date 1] ) )
    )
VAR _2 =
    CALCULATE (
        DIVIDE ( SUM ( 'input'[X1] ), SUM ( 'input'[Y1] ) ) -
        DIVIDE ( SUM ( 'input'[X2] ), SUM ( 'input'[Y2] ) ),
        FILTER ( input, 'input'[Date] = MAX ( 'Date 2'[Date 2] ) )
    )
RETURN
    IF ( ABS ( _1 - _2 ) > 100, "undef.", _1 - _2 )