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

[MaleneL]

Hi all

I am working on at DAX formular to get the stage for the last day last year, but I get the above message.

the formular is 

Stadie EOY =

VAR _StadieEOY =

    CALCULATE(

        FIRSTNONBLANK(Fact_DWH[Stage_Name],1),

            FILTER(

                Fact_DWH,

                Fact_DWH[FBK_Date#Int#EndOfMonth] = 'Fact_DWH'[FBK_Date#Int#EndOfYear]

                    && Fact_DWH[Customer] = 'Fact_DWH'[Customer]))

Return

if(ISBLANK(_StadieEOY),0,_StadieEOY)
How can i work around that the stage Name is not at numer?

Customer	FBK_Date#Int#EndOfMonth	FBK_Date#Int#EndOfYear	Stage_Name	stage EOY LY
1	28-02-2023	31-12-2022	1b	??
2	28-02-2023	31-12-2022	2c	??
3	28-02-2023	31-12-2022	3d	??
4	28-02-2023	31-12-2022	2c	??
5	28-02-2023	31-12-2022	1b	??
1	31-12-2022	31-12-2021	3d	??
2	31-12-2022	31-12-2021	2c	??
3	31-12-2022	31-12-2021	1b	??
4	31-12-2022	31-12-2021	1b	??
5	31-12-2022	31-12-2021	2c	??

hope you can help

Malene

[Anonymous · Accepted solution]

Hi @MaleneL 

The type of value you want to return is Text type, but the number type returned by 0 in the if statement you defined, the two returned value types are different, so an error will be reported, you can refer to the following new expression

Stadie EOY = 
 var  _StadieEOY=CALCULATE(
        FIRSTNONBLANK(Fact_DWH[Stage_Name],1),
            FILTER(
                Fact_DWH,
                Fact_DWH[FBK_Date#Int#EndOfMonth] = EARLIER('Fact_DWH'[FBK_Date#Int#EndOfYear])
                    && Fact_DWH[Customer] = EARLIER('Fact_DWH'[Customer])))
return IF(ISBLANK(_StadieEOY),"None",_StadieEOY)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @MaleneL 

The type of value you want to return is Text type, but the number type returned by 0 in the if statement you defined, the two returned value types are different, so an error will be reported, you can refer to the following new expression

Stadie EOY = 
 var  _StadieEOY=CALCULATE(
        FIRSTNONBLANK(Fact_DWH[Stage_Name],1),
            FILTER(
                Fact_DWH,
                Fact_DWH[FBK_Date#Int#EndOfMonth] = EARLIER('Fact_DWH'[FBK_Date#Int#EndOfYear])
                    && Fact_DWH[Customer] = EARLIER('Fact_DWH'[Customer])))
return IF(ISBLANK(_StadieEOY),"None",_StadieEOY)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MaleneL · Accepted solution]

thanks it worked ;O)