Use variable value to refer to a table

[Anonymous]

Hi,

I am wondering if what I am trying to do is possible.

So I have 3 tables, lets call them "A", "B", "C". These 3 tables have the same column names, with different values.

I also created a single column calculated table, called "Selection", Column "NAME"  with values "A","B","C".

I want to create a measure that will use data from one of the 3 tables, based on the value of "Selection" table.

Here is what I tried:

Measure1  = 
var selection = values(Selection[NAME])
var TableUsed = if(numberofselection>1,"A", selection) 

var calc = calculate( sum(TableUsed[VariableName]))
return calc

I keep getting error that table named "TableUsed" does not exist. I thought the TableUsed should return values defined by the var statement.

Is it possible to use variable as reference to a table?

Thanks

[Cmcmahan]

The easiest thing you can do is to let go of the fact that these tables have identical structure, and create a measure that calculates differently based on the value in your Selection table. It seems like a really useful design to take advantage of, but until PowerBI implements DAX calculation groups, it's just not feasible. 

Then you can set up a measure like this to switch calculation based on the value of [SelectionValue].

SumOfTable = 
SWITCH( SELECTEDVALUE(Selection[NAME], "A"), //Setting A as the default if multiple or none are selected.
    "A", SUM(A[Column]),
    "B", SUM(B[Column]),
    "C", SUM(C[Column]),
    0
)

[Cmcmahan · Accepted solution]

If you've got a large number of tables, and don't want to have to type this all out for each table, you could try adding a "Origin Table" column to each table, and then appending the tables into one big table in Power Query.

Then you can just slice on the Origin Table column and get the results you're asking for.

[Iamnvt]

Measure1  = 
var selection = values(Selection[NAME])
var TableUsed = if(numberofselection>1,"A", selection) 


var calc = calculate( sum(TableUsed[VariableName]))
return calc

TableUsed has to be established by using a Table function.

You may try this:

Measure1  = 
var selection = values(Selection[NAME])
var TableUsed = 
UNION(Filter(Values(Selection[NAME]), numberofselection <= 1)), DATATABLE (
            "NAME", STRING,
            { { "A" } }))


var calc = calculate( Count(TableUsed[NAME]))
return calc

[evandrocunico]

Hi @Anonymous 

try this:

Create a table with index and description

index  description

1         Col 1 table A Col 1 table B col 1 Table C

2         Col 2 table A col 2 Table B col 2 Table C

...

Create a slicer with description

Create a measure:  Field_sel = max(index)

Create measures (col 1, col2, col 3 ...):

m_col1 = sum('Table A'[COL1])+sum('Table b'[COL1])+sum('Table C'[COL1])

m_col2 = sum('Table A'[COL2])+sum('Table b'[COL2])+sum('Table C'[COL2])

Create a measure: Value ABC = If (field_sel is blank(); m_col1;switch(true();field_sel=1;m_col1;field_sel=2;m_col2;field_sel=3;m_col3)

use Value ABC, selected by slicer

Regards.

[Anonymous]

Is there a way to do it with just 1 measure and just changing the source tables?

[evandrocunico]

@Anonymous i don´t know, sorry. 

Work´s for me, with a table to slicer, a measure to select and a dynamic measure. 

[Anonymous]

No worries. Thanks for the idea though. 

It's just that I will have more than 5 data source tables. I am trying to avoid creating individual measures for each data source tables 🙂

[evandrocunico]

Are you tried merge or append tables?