Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Preetish_1
Helper I
Helper I

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.

PreetishCapture.JPG

 

1 ACCEPTED SOLUTION
vik0810
Resolver V
Resolver V

Use following DAX for the column

 

 

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

View solution in original post

6 REPLIES 6
mbuick
Frequent Visitor

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]

Hi,

 

Did you try a Calculated Column or a Measure?

As the above formula was for a Calculated Column.

 

 

HI,

 

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

 

thanks.

vik0810
Resolver V
Resolver V

Use following DAX for the column

 

 

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

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

Hey @vik0810,

 

Thank you for your solution.

I got it to work

 

Thanks

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.