Forum Discussion

DanielHolland's avatar
DanielHolland
Frequent Visitor
4 years ago
Solved

If Statement

Hi All im after help on this, 

 

Im Reporting on SLAs and need to show the "SLA status" 

 

Please can I have a DAX forumla that will output this? 

 

Here is an example Data set, Status of the ticket. When the fault is to be fixed by, Todays date and Time, If todays date is Past the expected fix date and the ticket is still in open status it needs to show "breach" .

 

StatusExpected fix Date Todays DateSLA Status
Open30/02/2022  14:5228/03/2022 13:05Breach
Open05/04/2022 15:0928/03/2022 13:05On Track
Closed27/03/2022 14:0528/03/2022 13:05HIT

 

Thanks 

Dan

Need Help
Tutorial Requests
  • jennratten's avatar
    jennratten
    4 years ago

    This is how you can do it with Power Query.

     

    Table.AddColumn(#"Changed Type", "Breach Status", each if [Todays Date] > [#"Expected fix Date "] and [Status] = "Open" then "Breach" else "Not Breach", type text)

     

    Sample Script

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9IzVPSUTIw0jey0DcyMDJSMDSxMjUCCRkjhIytDEyBQk5FqYnJGUqxOgh9JvoGplBFplYGltj1+ecphBQlJmeDdTrn5BenpkAVmsPtBCvEotfDM0QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, #"Expected fix Date " = _t, #"Todays Date" = _t, #"SLA Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Todays Date", type datetime}, {"Expected fix Date ", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Breach Status", each if [Todays Date] > [#"Expected fix Date "] and [Status] = "Open" then "Breach" else "Not Breach", type text)
in
    #"Added Custom1"

     

    This is how you can do it with DAX:

    Breach Status DAX = if(and('Table'[Status] = "Open",'Table'[Expected fix Date ]<'Table'[Todays Date]),"Breach","Not Breach")

     

     

     

2 Replies

  • jennratten's avatar
    jennratten
    Super User
    4 years ago

    This is how you can do it with Power Query.

     

    Table.AddColumn(#"Changed Type", "Breach Status", each if [Todays Date] > [#"Expected fix Date "] and [Status] = "Open" then "Breach" else "Not Breach", type text)

     

    Sample Script

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9IzVPSUTIw0jey0DcyMDJSMDSxMjUCCRkjhIytDEyBQk5FqYnJGUqxOgh9JvoGplBFplYGltj1+ecphBQlJmeDdTrn5BenpkAVmsPtBCvEotfDM0QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, #"Expected fix Date " = _t, #"Todays Date" = _t, #"SLA Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Todays Date", type datetime}, {"Expected fix Date ", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Breach Status", each if [Todays Date] > [#"Expected fix Date "] and [Status] = "Open" then "Breach" else "Not Breach", type text)
in
    #"Added Custom1"

     

    This is how you can do it with DAX:

    Breach Status DAX = if(and('Table'[Status] = "Open",'Table'[Expected fix Date ]<'Table'[Todays Date]),"Breach","Not Breach")

     

     

     

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Whatever data you have supplied, you can use following formula

    =if([Status]="Open",if([Expected fix Date]<[Todays Date],"Breach","On Track"),"HIT")