Friday 22 December 2023

What is the common table function for grouping data in Power BI ? Power BI interview questions and answers 087

 What is the common table function for grouping data in Power BI ?

The common table function (CTE) for grouping data in Power BI is GROUPBY. It's a DAX function that allows you to create custom tables or virtual tables by grouping and aggregating data based on specified criteria.

Here's how it works:


Code snippet



  • <table>: The table you want to group data from.

  • <groupBy_columnName1>, <groupBy_columnName2>, ...: The columns on which you want to group the data.

  • <name>: (Optional) The name you want to assign to the resulting table. If omitted, a temporary table is created.

  • <expression>: (Optional) An expression to calculate for each group, such as aggregations or custom calculations.


Code snippet

    "Total Sales", SUM(Sales)

This creates a new table that groups products by their category and calculates the total sales for each category.

Key features of GROUPBY:

  • Flexibility: Group by multiple columns and perform various aggregations within a single expression.

  • Dynamic: Results can change based on filters and slicers, making it suitable for interactive reports.

  • Virtual table: Doesn't store data physically, optimizing memory usage.

Common use cases:

  • Creating summary tables: Consolidate data for concise overviews.

  • Preparing data for visualizations: Group and aggregate data before visualizing it in charts or graphs.

  • Performing custom calculations: Combine grouping with other DAX functions for advanced analysis.


  • SUMMARIZE: Similar to GROUPBY, but with a simpler syntax and limited to one aggregation per call.

  • GROUPBY in Power Query: Group data during the data preparation stage before loading it into the model.

Choosing the right function depends on your specific requirements and data manipulation goals.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.