Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
S_A
Regular Visitor

How to use a SELECTEDVALUE as column name in a Measure

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 🙂

1 ACCEPTED SOLUTION

hi @S_A 

 

The point is to unpivot your table first. 

 

supposing your table looks like:

ProductLen1Len2
A1110
A2110
A3440

 

try to unpivot it to:

FreemanZ_0-1680437410940.png

then everything become intuitive with Power BI:

FreemanZ_1-1680437610303.png

 

FreemanZ_2-1680437622566.png

 

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/

 

View solution in original post

4 REPLIES 4
S_A
Regular Visitor

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:

ProductLen1Len2
A1110
A2110
A3440

 

try to unpivot it to:

FreemanZ_0-1680437410940.png

then everything become intuitive with Power BI:

FreemanZ_1-1680437610303.png

 

FreemanZ_2-1680437622566.png

 

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

FreemanZ
Super User
Super User

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors