Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
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
Proud to be a Super User!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |