How to use value from a table to refer to the name of the measure for a new column

[StripeShow]

I am tyring to use the value from a table to refer to the name of the measure for a new column.

I have three measures which all return a single value (correlation coefficient), and I would like to put these values as rows in a column.  I assumed this could be with the follwoing approach, but if there is a better method, I am open to any solution.

I have created a new table with a manually entered column which has the names of the measures:  MeasureName1, MeasureName2, and MeasureName3.  These measures are assiciated with the table MasterHistory, so the fully qualified name of a measure would be MasterHistory[MeasureName1], for example.  I am trying to add a second column which returns the value of the mesure using the name from column1.  I tried the formula Column2 = MasterHistory[[Column1]], but that results in the error "The end of the input was reached".  See below...

Any suggestions would be greatly appreciated.

[ryan_mayu]

@StripeShow 

column 2="MasterHistory["&column1&"]"

[StripeShow]

@ryan_mayu 

Thanks, but that did not work.  It results in the error:  "Failed to resolve name 'column1'. It is not a valid table, variable, or function name."

If I make slight change and add brackes around column1 as in:  column 2 = "MasterHistory["&[column1]&"]"  I get the fully qualifed name of the mesure, but that is not what I want - I want the values of the measure as the result in each row.

[v-deddai1-msft · Accepted solution]

Hi @StripeShow ,

According to your description, are you trying to conbine three measure value(return single value without any row or column filter) as rows in one colum?

Would you please try to combine UNION and ROW function like below:

Table 2 =
UNION (
    ROW ( "column1", "MeasureName1", "column2", MasterHistory[MeasureName1] ),
    ROW ( "column1", "MeasureName2", "column2", MasterHistory[MeasureName2] ),
    ROW ( "column1", "MeasureName3", "column2", MasterHistory[MeasureName3] )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[StripeShow]

Hi @v-deddai1-msft ,

That did work, thank you!

However, there is one issue - I added both of the new fields to a table visual, and it seems that the values of the measures (correlation coefficient quick measure) are not being updated when I apply a filter (and the filter is filtering out data that affect the calculation of the measure).

In contrast, when I add the measures themseles to a matrix visual, and change the Values property to 'Show on rows", I get the effectively the same visual, but the values of the measures do update with filters - the only problem with this approach is that I cannot sort on the columns (presumably becuase the data are not actually in columns, and are only giving the appearance of such due the the 'Show on rows' setting).  It is this challenge that led me down the path of putting the measures into a new table as rows in the first place - so that I can sort on them.

In the end I need to be able sort AND filter, and neither appraoch seems to allow for both.  Do you have any suggestions on how this can be accomplished?

[v-deddai1-msft]

Hi @StripeShow ,

As I mentioned above, are you trying to conbine three measure value(return single value without any row or column filter) as rows in one colum?

Once you add measure to your column, it becomes a fixed computed value, independent of filter.

Best Regards,

Dedmon Dai

[StripeShow]

HI @v-deddai1-msft ,

Thanks for confirmnig.  Do you know of any other way to accomplish this that would allow for filter and sort capablity?

[v-deddai1-msft]

Hi @StripeShow ,

Add a rank column, and sort by it.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai