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 t...
  • jennratten's avatar
    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")