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.
I'd like to review the improvement in test scores for each students based on the test stage, how do I create the column Improvement below where the Improvement = score at initial assessment - score at completed programme (and where they have't completed programme then 0) ?
I've seen some examples using the add custom column but they don't make sense and the syntax isn't accepted when I try to add to the Custom column formula box.
Any help appreciated.
Thx
Hi ImkeF, thanks for your suggestion. The fabric community emails went straight to my junk folder so I didn't see your reply. I deciced to make the calculation within the SQL query and then imported it in the main table. Yes there is always a max. 2 per studen and your solution seems interesting. I might give it a go as an alternative just out of interest. Thanks again.
Hi @samwar14 ,
if there is alway max. 2 values per student, then you could:
1) pivot on [Stage] with no aggregation on value column [Score]
2) Fill down in column [Score]
This will return the starting values in all rows with ending values.
3) Add custom column where you subtract the starting value from the ending value
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjIPbMyyxRitWBCBoBsQkQV4BFQNKmQGyArMwIqg9D0ASqGqg3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Score = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Score", Int64.Type}, {"Column5", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column5]), "Column5", "Score"),
#"Filled Down" = Table.FillDown(#"Pivoted Column",{"Init"}),
#"Inserted Subtraction" = Table.AddColumn(#"Filled Down", "Subtraction", each [x] - [Init], type number)
in
#"Inserted Subtraction"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.