convert calclulated column datatype text to decimal number

[Anonymous]

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

[abhi9255]

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

[Anonymous]

Hi

Thanks to reply,

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

How to rectify it?

Thanks

kunuthuri

[abhi9255]

can you share you dax expression?

[Anonymous]

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

[abhi9255]

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

[Anonymous]

Is there any solution?

[v-jiascu-msft]

Hi @Anonymous,

What's the result you expect? 

Best Regards,

Dale

[Anonymous]

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

[v-jiascu-msft]

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

[Anonymous]

Hi

Accurate formula:

Finding the hours between startdate and enddate columns in action table.

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

                           VAR TheTime =

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

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

                            Return TheTime

Once executed the calculated column, it display

Name           Hoursbetween

field              30 mins

public           1 hrs 20 mins

according to requirement , above Name "field","public" etc... display field total hours and public total hours etc...

for this i created new measure 

measure = calculate(sum(action[Hoursbetween]) filter(site[name]="field"))

excecuted the query and drag it in to card visual it is showing

The function SUM cannot work with values of type string

thanks

[ChrisMendoza]

@Anonymous,

Seems like you've added more to your original post https://community.powerbi.com/t5/Desktop/when-we-trim-the-left-side-characters-with-data-type-text-in/td-p/437842.

The measure

Measure =
VAR MINstartdate =
    SUM ( 'Action'[StartTime] )
VAR MAXStartdate =
    SUM ( 'Action'[EndTime] )
VAR hrs =
    DATEDIFF ( MINstartdate, MAXStartdate, HOUR )
VAR mins =
    MOD ( DATEDIFF ( MINstartdate, MAXStartdate, MINUTE ), 60 )
VAR theTime =
    IF (
        hrs > 0
            && mins >= 1,
        COMBINEVALUES ( " ", hrs, "hrs", mins, "mins" ),
        IF (
            hrs > 0,
            COMBINEVALUES ( " ", hrs, "hrs" ),
            COMBINEVALUES ( " ", mins, "mins" )
        )
    )
RETURN
    theTime

seems to be working as you desire, correct?

[Anonymous]

Hi

Thanks to reply,

The Measure you send, once i drag it into any visual like (card, table etc...) it is showing error

[v-jiascu-msft]

Hi @Anonymous,

Can you share you file? A dummy one is enough.

Best Regards,

Dale

[Anonymous]

Hi

before created above measure, action table startdate and enddate columns are in date\time datatype

once we created new measure , the measure in text data type

then we drag it throwing an error

thanks

[v-jiascu-msft]

Surely, "3 hrs" is not a date. The text type won't be a problem.

Where did you drag it to?

What's wrong with my solution?

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"

Best Regards,

Dale

[Anonymous]

Hi

Above query, name column which column to conider?

[v-jiascu-msft]

I got it from your posts. Can you share a sample? Or how can I know anything else about your data?

Best Regards,

Dale

[Anonymous]

Hi

sample data file.