Forum Discussion

pbi_apprentice's avatar
pbi_apprentice
Regular Visitor
5 years ago
Solved

Storing column name in a variable

Hi,

 

Is it possible to store columns names/references (or even DAX code in general), in Variables , and if yes, how to do it ?

For example, let's say i have the following case :

 

MyMeasure = CALCULATE( SOMESTUFF(col1), FilterContext)

 

And i need to create multiple measures that are very close to this one.

I would like to find a way to do 

 

MyMeasure = VAR Target1 = "col1" RETURN CALCULATE( SOMESTUFF( Target1 ) , FilterContext)

 

And if it's possible, i would also appreciate a way of storing FilterContext in a Variable.

Need Help
  • pbi_apprentice's avatar
    pbi_apprentice
    5 years ago

    Thanks to Fowmy  and amitchandak   I managed to put together a solution  to store Tables, Columns, Filter Context in Variables, so that I may reference them all at the beginning of my functions / measures.

     

    Here I used AVERAGEX as my 'SOMESTUFF()'

     

    Example case of dealing with a Table : 

     

    Mesure = 
   VAR FilterContext = FILTER(_tab,_tab[category] = "a") 
   VAR TARGET = _tab 
   RETURN CALCULATE( AVERAGEX(TARGET,[numbers]),FilterContext)

     

     

    Example of dealing with columns :

     

     

    Mesure = 
   VAR FilterContext = FILTER(_tab,_tab[category] = "a") 
   VAR TARGET = ADDCOLUMNS(_tab,"col1",_tab[numbers]) 
   RETURN CALCULATE( AVERAGEX(TARGET,[col1]),FilterContext)

     

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    pbi_apprentice , I doubt the column way you said , but filter you can store but not a string

     

    MyMeasure =

    VAR Target1 = Table[Col1]

    Var FilterContext= filter(Table, Table[Col2] = "Abc")

     

    RETURN CALCULATE( SOMESTUFF( Target1 ) , FilterContext)

    • pbi_apprentice's avatar
      pbi_apprentice
      Regular Visitor
      5 years ago

      So I tried your solution, but I was only able to store the filter context :

       

      But for some reason, I can't store the table, as it doesn't recognise the column :

       

      Here is the data i am using : 

       

      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        5 years ago

        Hi pbi_apprentice,

         

        What is the requirement to store the Column you want to perform an aggregation over as a variable? If you are having to create multiple measures you will be defining the variable in each measure anyway.

        Are you looking for a reuseable single measure that provides a value based on the context of the query being executed for your visual?

         

        Thanks,

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    pbi_apprentice 

    You can store tables but you need to use a table function. Try this way

    Var TARGET = VALUES( _Tab[Numbers])

     

  • pbi_apprentice's avatar
    pbi_apprentice
    Regular Visitor
    5 years ago

    Thanks to Fowmy  and amitchandak   I managed to put together a solution  to store Tables, Columns, Filter Context in Variables, so that I may reference them all at the beginning of my functions / measures.

     

    Here I used AVERAGEX as my 'SOMESTUFF()'

     

    Example case of dealing with a Table : 

     

    Mesure = 
   VAR FilterContext = FILTER(_tab,_tab[category] = "a") 
   VAR TARGET = _tab 
   RETURN CALCULATE( AVERAGEX(TARGET,[numbers]),FilterContext)

     

     

    Example of dealing with columns :

     

     

    Mesure = 
   VAR FilterContext = FILTER(_tab,_tab[category] = "a") 
   VAR TARGET = ADDCOLUMNS(_tab,"col1",_tab[numbers]) 
   RETURN CALCULATE( AVERAGEX(TARGET,[col1]),FilterContext)