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.
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.
- Invisibleman3 years ago
Helper II
Hello Paul,
Thanks for the detailed information. I was thinking of this allready, but when searching the internet, I didn't saw a clear statement about this. But then I know, that I don't need to see/try this way further.
And I want to thank you for the help.
Regards,
Hans