Forum Discussion

elmer1970's avatar
elmer1970
New Member
7 years ago
Solved

Passing parameter as column name

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?
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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's avatar
    v-xicai
    7 years ago

    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

     

     

4 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    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's avatar
      elmer1970
      New Member
      7 years ago

      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's avatar
        v-xicai
        Community Support
        7 years ago

        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