topic Re: How to use a SELECTEDVALUE as column name in a Measure in DAX Commands and Tips

How to use a SELECTEDVALUE as column name in a Measure

Anonymous — Thu, 30 Mar 2023 18:39:54 GMT

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 🙂

Re: How to use a SELECTEDVALUE as column name in a Measure

FreemanZ — Fri, 31 Mar 2023 01:21:48 GMT

hi Anonymous

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.

Re: How to use a SELECTEDVALUE as column name in a Measure

Anonymous — Sun, 02 Apr 2023 11:33:38 GMT

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)

Re: How to use a SELECTEDVALUE as column name in a Measure

FreemanZ — Sun, 02 Apr 2023 12:16:10 GMT

hi Anonymous

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/

Re: How to use a SELECTEDVALUE as column name in a Measure

Anonymous — Sun, 02 Apr 2023 12:52:13 GMT

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. 👍