Forum Discussion

PascalT's avatar
PascalT
Helper I
8 years ago

Create a dynamic calculated Column from Different Tables

Hi, I know my title is a bit blurry...

 

So here is my issue:

I've three tables:

SumTable, Table2 and Table3

Table2 and Table3 contains one record each, and one of the columns if named Number.

 

In SumTable, i've a column named TableNumber. For each record, i've either 2 or 3 as values.

I've created a new column named TableLink. This column build a string that would let me to get the Number value from the Table2 or Table3.

 

TableLink = CONCATENATE(CONCATENATE("'";CONCATENATE(CONCATENATE("Table";[TableNumber]);"'"));"[Number]")

 

So, in my SumTable, I've a column named TableLink in which I have a string value either 'Table1'[Number] or 'Table2'[Number]

 

This is working.

 

Now, in my SumTable, I want to create another column named SumNumber.

To get the value, I just want to write SumNumber = SUM([TableLink])

This would be equivalent to SumNumber = SUM('Table1'[Number])

 

My issue is that i get an error as the TableLink values are considered as String

It says "The function SUM cannot work with value of type String".

 

But if I write manually exactly the same, SumNumber = SUM('Table1'[Number]), of course it works...

 

Is there any function or DAX thing to get the expression of a String? Or any other Idea?

 

Thank you for your help

 

Pascal

Need Help

3 Replies

  • vik0810's avatar
    vik0810
    Resolver V
    8 years ago

    There is no such thing as "Dynamic DAX" like there is dynamic SQL. You dont need the column TableLink, you can achieve this with the SWITCH function

     

    SWITCH(
    Table[TableNumber],  
    2, SUM(Table2[Number]), 
    3, SUM(Table3[Number])
    .........
)
    • PascalT's avatar
      PascalT
      Helper I
      8 years ago

      Hi and thank you very much. However, i dont have only Table2 and Table3, i've many more and the 2 or 3 are random ID...

      Maybe i should add some screenshots...

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi PascalT,

         

        Could you post your table structures with some sample/mock data, so that we can better assist on this issue? It's better to share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

         

        Regards