Creating another tables by copying original table using Dax and Query, which way is lighter?

[jeongkim]

Hi,

I have big data table which I would call it as Original and this has, for example, columns ABCDEFG.

And I wanna create another tables fetching each columns respectively from Original table to map to other data tables. 

e.g. 

Table 1: Original (ABCDEFG) - main table and I will keep this as master

Table 2: Column A

Table 3: Column B

Table 4: Column C

Table 5: Column D

Table 6: Column E

Table 7: Column F

Table 8: Column G

In that case I was wondering which way is lighter running PBI between option 1: creating another tables by 'Summarize' columns in Dax and option 2: duplicating original table and remove other unneeded columns in Query.

Both are creating needed tables as same but its pbix sizes are a bit different as below. 

DAX: Original + new tables by each column ABCDEFG in DAX using Summarize function
QUERY: Original + new tables by each column ABCDEFG in QUERY duplicating original table and removing unneeded columns
Original: Original 

It seems creating new tables by Query seems to have smaller size than Dax creation but when I need to change some table structure, Query takes too long loading time so still Dax creation seems better for me but not sure in the long run.  

Pls advise me your opinions. 

[Jihwan_Kim · Accepted solution]

Hi, in my case,

option 1. I usually ask source team to create those, and then I simply get those from the source.

option 2. If I cannot ask source team to create those, my next option is to create in Power Query Editor (or, Dataflow). -> If the speed of the query is too slow in Power Query editor, one of the reasons is because of queryfolding-not-properly-done.

option 3. I usually do not create any tables by DAX unless it is necessary. I do have a few cases creating tables by DAX, but if the case is something like in the question, I suggest option1 or option2.

[danextian · Accepted solution]

Hi @jeongkim 

The question is how significant is the change in refresh time  using the Power Query option. If you are ok with the increase in refresh time then I'll go with it. If the increase is very subtantial, I'll go for the DAX option.  But ideally, these transformations should be done upstream with minimal or no extra ones in the query editor. 

[danextian · Accepted solution]

Hi @jeongkim 

The question is how significant is the change in refresh time  using the Power Query option. If you are ok with the increase in refresh time then I'll go with it. If the increase is very subtantial, I'll go for the DAX option.  But ideally, these transformations should be done upstream with minimal or no extra ones in the query editor. 

[Jihwan_Kim · Accepted solution]

Hi, in my case,

option 1. I usually ask source team to create those, and then I simply get those from the source.

option 2. If I cannot ask source team to create those, my next option is to create in Power Query Editor (or, Dataflow). -> If the speed of the query is too slow in Power Query editor, one of the reasons is because of queryfolding-not-properly-done.

option 3. I usually do not create any tables by DAX unless it is necessary. I do have a few cases creating tables by DAX, but if the case is something like in the question, I suggest option1 or option2.

[jeongkim]

Hi,

Any reason you don't recommend adding tables in dax which seems loading quicker. 

[v-nmadadi-msft · Accepted solution]

Hi  @jeongkim 
Thanks for reaching out to the Microsoft fabric community forum.

DAX tables are calculated in memory at model load, which can inflate memory usage and impact performance during refreshes especially with large datasets. Power Query, on the other hand, can leverage query folding, pushing transformations back to the data source for more efficient processing.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you