Get particular columns as new table in dax as it is

[jeongkim]

Hi,

How can I get, for example, first 3 columns in new data table as it is in dax? 

My master table is mixed up with all types of data so I wanna separate them. 

and what is the difference from Summarize dax fucntion? 

[Akash_Varuna · Accepted solution]

Hi @jeongkim You can achieve this by using either SUMMARIZE or SELECTCOLUMNS in DAX.

Using SELECTCOLUMNS:

NewTable =
SELECTCOLUMNS(
    Table,
    "LNCEL_FDD", Table[LNCEL_FDD],
    "Sdn", Table[Sdn],
    "actSuperCell", Table[actSuperCell]
)

Using SUMMARIZE:

NewTable =
SUMMARIZE(
    Table,
    Table[LNCEL_FDD],
    Table[Sdn],
    Table[actSuperCell]
)

SELECTCOLUMNS extracts specific columns as they are, while SUMMARIZE groups data and returns unique combinations of the selected columns.

[Akash_Varuna · Accepted solution]

Hi @jeongkim You can achieve this by using either SUMMARIZE or SELECTCOLUMNS in DAX.

Using SELECTCOLUMNS:

NewTable =
SELECTCOLUMNS(
    Table,
    "LNCEL_FDD", Table[LNCEL_FDD],
    "Sdn", Table[Sdn],
    "actSuperCell", Table[actSuperCell]
)

Using SUMMARIZE:

NewTable =
SUMMARIZE(
    Table,
    Table[LNCEL_FDD],
    Table[Sdn],
    Table[actSuperCell]
)

SELECTCOLUMNS extracts specific columns as they are, while SUMMARIZE groups data and returns unique combinations of the selected columns.

[jeongkim]

Thanks,

Do you think selectcolumns in dax will be lighter than duplicating table and picking up only needed columns in query?

[v-ssriganesh]

Hello @jeongkim,
Thank you for reaching out to the Microsoft Fabric Community.

I have reproduced your scenario in Power BI based on your requirement to extract specific columns ($dn, actSuperCell, City&County) from a master table using DAX and also compared it with the Power Query method.

DAX Method (SELECTCOLUMNS) or Power Query Method Both approaches successfully return the expected output with just the selected columns.

For your reference, I’m attaching the .pbix file containing:

• The original LNCEL_FDD table.
• The new table created via SELECTCOLUMNS.
• A duplicated and trimmed table using Power Query.

Thank you @Akash_Varuna for sharing valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[jeongkim]

Thanks, what I was wondering is which way is taking less data/server loads? 

Since my data is very huge so trying to stream its volumn as much as possible. 

If dax with selectedcolumns is lighter than query then I will go with it. 

If not, the other way. 

[v-ssriganesh]

Hi @jeongkim,
Thanks for the clarification.

since you're working with a large dataset and want to minimize data/server load, the better approach is to use Power Query to duplicate the table and remove unnecessary columns before loading it into the model. This ensures that only the required data is imported, reducing both memory usage and refresh time. In contrast, using SELECTCOLUMNS in DAX still loads the full original table into memory and creates an additional table, which increases the overall memory footprint. So, for performance and efficiency, Power Query is the recommended method.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a "kudos" to help others locate it easily.
Thank you.

[jeongkim]

Do you think the same if I need to keep orginal big data both for dax and query? 

The purpose separating particular columns doesn't mean I need only that columns.

e.g. if original data has columns abcdefgh 

then I need to separate only column ab while still need cdefgh columns. 

so whichever I choose, I will maintain original abcdefgh and just adding ab columns in separate in other table(to make another different data table). 

So pls advise still query is better as I used to run query with duplicating/removing columns as what you recommended but it takes big data load cuz duplicating table means still need to fetch entire data first at the beginning step anyway. 

[v-ssriganesh · Accepted solution]

Hi @jeongkim,
Thanks for the detailed explanation.

since you're planning to keep the original full dataset and create a second table with selected columns (e.g: ab), then in this case, using DAX with SELECTCOLUMNS is a more efficient option. This is because the data from the original table is already loaded into the model, and SELECTCOLUMNS simply creates a projection of the needed columns without triggering another data load. On the other hand, duplicating the table in Power Query means re-fetching and transforming the entire dataset again before load, which does add overhead during refresh. So if both tables will exist in the model anyway, and the second one is just a slim view of the original, DAX is lighter and more efficient in this case.

If this helps, please “Accept as solution” and give a “kudos” to assist other community members.
Thank you.