Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Creating a dynamic table only with years to use with Parameter

Hi

 

Im working with PBI Desktop connected to SSAS Server.

I want to create a dynamic table with just the year of a specific date column columnC from factualA, to use it as a source in a report parameter.

 

I can do this, right?

Conceptually this should be done at the SSAS Project level, not PBI, correct?

 

I'm seeing ADDVALUES, SUMMARIZE, SELECTVALUES, CALCULATETABLE... what is the most efficient way 

to simply create a table in SSAS, with the distinct year values of same date column?

 

I was trying to do = ADDCOLUMNS (VALUES(YEAR(FactualF[columnC])) but it isn't working.

 

Regards

 

Need Help

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi,@Ricardo

        When you use live connection to connect SSAS Server, there is no sense in which a new model is being created; in other words, it's not possible to define new calculated columns, hierarchies, relationships, and so on.

    You can only create report level measure.

    So you can create a table in SSAS like below:

    = VALUES(FactualF[columnC].[Year])

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi
      this didn't work. 
      = VALUES(FactualF[columnC].[Year])

       

      I had to create in FactualF a year calculated column, and then

      =VALUES(FactualF[Year])

      but i think this is not efficient and a lost of space.

       

      Can i combine VALUES with YEAR function?

       

      Regards

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi,@Ricardo

             Create in FactualF a year calculated column, and then use =VALUES(FactualF[Year]) is the efficient way.

        Combine VALUES with YEAR function is also run YEAR function and the run Values function. So it is the best way for you. 

         

        Best Regards,

        Lin