Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Use following DAX for the column
Index = CALCULATE ( COUNTROWS ( Table ), FILTER ( Table, Table[User] = EARLIER ( Table[User] ) && Table[Date] <= EARLIER ( Table[ Date] ) ) )
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.
Use following DAX for the column
Index = CALCULATE ( COUNTROWS ( Table ), FILTER ( Table, Table[User] = EARLIER ( Table[User] ) && Table[Date] <= EARLIER ( Table[ Date] ) ) )
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.)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |