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