DAX column name from row cell value

Question

Is it real:

I want to add calculated column that calculate count in "Table1" column by "Table2" row value in column "Columns"

CalculatedColumn formula in powerpivot

=COUNTX('Table1';'Table1'['Table2'[Columns]])

but this formula not working

Answer 1

this actually does not directly answer your question, some more details about your Table1 is needed, but maybe you can use this solution

If you change your layout (if this is possible) to this

and add a relationship to your Table2

there is no need for any calculation, in the screen below, columns comes from Table2 and Value from Table1_adjusted

Hope this helps, if not, you should explain what one is seeing in Table1 and Table2

Answer 2

EDIT: I missed the PowerPivot / DAX requirement at the time of answering. Now that I have already posted an answer, I will leave it for a while.

This answer is based on:

1. Concatenating string to build the reference.

2. Using INDIRECT.

You may need a quite similar operation.

I cannot test other options, as I cannot install PowerPivot for the time being.

---

I am assuming you have defined Table1 in the first range. Then use

=COUNT(INDIRECT("Table1["&A12&"]"))

to count the number of ones (or any other contents, i.e., non-empty cells) for Column1 (in B12). Similarly for Column2. You can simply copy B12, and paste into B13.

You might need to use another function of the family of COUNT, if you add complexity to your task.

---

If you did not define an Excel table in the first range, use named ranges:

1. Select data cells for Column1.
2. Use Formulas -> Define Name, and enter Column1 for the name.
3. Use formula =COUNT(INDIRECT(A12)) for Column1 in B12.
4. Copy-paste.