Forum Discussion

Luck01's avatar
Luck01
New Member
6 years ago

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]

 

Months123456
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

  • 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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft 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