Forum Discussion

khesan99's avatar
khesan99
Regular Visitor
3 years ago
Solved

DAX Calculation of Survival Rates

Hi I am relatively new to DAX and have a problem that is causing me trouble.  This is not the actual scenario for confidentiality reasons. I need to calculate the [Survival_Rate]. We are a nurse...
  • lbendlin's avatar
    3 years ago

    DAX is not necessarily the right tool for that, although you can use PRODUCTX to some extent.  Better to do this in Power Query,

     

    let
      Source = Table.FromRows(
        Json.Document(
          Binary.Decompress(
            Binary.FromText(
              "LY3JEcAwCAN74e2HOewktTDuv41EO/lo0CJBt00bFnegdkabf2N6CSwAu0e5jU8FptQDUAAFPAGLhnoXfnOs/h/nBQ==",
              BinaryEncoding.Base64
            ),
            Compression.Deflate
          )
        ),
        let
          _t = ((type nullable text) meta [Serialized.Text = true])
        in
          type table [RowIndex = _t, #"Number of Plants Planted" = _t, #"Number of Plants Died" = _t]
      ),
      #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
          {"RowIndex", Int64.Type},
          {"Number of Plants Planted", Int64.Type},
          {"Number of Plants Died", Int64.Type}
        }
      ),
      #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Plant Death Rate",
        each [Number of Plants Died] / [Number of Plants Planted],
        type number
      ),
      #"Added Custom1" = Table.AddColumn(
        #"Added Custom",
        "Survival Rate",
        each List.Accumulate(
          {0 .. [RowIndex]},
          100,
          (state, current) =>
            if current = 0 then state else state * (1 - #"Added Custom"[Plant Death Rate]{current - 1})
        ),
        type number
      )
    in
      #"Added Custom1"

     

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".