IF column DAX

[Anonymous]

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])

[v-alq-msft]

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]

@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 · Accepted solution]

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.

[Anonymous]

@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? 

[DataInsights]

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

[Anonymous]

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

Many thanks,

Gediminas