Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

use dynamic variable in "Measure"

 

Hi . I'm looking to get some help on using the "Measure" tool in PowerBI. 

After opening a file and splitting the column, the variable split as "a.1" , "a.2", "a.3" .. etc.

In "Measure", I want this variable "a.<num>" to be dynamic such that I can select "num" without having to manually modifying the code. Any ideas?

 

Thanks

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    It is impossible to make the columns used in measures to be changed dynamically.

    Here is a workround:

    In Edit queries, unpivot columns, add a custom column

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIBYhBtDMRmQGyoFKsTDRY1g8qaQWWNwDKmUB5MxgLMj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [a.1 = _t, a.2 = _t, a.3 = _t, a.4 = _t, a.5 = _t, index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a.1", Int64.Type}, {"a.2", Int64.Type}, {"a.3", Int64.Type}, {"a.4", Int64.Type}, {"a.5", Int64.Type}, {"index", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "column_index", each Text.End([Attribute],Text.Length([Attribute])-2))
in
    #"Added Custom"

    Close&&apply, 

    Create a measure

    Measure = CALCULATE(SUM('Table'[Value]),ALLSELECTED('Table'))

     

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.