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 does not exceed column testing, then just return first column time. I have the following code im trying(shown below), however all it does for me is it returns first column. Any suggestions?

thanks fro your help 

 

 

 

 

 

 

 

Columntest = IF('Klaipėda - Radviliškis'[Time Klaipėda - Radviliškis] > 'Klaipėda - Radviliškis'[Testing], 'Klaipėda - Radviliškis'[Time Klaipėda - Radviliškis] - 'Klaipėda - Radviliškis'[Testing], 'Klaipėda - Radviliškis'[Time Klaipėda - Radviliškis])

 

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.

     

6 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Anonymous, are you using the Time data type? I created an example below and it works (all three columns are Time data type).

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      DataInsights  Could you share your pbix file or code with me pleease?

      Many thanks,

      Gediminas

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