Create a dynamic calculated Column from Different Tables

[PascalT]

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

[vik0810]

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]

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]

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. 

Regards