Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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.
Solved! Go to Solution.
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]))
or
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])
)
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]))
or
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])
)
Second option works, thank you so much 🙂
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])
)
)
Thank you,
There is an error related to CURRENTGROUP() - "Parameter is not the correct type"
User | Count |
---|---|
13 | |
10 | |
8 | |
7 | |
5 |
User | Count |
---|---|
24 | |
16 | |
15 | |
10 | |
7 |