topic Re: DAX GROUPBY and keep all columns in DAX Commands and Tips

DAX GROUPBY and keep all columns

Przem — Thu, 30 Nov 2023 12:16:44 GMT

Greetings,
Using someones PowerBI dataset I want to group ORA IDs and keep the latest PROJECT ID for each ORA ID.
Data:

ORA ID	PROJECT ID	END_DATE
1	11	01.01.2023
1	12	02.01.2023
2	13	01.01.2023
2	14	02.01.2023
2	15	03.01.2023

Needed result:

ORA ID	PROJECT ID	END_DATE
1	12	02.01.2023
2	15	03.01.2023

I need a table so I used:

ResultTable = GROUPBY('Data', 'Data'[ORA ID], "Latest Time", MAXX(CURRENTGROUP(),'DATA'[END_DATE]))

My result:

ORA ID	END_DATE
1	02.01.2023
2	03.01.2023

What should I do to keep all columns, or at least add PROJECT ID column?
Unfortunately I can't use GROUPBY in PowerQuery.

Re: DAX GROUPBY and keep all columns

123abc — Thu, 30 Nov 2023 13:46:21 GMT

If you want to keep all the columns in your result table, including the PROJECT ID, you can achieve this by using the SUMMARIZE function in DAX. Here's an example of how you can modify your formula:

ResultTable =
SUMMARIZE(
'Data',
'Data'[ORA ID],
"LatestTime", MAXX(CURRENTGROUP(), 'Data'[END_DATE]),
"LatestProjectID",
CALCULATE(
VALUES('Data'[PROJECT ID]),
'Data'[END_DATE] = MAXX(CURRENTGROUP(), 'Data'[END_DATE])
)
)

Re: DAX GROUPBY and keep all columns

Dangar332 — Thu, 30 Nov 2023 14:27:00 GMT

hi, Przem

try below

Table 2 = GROUPBY('Table (2)','Table (2)'[ORA ID],"project id",MAXX(CURRENTGROUP(),'Table (2)'[PROJECT ID]),"end date",MAXX(CURRENTGROUP(),'Table (2)'[END_DATE]))

Table 3 = var a = MAX('Table (2)'[END_DATE]) return SUMMARIZE( 'Table (2)','Table (2)'[ORA ID], "project id",CALCULATE(MIN('Table (2)'[PROJECT ID]),'Table (2)'[END_DATE]=MAX('Table (2)'[END_DATE])) ,"s",MAX('Table (2)'[END_DATE]) )

Re: DAX GROUPBY and keep all columns

Przem — Fri, 01 Dec 2023 08:09:16 GMT

Thank you,
There is an error related to CURRENTGROUP() - "Parameter is not the correct type"

Re: DAX GROUPBY and keep all columns

Przem — Fri, 01 Dec 2023 10:47:20 GMT

Second option works, thank you so much 🙂