Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

IF column DAX

Hey guys, I have this issue where I need to create conditional column and basically, if Time(first column) exceeds column Testing, it should give the difference between time and testing, while if it...
Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    I modified new data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    The data type of 'Time' is text. You may create a calclulated column and format it as (hh:nn:ss) as below.

    Result = 
var _h = 
VALUE(LEFT([Time],2))
var _hour = 
INT(
    DIVIDE(
        VALUE(MID([Time],4,2)),
        60
    )
)
var _min = 
MOD(
    VALUE(MID([Time],4,2)),
    60
)
return
IF(
    _h+_hour<10,
    IF(
        _min>=10,
        TIMEVALUE("0"&(_h+_hour)&":"&_min&":"&"00"),
        TIMEVALUE("0"&(_h+_hour)&":0"&_min&":"&"00")
    ),
    IF(
        _min>=10,
        TIMEVALUE((_h+_hour)&":"&_min&":"&"00"),
        TIMEVALUE((_h+_hour)&":0"&_min&":"&"00")
    )
)

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

 

You may create a calculated column as below.

Result = 
IF(
    [Time]>[Test],
    var _text = ([Time]-[Test])&""
    return
    IF(
        LEFT(_text,2)="12",
        SUBSTITUTE(_text,"12","00",1),
        _text   
    ),
    [Time]&""
)

 

Result:

 

Best Reagrds

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

v-alq-msft  Your solution does work for me, however do you know how to edit this code so I could get the time rounded, where minutes would only up to 60 and then go to hours? 
I am using Chelsie Eidens Duration to aggregate time by keeping it as whole number. Thats what I get after using your code: Everything seems good, but some of them show 84 minutes.

 


https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    I modified new data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    The data type of 'Time' is text. You may create a calclulated column and format it as (hh:nn:ss) as below.

    Result = 
var _h = 
VALUE(LEFT([Time],2))
var _hour = 
INT(
    DIVIDE(
        VALUE(MID([Time],4,2)),
        60
    )
)
var _min = 
MOD(
    VALUE(MID([Time],4,2)),
    60
)
return
IF(
    _h+_hour<10,
    IF(
        _min>=10,
        TIMEVALUE("0"&(_h+_hour)&":"&_min&":"&"00"),
        TIMEVALUE("0"&(_h+_hour)&":0"&_min&":"&"00")
    ),
    IF(
        _min>=10,
        TIMEVALUE((_h+_hour)&":"&_min&":"&"00"),
        TIMEVALUE((_h+_hour)&":0"&_min&":"&"00")
    )
)

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.