Calculated Index Column

[Preetish_1]

Hello everyone,

I was wondering if someone could help me figure out how to create a calculated index column using DAX. I would like to use a user name column and a date column, indexing the first occurence of a particular user as 1, and incrementing the next occurences.

The attached image shows a sample of the data.

Thanks.

Preetish

[mbuick]

Hi,

I'm trying to do the same workflow but get the following error;

A single value for column 'Location_id' in table 'Master_Monitoring (Azure)' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Below is a subset of the data I am using;

MeterReading,Monitoring_DateTime,Location_ref
73496, Sun Oct 01 2017 10:05:00 ,ACS-01 [2682]
73503, Mon Oct 02 2017 07:35:00 ,ACS-01 [2682]
73509, Tue Oct 03 2017 10:25:00 ,ACS-01 [2682]
73509, Wed Oct 04 2017 16:15:00 ,ACS-01 [2682]
73509, Thu Oct 05 2017 08:06:00 ,ACS-01 [2682]
73509, Fri Oct 06 2017 09:06:00 ,ACS-01 [2682]
, Fri Oct 06 2017 15:30:00 ,ACS-02 [2683]
73570, Sat Oct 07 2017 13:50:00 ,ACS-01 [2682]
73711, Sun Oct 08 2017 15:14:00 ,ACS-01 [2682]
73711, Mon Oct 09 2017 12:47:00 ,ACS-01 [2682]
73746, Tue Oct 10 2017 11:45:00 ,ACS-01 [2682]
73762, Wed Oct 11 2017 08:40:00 ,ACS-01 [2682]
, Wed Oct 11 2017 12:37:00 ,ACS-01 [2682]
, Wed Oct 11 2017 15:10:00 ,ACS-02 [2683]
73772, Thu Oct 12 2017 07:37:00 ,ACS-01 [2682]
73778, Fri Oct 13 2017 07:51:00 ,ACS-01 [2682]
, Fri Oct 13 2017 08:00:00 ,ACS-02 [2683]
73778, Sat Oct 14 2017 09:20:00 ,ACS-01 [2682]
73793, Sun Oct 15 2017 08:00:00 ,ACS-01 [2682]
73831, Mon Oct 16 2017 07:10:00 ,ACS-01 [2682]
73864, Tue Oct 17 2017 06:35:00 ,ACS-01 [2682]
73904, Wed Oct 18 2017 10:20:00 ,ACS-01 [2682]
73952, Thu Oct 19 2017 06:15:00 ,ACS-01 [2682]
73971, Fri Oct 20 2017 06:15:00 ,ACS-01 [2682]
73988, Sat Oct 21 2017 06:00:00 ,ACS-01 [2682]
, Sat Oct 21 2017 11:52:00 ,ACS-02 [2683]
73998, Sun Oct 22 2017 06:30:00 ,ACS-01 [2682]
74018, Mon Oct 23 2017 11:00:00 ,ACS-01 [2682]
74071, Tue Oct 24 2017 08:00:00 ,ACS-01 [2682]
74080, Wed Oct 25 2017 11:00:00 ,ACS-01 [2682]
74093, Thu Oct 26 2017 08:05:00 ,ACS-01 [2682]
74104, Fri Oct 27 2017 07:30:00 ,ACS-01 [2682]
, Fri Oct 27 2017 10:40:00 ,ACS-02 [2683]
74104, Sat Oct 28 2017 08:30:00 ,ACS-01 [2682]
74146, Sun Oct 29 2017 07:50:00 ,ACS-01 [2682]
74180, Mon Oct 30 2017 07:35:00 ,ACS-01 [2682]
74207, Tue Oct 31 2017 07:00:00 ,ACS-01 [2682]
74207, Tue Oct 31 2017 07:38:00 ,ACS-01 [2682]

[Preetish_1]

Hi,

Did you try a Calculated Column or a Measure?

As the above formula was for a Calculated Column.

[mbuick]

HI,

This is a calculated column as per the example script in the original post.

thanks.

[vik0810 · Accepted solution]

Use following DAX for the column

Index =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        Table,
        Table[User] = EARLIER ( Table[User] )
            && Table[Date] <= EARLIER ( Table[ Date] )
    )
)

[Anonymous]

Can you do this from within a Summarize table as well or does it have to be a calculated column?

(And many thanks, this was just what I needed.)

[Preetish_1]

Hey @vik0810,

Thank you for your solution.

I got it to work

Thanks