Forum Discussion

rezaaditia's avatar
rezaaditia
Helper III
7 years ago
Solved

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

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

 

Need Help
Show and Tell

8 Replies

    • rezaaditia's avatar
      rezaaditia
      Helper III
      7 years ago

      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's avatar
        amitchandak
        Super User
        7 years ago

        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,"")
    )
)
    • rezaaditia's avatar
      rezaaditia
      Helper III
      7 years ago

      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

  • amitchandak's avatar
    amitchandak
    Super User
    7 years ago

    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's avatar
      amitchandak
      Super User
      7 years ago

      Here we are comparing text format year with numeric format year

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