Forum Discussion
Luck01
6 years agoNew Member
First value in the matrix
Hi,
I need the formula to extract the first values in the different rows shown in bold in this matrix, to obtain a column only with this values in bold and not the rest.
Column [Levels]
Row [Months]
Values [Prices]
| Months | 1 | 2 | 3 | 4 | 5 | 6 |
| ene-19 | 12.000 | 11.000 | 8.000 | 7.000 | 6.000 | 5.000 |
| feb-19 | 15.000 | 14.000 | 13.000 | 12.000 | 11.000 | |
| mar-19 | 16.000 | 15.000 | 14.000 | 13.000 | 12.000 | |
| abr-19 | 17.000 | 16.000 | 15.000 | 14.000 | 13.000 | |
| may-19 | 18.000 | 16.000 | 15.000 | 14.000 | ||
| jun-19 | 19.000 | 18.000 |
2 Replies
- lbendlin
Super User
So for each month you want the value that was recorded at the lowest level that has data. Write your DAX accordingly.
Something like
measure = FISRTNONBLANKVALUE([levels],sum([prices]))
- mahoneypat
Microsoft Employee
This is done more easily in the query editor. To see how it works with your example data, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs1L1TW0VNJRUoAAQyM9AwMDJL4hKh8ILDBEzDFEzDBETMEisTrRSmmpSVArkawxRbPWBI1vjMbH7kyQ8bmJRZjGo7mHTOtAxicmYTEeLQDItA7i+kok45GUWJBlBcjIrNI8NCPRjLZE0wqxKjYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Months = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Months", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Months"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Months"}, {{"AllRows", each _, type table [Months=text, Attribute=text, Value=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TopRow", each Table.FirstN([AllRows],1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}), #"Expanded TopRow" = Table.ExpandTableColumn(#"Removed Columns", "TopRow", {"Attribute", "Value"}, {"Attribute", "Value"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded TopRow",{{"Attribute", Int64.Type}, {"Value", Int64.Type}}) in #"Changed Type1"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat