Forum Discussion
Assign a table to a variable is that possible
- 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.
Invisibleman
Could you please share your expected result with example?
- Invisibleman3 years ago
Helper II
Hello Rohit_joshi_10,
In this there is now not directly an expected result to show. But I have a couple of tables, with each multiple columns. And I just want to know, if it is possible to have an table being assigned to a variable. So that you can use the variable in any needed formula instead to use the actual table name and that then also is including all the columns from the original table. So it can be a sum, sumx, calculateor whatever command.
This so Power BI doesn't need to filter out the not needed rows, as I assume, it will work faster. When you can address the table directly and Power BI only needs to check/sort the 10.000 rows instead of the 100.000 rows.
Regards,
Hans