cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Add more columns to the result of GROUPBY

I am creating a table using GROUPBY, however, I want to add more columns from the source to this result table. I don't want to group by these columns, I just want to add them to the end result. 

 

For example I have this: 

 

GROUPBY(entries, entries[accountId])

 

But I would like to add more columns (not to be grouped by) to the end result of this.. If I do something like this: 

GROUPBY(entries, entries[accountId], entries[newfield])

 

It will also be grouped by the new column. How can we do this? 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @DevM01 ,

In most cases GROUPBY() is simliar with SUMMAZIRE(). When you create table using GROUPBY(entries,entries[accountId]), the table will return a summarize table with distinct ID values.

If you want to add more columns based on this summarized table, you can save it as variable and use ADDCOLUMNS() to add new columns for it which is calulated from the source table. Here is the sample:

Test =
VAR tab =
    GROUPBY ( 'Table', 'Table'[ID] )
VAR tb =
    ADDCOLUMNS (
        tab,
        "a",
            CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER ( tab, [ID] = EARLIER ( 'Table'[ID] ) )
            )
    )
RETURN
    tb

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi @DevM01 ,

In most cases GROUPBY() is simliar with SUMMAZIRE(). When you create table using GROUPBY(entries,entries[accountId]), the table will return a summarize table with distinct ID values.

If you want to add more columns based on this summarized table, you can save it as variable and use ADDCOLUMNS() to add new columns for it which is calulated from the source table. Here is the sample:

Test =
VAR tab =
    GROUPBY ( 'Table', 'Table'[ID] )
VAR tb =
    ADDCOLUMNS (
        tab,
        "a",
            CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER ( tab, [ID] = EARLIER ( 'Table'[ID] ) )
            )
    )
RETURN
    tb

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User I
Super User I

Hi @DevM01 
You might look into SUMMARIZECOLUMNS()
https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

Can you show an example input and output table with data?  You can addcolumns by providing a name and aggregation for the newfield column.

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors