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

Re: convert calclulated column datatype text to decimal number

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

 

 

 

Established Member
Posts: 180
Registered: ‎03-22-2018

Re: convert calclulated column datatype text to decimal number

@kunuthuri,

 

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-i....

 

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?

 

1.PNG

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

Re: convert calclulated column datatype text to decimal number

Hi

 

Thanks to reply,

 

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

Community Support Team
Posts: 7,323
Registered: ‎05-02-2017

Re: convert calclulated column datatype text to decimal number

Hi @kunuthuri,

 

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

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 143
Registered: ‎04-06-2017

Re: convert calclulated column datatype text to decimal number

Hi

 

Capture13.PNG

 

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

 

Capture15.PNG

 

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

 

 

Capture14.PNG

 

then we drag it throwing an error

 

thanks

 

 

Community Support Team
Posts: 7,323
Registered: ‎05-02-2017

Re: convert calclulated column datatype text to decimal number

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 143
Registered: ‎04-06-2017

Re: convert calclulated column datatype text to decimal number

Hi

 

Capture16.PNG

 

Above query, name column which column to conider?

Highlighted
Community Support Team
Posts: 7,323
Registered: ‎05-02-2017

Re: convert calclulated column datatype text to decimal number

[ Edited ]

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

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 143
Registered: ‎04-06-2017

Re: convert calclulated column datatype text to decimal number

Hi

 

sample data file.

 

Capture17.PNG