'How to create a column based on grouped condition?
My test tabe in powerbi:
| IdRecord | Date | Value |
|---|---|---|
| 1 | 2022-04-25 23:45:00.000 | 100 |
| 1 | 2022-04-24 18:07:00.000 | 344 |
| 2 | 2022-05-01 23:45:00.000 | 5 |
| 2 | 2022-05-02 18:07:00.000 | 66 |
| 2 | 2022-05-03 18:07:00.000 | 31 |
I require to create a calculated column to mark the earliest of the records grouped by id.
Desired output
| IdRecord | Date | Value | IsFirst |
|---|---|---|---|
| 1 | 2022-04-25 23:45:00.000 | 100 | 0 |
| 1 | 2022-04-24 18:07:00.000 | 344 | 1 |
| 2 | 2022-05-01 23:45:00.000 | 5 | 1 |
| 2 | 2022-05-02 18:07:00.000 | 66 | 0 |
| 2 | 2022-05-03 18:07:00.000 | 31 | 0 |
Solution 1:[1]
Answering to myself
FirstRes= VAR MYMIN = CALCULATE(
MIN(Table[Date]),
FILTER ( Table, Table[IdRecord] = EARLIER(Table[IdRecord]))
)
RETURN
IF(CALCULATE(
MIN(MIN(Table[Date]),MYMIN),
FILTER ( Table, Table[IdRecord] = EARLIER ( Table[IdRecord] ) )
) = Table[Date],1,0)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Andres Mora |