Using a text string from one column as a column reference in a calculated column

[eacy]

Hi,

I have one table with a column named TBLSYSTEMCODE[TCODE] containing a text string "DCREATED" and an column named TBLSYSTEMCODE[ID] containing the id "1"

Then I have a second table named TBLPO[DCREATED] containing a date "01-01-2016"

And I have a third tabel named TBLMILESTONE[ISYSTEMCODEID] with a value "1"

I want to add a calculated column named TBLMILESTONE[INITIALTIME] which should extract the date "01-01-2016" from TBLPO[CREATED] where TBLSYSTEMCODE[ID] = TBLMILESTONE[ISYSTEMCODEID]

The ideas is that TBLMILESTONE[ISYSTEMCODEID] dynamically will change and refer to a row in TBLSYSTEMCODE[TCODE] which refer to another text string e.g. "DCLOSED" which means that the date to extract is TBLPO[DCLOSED] instead.

So the problem is how to utilize this "Configuration" text string as a colunm reference.

Thanks

[v-micsh-msft]

Hi eacy,

Could you please share us some data sample, or a screenshot of your data model?

If I understand your issue clearly, you would like to use a column value to filter columns in another table, and I don’t think this is available currently.

What I could think out here is, to alter the table 2 “TBLPO”, transform this table and make it owns a column with the same value in “TBLSYSTEMCODE[TCODE]”, then use this two to filter the date.

Regards

[eacy]

Hi Michael,

Thanks for your feedback.

If you think about it, it is quite complicated to document and I belive you got it right.

The main problem I face is to pick up a string from a field and reuse it as a column reference like:

I have:

TABLE[reference string] = "Column A"

I want to use it like:

IF(TABLE[Column A] = TRUE;bla bla bla)

I would like avoid to add any infomation to the TBLPO table as you suggest because in the future I would like to easily exchange that tabel with a table from another backend system.

[Anonymous]

I know this is an old post but I have the exact same issue and was curious if this has been resolved in another post.  I am being passed 1 table with a single column list of job roles ("Salesperson", "Warehouse", "CounterSales") and then I have a complex table with the persons name and those three roles as column names(Columns = AssociateName, Salesperson,Warehouse,CounterSales.)  Please understand this is actually around 100 roles but I simplified.  Is there a way to SUM or FILTER by passing the variable from the list TABLE[reference string] = "Salesperson" and using it as a column reference similar to example above SUM(TABLE[SalesPerson])?  Thank you in advance.

[v-micsh-msft]

Hi eacy,

I did a deeper research on this topic, still can't find any further helpful infromation on this topic.

Please take consider to submit an idea on this.

Regards