Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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]))
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |