Reply
Member
Posts: 132
Registered: ‎04-06-2017

convert calclulated column datatype text to decimal number

Hi

 

I created calculated column and its datatype is "text". how to convert it in to whole number or decimal number datatype. I need DAX expression for this?

 

can you please assist?

 

Thanks

Kunuthuri

Regular Visitor
Posts: 30
Registered: ‎10-09-2017

Re: convert calclulated column datatype text to decimal number

Just select the calulated column from the fields pane and in the formatting pane choose the data type you want.

 

 

2018-06-18_16h29_22.png

 

 

 

Member
Posts: 132
Registered: ‎04-06-2017

Re: convert calclulated column datatype text to decimal number

Hi

 

Thanks to reply,

 

we created calculated column and its data type and format in text, and change it to decimal , showing error

 

Capture7.PNGCapture8.PNGCapture9.PNG

 

How to rectify it?

 

Thanks

kunuthuri

Regular Visitor
Posts: 30
Registered: ‎10-09-2017

Re: convert calclulated column datatype text to decimal number

can you share you dax expression?

Member
Posts: 132
Registered: ‎04-06-2017

Re: convert calclulated column datatype text to decimal number

calculated column

 

Hours Between = VAR Hrs = DATEDIFF(Table[startdate],'Table'[enddate],HOUR)                                                                                                              VAR Mins = MOD(DATEDIFF( Table[startdate],'Table'[enddate],MINUTE),60)

                           VAR TheTime =

                            IF (Hrs >0, COMBINEVALUES (" " , Hrs, "hrs", Mins, "mins"),

                                             COMBINEVALUES(" " , Mins,"mins"))

                            Return TheTime

Regular Visitor
Posts: 30
Registered: ‎10-09-2017

Re: convert calclulated column datatype text to decimal number

You are trying to concate string along with number which cannot be converted into decimal. that is why you are getting the error

Member
Posts: 132
Registered: ‎04-06-2017

Re: convert calclulated column datatype text to decimal number

Is there any solution?

 

Community Support Team
Posts: 5,366
Registered: ‎05-02-2017

Re: convert calclulated column datatype text to decimal number

Hi @kunuthuri,

 

What's the result you expect? 

 

Best Regards,

Dale

Member
Posts: 132
Registered: ‎04-06-2017

Re: convert calclulated column datatype text to decimal number

Hi

 

Finding Hours between start and end date ?

 

Hours Between = VAR Hrs = DATEDIFF(Table[startdate],'Table'[enddate],HOUR)                                                                                                              VAR Mins = MOD(DATEDIFF( Table[startdate],'Table'[enddate],MINUTE),60)

                           VAR TheTime =

                            IF (Hrs >0, COMBINEVALUES (" " , Hrs, "hrs", Mins, "mins"),

                                             COMBINEVALUES(" " , Mins,"mins"))

                            Return TheTime

 

Result :

 

Name      Hours Between

ABC         1 hrs 15 mins

DEF          2 hrs 40 mins

XYZ          10 mins

 

Above query is executed correctly and i need to find total hours in Card visual. So, next i created new measure 

 

Total Hours measure = calculate(sum(table1[Hours Between]), filter (table2,[type]="sample"

 

Once i drag the Total Hours measure in to card visual . it is showing

 

The Function SUM cannot work with values of type string

 

How to resolve this issue. can you please assist?

 

Thanks

Kunuthuri

Highlighted
Community Support Team
Posts: 5,366
Registered: ‎05-02-2017

Re: convert calclulated column datatype text to decimal number

Hi Kunuthuri,

 

If you don't want to change the format of [Hours Between], maybe you can try the formula below.

Measure =
VAR total =
    SUMX (
        SUMMARIZE (
            'table',
            'table'[Name],
            "seconds", DATEDIFF ( [startdate], [enddate], SECOND )
        ),
        [seconds]
    )
VAR hours =
    INT ( total / 3600 )
VAR minutes =
    INT ( MOD ( total, 3600 ) / 60 )
VAR seconds =
    MOD ( MOD ( total, 3600 ), 60 )
RETURN
    hours & " hours "
        & minutes
        & " minutes "
        & seconds
        & " seconds"

If you want a more accurate formula, please provide a sample.

 

Best Regards,

Dale