M equivalent for DAX poisson.dist

Question

does anyone know if there is an M equivalent for the poisson distribution formula in DAX ? I have a query where I want to calculate the formula based on 2 columns but can't find any solution anywhere. Or should i just add the query to the data model and use DAX to calculate it there ?

Maybe this youtube video will help - link. I know this is a bad practice on SO to simply paste a link to the solution, but I won't even pretend I understand what's going on there...! — Justyna MK

Alexis Olson Alexis Olson · Accepted Answer · 2020-08-31T19:51:58

You can define it based on the definition as a custom function.

Here's one example of how to implement PoissonDist(k,mean,cdf) using List.Accumulate for the cumulative sum:

(k as number, mean as number, cdf as logical) as number =>
let
    k = Number.RoundDown(k, 0),
    Poisson = Number.Exp(-mean) * Number.Power(mean, k) / Number.Factorial(k),
    Cumulative = List.Accumulate(List.Range({0..k},0), 0, (state, current) => state + Number.Exp(-mean) * Number.Power(mean, current) / Number.Factorial(current)),
    Switch = if cdf then Cumulative else Poisson,
    Result = if k < 0 or mean < 0 then error "Input cannot be negative." else Switch
in
    Result

This version is a bit more compact but essentially the same thing:

(k as number, m as number, cdf as logical) as number =>
let
    k = if k > 0 and m > 0 then Number.RoundDown(k,0) else error "Input cannot be < 0.",
    Poisson = List.Accumulate(List.Range({0..k}, if cdf then 0 else k), 0,
        (sum, i) => sum + Number.Exp(-m) * Number.Power(m, i) / Number.Factorial(i))
in
    Poisson

M equivalent for DAX poisson.dist

1 Answers