Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Use variable value to refer to a table

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

Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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.

8 Replies

  • evandrocunico's avatar
    evandrocunico
    Resolver III
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

       

       

      • evandrocunico's avatar
        evandrocunico
        Resolver III
        7 years ago

        Anonymous i don´t know, sorry. 

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

         

         

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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's avatar
      Cmcmahan
      Resident Rockstar
      7 years ago

      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's avatar
        Iamnvt
        Continued Contributor
        7 years ago
        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