Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with multiple numerical columns. I need to apply a set of measures such as Mean, Median, Mode, etc. of a selected column. The results are to be shown in a table. The goal is to build a data profiler.
EXAMPLE
Data: Products
Product | Name Length | Description Length | Country
A123 | 25 | 75 | UK
B321 | 15 | 88 | FR
etc..
Measure:
Avg = AVERAGE(SELECTEDVALUE(selected column))
Thanks in advance for your assistance 🙂
Solved! Go to Solution.
hi @S_A
The point is to unpivot your table first.
supposing your table looks like:
Product | Len1 | Len2 |
A1 | 1 | 10 |
A2 | 1 | 10 |
A3 | 4 | 40 |
try to unpivot it to:
then everything become intuitive with Power BI:
regarding unpivot:
the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lECYwOlWB0g3wiNbwxkm4AwkB8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Len1 = _t, Len2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Len1", Int64.Type}, {"Len2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Len"}})
in
#"Renamed Columns"
or you do it following:
https://monocroft.com/how-to-unpivot-columns-to-rows-in-powerbi/
Hi @FreemanZ,
appologies for not being very clear.
I have made a fact table holding the column names to be analysed. When a column is selected, a measure builds the column name
SelectedColumn = "'Data Products'[" & SELECTEDVALUE(selected column) & "]"
I want to be able to use the value of this measure as the column reference, for example
Avg = AVERAGE(SelectedColumn)
hi @S_A
The point is to unpivot your table first.
supposing your table looks like:
Product | Len1 | Len2 |
A1 | 1 | 10 |
A2 | 1 | 10 |
A3 | 4 | 40 |
try to unpivot it to:
then everything become intuitive with Power BI:
regarding unpivot:
the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lECYwOlWB0g3wiNbwxkm4AwkB8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Len1 = _t, Len2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Len1", Int64.Type}, {"Len2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Len"}})
in
#"Renamed Columns"
or you do it following:
https://monocroft.com/how-to-unpivot-columns-to-rows-in-powerbi/
Hi @FreemanZ ,
table in question has 2 million rows and 18 columns, was hoping there was a DAX alternative to Excels INDIRECT function.
This works and as you said the rest solves itself.
Thank you for your help. 👍
hi @S_A
SELECTEDVALUE returns a single value, while AVERAGE expects a column as its argument.
The point is it doesn't make sense to average oneself.
SELECTEDVALUE is used more often to capture a slicer selection and use the captured value to filter a table/column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |