use dynamic variable in "Measure"

[zindagi414]

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

[v-juanli-msft]

Hi @zindagi414 

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.