Cannot format DAX measure to the expected output

[-Ozym4nd1az-]

I have the following measure and I need the output in the format of 'DATETIME', even though I tried many changes (I even tried to use a column instead of a measure, so that I could easier change the format, but the calculation doesn't work as expected when in a column) it didn't work. 

I need this calculation in 'DATETIME' and I don't even understand which kind of format this output is (maybe duration?). 

Any hints?

Main output to be yieldMeasure used by the main measure

[amitchandak]

@-Ozym4nd1az- , Can you share sample data and sample output in table format? and the formula is a text format.

refer: https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

[-Ozym4nd1az-]

@amitchandak 

Max. response time = if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "4 - Low"), FORMAT([Next business day measure], "m-d-YYYY HH:MM:SS")+TIME(2,0,0), if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "3 - Medium"), FORMAT([Next business day measure], "m-d-YYYY HH:MM:SS")+TIME(1,0,0), if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "2 - High"), FORMAT([Next business day measure], "m-d-YYYY HH:MM:SS")+TIME(0,30,0), if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "1 - Very High"), FORMAT([Next business day measure], "m-d-YYYY HH:MM:SS")+TIME(0,15,0), 0))))

Next business day measure = if([Non business hours] == 2 && (CALCULATE(COUNT('Incident Data'[Is holiday ?]), 'Incident Data'[Is holiday ?] <> 7) && CALCULATE(COUNT('Incident Data'[Is holiday ?]), 'Incident Data'[Is holiday ?] <> 1)) && (CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "3 - Medium") || CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "4 - Low")), CONCATENATEX('Incident Data', FORMAT('Incident Data'[Openday.1] + " 07:30:00", "m/d/yyyy hh:mm:ss")),

if(([Non business hours] == 2 || [Non business hours] == 1 || [Non business hours] == 0 ) && CALCULATE(COUNT('Incident Data'[Is holiday ?]), 'Incident Data'[Is holiday ?] == 7) && (CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "3 - Medium") || CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "4 - Low")), CONCATENATEX('Incident Data', FORMAT(('Incident Data'[Openday.1]+2) + " 07:30:00", "m/d/yyyy hh:mm:ss")),

if(([Non business hours] == 2 || [Non business hours] == 1 || [Non business hours] == 0 ) && CALCULATE(COUNT('Incident Data'[Is holiday ?]), 'Incident Data'[Is holiday ?] == 1) && (CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "3 - Medium") || CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "4 - Low")), CONCATENATEX('Incident Data', FORMAT(('Incident Data'[Openday.1]+1) + " 07:30:00", "m/d/yyyy hh:mm:ss")),

if(([Non business hours] == 1) && CALCULATE(COUNT('Incident Data'[Is holiday ?]), 'Incident Data'[Is holiday ?] <> 1) && CALCULATE(COUNT('Incident Data'[Is holiday ?]), 'Incident Data'[Is holiday ?] <> 7) && (CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "3 - Medium") || CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "4 - Low")), CONCATENATEX('Incident Data', FORMAT(('Incident Data'[Openday.1]+1) + " 07:30:00", "m/d/yyyy hh:mm:ss")),CONCATENATE(FORMAT(SUMX('Incident Data', 'Incident Data'[Openday.1]),"m/d/yyyy"), FORMAT(SUMX('Incident Data', 'Incident Data'[Open time]), " hh:mm:ss"))))))

Openday.1	Is holiday?	Non business hours	Open time	Next business day measure
Thursday, January 2, 2020	5	0	11:50:49	1/2/2020 11:50:49
Friday, Junuary 3, 2020	6	0	08:23:29	1/3/2020 08:23:29
Wednesday,January 8, 2020	4	2	07:16:40	1/8/2020 07:30:00

[v-deddai1-msft · Accepted solution]

Hi @-Ozym4nd1az- ,

Would you please try to remove  format function in your measure, and change the data format for the measure:

Max. response time =
IF (
    CALCULATE (
        COUNT ( 'Incident Data'[Priority] ),
        'Incident Data'[Priority] == "4 - Low"
    ),
    [Next business day measure] + TIME ( 2, 0, 0 ),
    IF (
        CALCULATE (
            COUNT ( 'Incident Data'[Priority] ),
            'Incident Data'[Priority] == "3 - Medium"
        ),
        [Next business day measure] + TIME ( 1, 0, 0 ),
        IF (
            CALCULATE (
                COUNT ( 'Incident Data'[Priority] ),
                'Incident Data'[Priority] == "2 - High"
            ),
            [Next business day measure] + TIME ( 0, 30, 0 ),
            IF (
                CALCULATE (
                    COUNT ( 'Incident Data'[Priority] ),
                    'Incident Data'[Priority] == "1 - Very High"
                ),
                [Next business day measure] + TIME ( 0, 15, 0 ),
                0
            )
        )
    )
)

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

Best Regards,

Dedmon Dai

[Greg_Deckler]

@-Ozym4nd1az- - Maybe try the technique in Chelsea Eiden's Duration. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

Can you just change the data type to Date Time?

[-Ozym4nd1az-]

I have used this technique for other kind of stuff. The problem here is that I just don't have hours, but date as well is it counting as duration as well? Unfortunately, I cannot change the data type to Date Time, because it is not a calculated column. I already tried to transfer the measure to a column but it gives me some error that are difficult to overcome.