Forum Discussion
khesan99
3 years agoRegular Visitor
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...
- 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".
lbendlin
Super User
3 years agoDAX 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".