Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I am looking for a M query to return the difference in times between two cells in the same column. I have been able to do this a couple other ways (DAX formulas and creating two different tables with index values), but keep getting the error of not enough memory. My table is 3 million rows and I need all of the data so I cannot remove anything. Does anyone have a way around this? Im thinking M query would be the way to go but have not been able to create one.
Hi @Anonymous
Which method do you use?
from this statement:
I have been able to do this a couple other ways (DAX formulas and creating two different tables with index values)
It seems you use a complex method including creating another table.
But i used to slove this kind of problem with simple method below:
create calculated columns:
Column_Previous = CALCULATE(MAX(Sheet2[time]),FILTER(Sheet2,Sheet2[time]<EARLIER(Sheet2[time])))
diff_s = DATEDIFF([Column_Previous],[time],SECOND)
Or you could create a measure (measure will be better for performance generally, but measures can't be added into slicer visual)
Measure =
VAR Previous =
CALCULATE (
MAX ( Sheet2[time] ),
FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[time] < MAX ( Sheet2[time] ) )
)
RETURN
DATEDIFF ( Previous, MAX ( Sheet2[time] ), SECOND )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous,
Assuming that the rows are in the correct order, create an index on your table(unless you have something else in your dataset that uniquely identifies your rows which is also a number, then you can use this). This index is used to identify the previous row. You create it by Add Column -> Index Column.
Then create a custom column with a code like this(note that theM is case sensitive.):
= if
#"Added Index"{[Index]} [Index] = 0 then null
else
#"Added Index"{[Index]} [dummyDateTime] - #"Added Index"{[Index]-1} [dummyDateTime]
This code generates a new column where the values are the value of the current row - the value of the previous row. The if-statement is there to prevent an error for the first row where there is no prior row to compare with.
I have created a mockup report here: mockup
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
I get the following error when putting in this query.
"Expression.Error: The field 'date' of the record wasnt found."
Not sure why Im getting this. Are the other columns in the table interferring with this query?
what is the code you have entered?
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |