Switch from physical table to virtual table preventing loss of relations, calc. columns and measures

Hello,
I cannot attach the PIBX file due to business-related policy. However, it’s a generic question/problem, and I’d like to understand if what I’d like to achieve is possible or not.
I have a table with hundreds of calculated columns and measures + relations with dozen of other tables.
The table source is a combined table built in PowerQuery (append of multiple tables).
Due to performance and memory issues, I’d like to move the tables append from PowerQuery to DAX, switching from a “physical” table to a virtual table (union).
But I don’t know if this is possible. I’m trying to figure out the way without luck.
Can you help me?

@Ferraglia ,

I actually did an entire video dedicated to this question that I think you will find helpful.

Bottom line - virtual tables exist only within measures, so will not help you here. Calculated tables are the other option, but unlikely to improve either memory or performance issues, and may in fact make them worse…

Here’s a summary table I put together for the video that you may also find helpful.

  • Brian
1 Like

Hi @BrianJ.
I do understand what I want to do may decrease performances and/or increase memory issues… And this is definitevely wird, but my question is much more related to the way how to switch from “phisical” to calculated table. Without loosing relations and all calculated columns/measures. As matter of fact it would take long long time to re-create columns and re-assign relations to the calculated table.

@Ferraglia When you say Virutal Table, do you mean using UNION is DAX measures or a Calculated Table?

If you append tables in DAX for a Calculated Table you might be able to reduce the time spent in Power Query, but you will increase the memory consumption by importing separate tables and then creating a UNION of all of them.

If you append tables inside a measure then you will have to wait forever for the measure to return a value.

Example:

FullSales Table is the one combined in PQ and then imported.

2007, 2008, 2009 are parts of FullSales imported separately and then combined using UNION for the table FullSales_DAX

Memory Footprint:

In this case PQ and DAX table has similar memory consumption, however, 2007, 2008, 2009 are also consuming the memory and you can’t remove them else FullSales_DAX will break.

If I create 3 measures:

The one that use PQ table runs in 10ms:

The one that use DAX Table runs in similar duration

The one that uses all 3 tables virtually runs in 5.3 seconds as it makes Formula Engine (Single Threaded slower engine to work a lot in merging 3 data cache of ~4 Million rows with a size of ~30MB):

So make these test before you choose one over another.

4 Likes

Hi @Ferraglia, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

Hi @Ferraglia, we’ve noticed that no response has been received from you since August 5. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @Ferraglia, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!