Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have to create a custom column in the query performing the percentile based on a set of values in another column. Do you know any formula? I know how to do it in DAX but in this case I really need an additional column in the query!
Solved! Go to Solution.
@saramark , there's no ready-to-eat percentile-wise functions available in PQ so far; but we can build some workarounds in this pattern,
//dataset is a list of numbers, pct ranges from 0 to 1
Pctl = (dataset as list, pct as number) =>
List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0)))
this user defined function can be included in a query and is easy to be called in an applied setp this way,
let
//dataset is a list of numbers, pct ranges from 0 to 1
Pctl = (dataset as list, pct as number) =>
List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0))),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBBDgQhCAT/4nkvAoK+xcz/vzETi03w1BG7Wtq9m1h7fruZHPGJWD1ZHJnryNAjcll61LvByQEE6YgCDHCZ1RlwwTAS8Jrig5UyWivQcS6qzExhaHCaVby+kG3/QCfldoKvXBeLwg2/fimLfcPnBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [val = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"val", type number}}),
//call to fx Pctl
#"75% Percentile" = Pctl(#"Changed Type"[val], .75)
in
#"75% Percentile"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@saramark , there's no ready-to-eat percentile-wise functions available in PQ so far; but we can build some workarounds in this pattern,
//dataset is a list of numbers, pct ranges from 0 to 1
Pctl = (dataset as list, pct as number) =>
List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0)))
this user defined function can be included in a query and is easy to be called in an applied setp this way,
let
//dataset is a list of numbers, pct ranges from 0 to 1
Pctl = (dataset as list, pct as number) =>
List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0))),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBBDgQhCAT/4nkvAoK+xcz/vzETi03w1BG7Wtq9m1h7fruZHPGJWD1ZHJnryNAjcll61LvByQEE6YgCDHCZ1RlwwTAS8Jrig5UyWivQcS6qzExhaHCaVby+kG3/QCfldoKvXBeLwg2/fimLfcPnBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [val = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"val", type number}}),
//call to fx Pctl
#"75% Percentile" = Pctl(#"Changed Type"[val], .75)
in
#"75% Percentile"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.