Passing parameter as column name

[elmer1970]

Is it possible to pass a parameter to specify a column name?

So I have the calculation below:

CALCULATE(

MAX( 'AllBoundaries'[High]),

FILTER('AllBoundaries', 'AllBoundaries'[High] <= [Test Score] )

)

but what I'd really like to do is pass the column name as a variable something like this:

VAR columnname = 'Subject'[Subject Code]

RETURN

CALCULATE(

MAX( 'AllBoundaries'[columnname]),

FILTER('AllBoundaries', 'AllBoundaries'[columnname] <= [Test Score] )

)

this doesn't work so wondered if there was another way to achieve?

[v-xicai · Accepted solution]

Hi @elmer1970 ,

To work around this issue, you can select the these columns such as Col1,Col2,and Col3 in Query Editor, right click and choose Unpivot columns. Then Put the Attribute(content is names of Col1,Col2,and Col3) into Slicer visual.

For example the steps in the picture below.

Then you can create measure like DAX below, assuming the [the fixed field] is referred to the fields which is exclude columns such as Col1,Col2,and Col3(in the picture above, it is field [Product]).

Measure1 = CALCULATE( MAX( 'AllBoundaries'[Value]), FILTER('AllBoundaries', 'AllBoundaries'[the fixed field]= MAX('AllBoundaries'[the fixed field])&&'AllBoundaries'[Attribute]=MAX( 'AllBoundaries'[Attribute])&& 'AllBoundaries'[Value] <= [Test Score] ))

Best Regards,

Amy

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

[v-xicai · Accepted solution]

Hi @elmer1970 ,

To work around this issue, you can select the these columns such as Col1,Col2,and Col3 in Query Editor, right click and choose Unpivot columns. Then Put the Attribute(content is names of Col1,Col2,and Col3) into Slicer visual.

For example the steps in the picture below.

Then you can create measure like DAX below, assuming the [the fixed field] is referred to the fields which is exclude columns such as Col1,Col2,and Col3(in the picture above, it is field [Product]).

Measure1 = CALCULATE( MAX( 'AllBoundaries'[Value]), FILTER('AllBoundaries', 'AllBoundaries'[the fixed field]= MAX('AllBoundaries'[the fixed field])&&'AllBoundaries'[Attribute]=MAX( 'AllBoundaries'[Attribute])&& 'AllBoundaries'[Value] <= [Test Score] ))

Best Regards,

Amy

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

[elmer1970]

Hi Amy

thank you so much for your reply, when I add the measure I get the error :

Column 'Test Score' cannot be found or may not be used in this expression. Would you have any idea how I could fix this?

[v-xicai]

Hi  @elmer1970 ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @elmer1970 ,

The [Test Score] is a column , right? If yes, you can add MAX function in front of it, like MAX(Table[Test Score]).

Best Regards,

Amy