Calculated Column B to Record only Unique Values of Column A

[Anonymous]

Is it possible to create a calculated column B (I named it as "Interim" in the image), which records values only once, from column A ("DailyTimeSpent") and remains zero, if it encounters the column A value again.

Example: Interim column should record 14942 for the filter conditions of UserID 2293 and Date of 10-08-2018 *BUT* when it encounters the same number again for the same filter conditions of UserID 2293 and Date of 10-08-2018, towards the end of the table, it should record Zero.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to add a rank column first. Then you may get the interim column. For example:

Rank =
RANKX (
    FILTER (
        Table1,
        Table1[UserID] = EARLIER ( Table1[UserID] )
            && Table1[DailyTimeSpent] = EARLIER ( Table1[DailyTimeSpent] )
    ),
    Table1[PersonalID],
    ,
    ASC,
    DENSE
)

Interim =
IF ( Table1[Rank] = 1, Table1[DailyTimeSpent], 0 )

Regards,

Cherie