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

[rezaaditia]

Hi -

Please help, i am stuck with this formula, had searched over the internet, but no luck

i want to create TAT column using Dax formula with adding new column,

TAT = 
IF (
    ISBLANK ([po_creation_date].[Date] ) || ISBLANK ( [po_release_supplier2].[Date])~
    0~
    IF (
        [po_creation_date].[Date] <= [po_release_supplier2].[Date]~
        CALCULATE (
            SUM ( 'Calendar'[IsWorkingDay2] )~
            DATESBETWEEN ( 'Calendar'[Date]~'p2p-report'[po_creation_date]~'p2p-report'[po_release_supplier2] )
        )-1~
        - CALCULATE (
            SUM ( 'Calendar'[IsWorkingDay2] )~
            DATESBETWEEN ( 'Calendar'[Date]~'p2p-report'[po_release_supplier2]~'p2p-report'[po_creation_date] )
        )-1
    )
)

instead 0 i want to put "in progress", but i got that error...

IF (
    ISBLANK ([po_creation_date].[Date] ) || ISBLANK ( [po_release_supplier2].[Date])~
    "In Progress"~

any suggestion?

Thanks

Regards,

Reza

[v-chuncz-msft · Accepted solution]

@rezaaditia 

Append the following text to the end of your formula.

& ""

[rezaaditia]

Thanks @v-chuncz-msft  your suggestion is working.

Thanks @amitchandak for your assistance, got the answer from your last feedback,

Really appreciate with your help

[rezaaditia]

Hi @v-chuncz-msft 

Thanks for your reply.

I don't know if i use correctly or not, but i tried below still shows #error

IF (
    ISBLANK ( [srm_sc_appr_dt].[Date] ) || ISBLANK ( [po_creation_date] )~
    0 & "in progress"~

Thanks

[amitchandak]

else condition is not very clear. Can you try something like

TAT = 
IF (
    ISBLANK (([po_creation_date].[Date] ) || ISBLANK ( [po_release_supplier2].[Date])),
    "InProgress",
    IF (
        [po_creation_date].[Date] <= [po_release_supplier2].[Date],
        concatenate(CALCULATE (
            SUM ( 'Calendar'[IsWorkingDay2] )~
            DATESBETWEEN ( 'Calendar'[Date]~'p2p-report'[po_creation_date]~'p2p-report'[po_release_supplier2] )
        )-1
        - CALCULATE (
            SUM ( 'Calendar'[IsWorkingDay2] )~
            DATESBETWEEN ( 'Calendar'[Date]~'p2p-report'[po_release_supplier2]~'p2p-report'[po_creation_date] )
        )-1,"")
    )
)


TAT = 
IF (
    ISBLANK (([po_creation_date].[Date] ) || ISBLANK ( [po_release_supplier2].[Date])),
    "InProgress",
    IF (
        [po_creation_date].[Date] <= [po_release_supplier2].[Date],
        concatenate(CALCULATE (
            SUM ( 'Calendar'[IsWorkingDay2] )~
            DATESBETWEEN ( 'Calendar'[Date]~'p2p-report'[po_creation_date]~'p2p-report'[po_release_supplier2] )
        )-1,""),
        concatenate( CALCULATE (
            SUM ( 'Calendar'[IsWorkingDay2] )~
            DATESBETWEEN ( 'Calendar'[Date]~'p2p-report'[po_release_supplier2]~'p2p-report'[po_creation_date] )
        )-1,"")
    )
)

[amitchandak]

Concat empty string to non zero result too. Then you will able to given in progress instead of zeo. But that might impact sorting.

[amitchandak]

Here we are comparing text format year with numeric format year

CALCULATE(sum(Sales[Sales]),OrderTime[Order Year]= CONCATENATE( Year(NOW())-2,""))

[rezaaditia]

Hi @amitchandak 

I did try but still got same error.

Thanks

[amitchandak]

Share new calculation