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 

    Many thanks for your help, one more thing: I created a custom column to return the actual time value if the time exceeds the norm and if it did not, then just return the actual time value, like shown below. If the Time column exceeds 4:30, then it shows difference, if not, it returns the actual time value. 

    However, i need to display it into the histogram and show the actual value below 4:30 or 4:30 in the green color and then if it exceeds it would add on the column and show it in red, something like that below. Any ideas what measure should be created? 

     

     

     

     

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