Forum Discussion

Invisibleman's avatar
Invisibleman
Icon for Helper II rankHelper II
3 years ago
Solved

Assign a table to a variable is that possible

Dear all,   For my Power BI, I have for each year a database/table and all those years I also have combined to 1 with the append function. Now as I would think, the smaller the database/table, the ...
  • PaulOlding's avatar
    3 years ago

    TL;DR - No, it's not possible to dynamically reference table and column names like this.

     

    You sometimes see people using a SWITCH or IF statement to control which table is used in a calculation, giving something like:

    SWITCH(
        SELECTEDVALUE(Date[Year]),
        "2021", CALCULATE(SUM(TableName_2021[Amt]),  'mapCMEU GL Accounts'[Account] IN { "41110000" }),
        "2022", CALCULATE(SUM(TableName_2022[Amt]),  'mapCMEU GL Accounts'[Account] IN { "41110000" }),
        "2023", CALCULATE(SUM(TableName_2023[Amt]),  'mapCMEU GL Accounts'[Account] IN { "41110000" }),
       etc...

    However, this would be a bad idea.

    Of course, it stands to reason that "a sum for 10.000 rows would be faster than the sum for 100.000".  At this volume of data though the difference is miniscule - too small to be perceptible for a human. 

    The next factor to consider is to do with the internals of Vertipaq and it's two engines, Formula engine and Storage engine.  If you're interested the SQLBI guys have good content on it.  The short version is doing conditionals like SWITCH and IF requires extra work for the formula engine, which will add to the time the query takes.  Also, the Vertipaq engine in general is architected and optimized to do filter-table-and-sum-column type calculations so it's unlikely you'll ever need to do mangle your code like above to get good performance.

     

    So, your strategy of appending all the tables into 1 is the way to go.  All the DAX will be simpler, it'll be easier to maintain, and I wouldn't expect you to get better performance by splitting the table up anyway.