Forum Discussion

Preetish_1's avatar
Preetish_1
Helper I
8 years ago
Solved

Calculated Index Column

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

 

Need Help
  • vik0810's avatar
    vik0810
    8 years ago

    Use following DAX for the column

     

     

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

6 Replies

  • vik0810's avatar
    vik0810
    Resolver V
    8 years ago

    Use following DAX for the column

     

     

    Index =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        Table,
        Table[User] = EARLIER ( Table[User] )
            && Table[Date] <= EARLIER ( Table[ Date] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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.)

  • mbuick's avatar
    mbuick
    Frequent Visitor
    8 years ago

    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's avatar
      Preetish_1
      Helper I
      8 years ago

      Hi,

       

      Did you try a Calculated Column or a Measure?

      As the above formula was for a Calculated Column.

       

       

      • mbuick's avatar
        mbuick
        Frequent Visitor
        8 years ago

        HI,

         

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

         

        thanks.