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.
Hi! I have the below table where the data gets generated every 1-2 seconds.
Column A - Datetime
Column B - Cumulative Counter
Column C - Calculated in Excel to remove cumulative frequency IF(B2>=B1,B2-B1,B2)
Please help me identify a way using power(m) query to calculate Column C in power bi.
Due to the large dataset (over 3 million records), the dax query keeps on processing but won't execute.
Thanks!
A | B | C |
10/20/20 2:43 | 5.00 | 5.00 |
10/20/20 2:44 | 5.00 | - |
10/20/20 2:45 | 7.00 | 2.00 |
10/20/20 2:46 | 15.00 | 8.00 |
10/20/20 2:48 | 70.00 | 55.00 |
10/20/20 2:49 | 97.00 | 27.00 |
10/20/20 2:50 | 124.00 | 27.00 |
10/20/20 2:51 | 151.00 | 27.00 |
10/20/20 2:52 | - | - |
10/20/20 2:53 | 1.00 | 1.00 |
10/20/20 2:54 | 13.00 | 12.00 |
10/20/20 2:56 | 67.00 | 54.00 |
Solved! Go to Solution.
I was able to use the below query for a dax calculated column which gave me the desired results without taking a long time to process the large dataset. Had to create an index column first in power query.
I was able to use the below query for a dax calculated column which gave me the desired results without taking a long time to process the large dataset. Had to create an index column first in power query.
Hi @pwrbiadm ,
Due to your large dataset, it is suggested to create a measure like so:
Measure =
VAR ThisRow_B =
MAX ( [B] )
VAR LastRow_B =
CALCULATE (
MAX ( 'Query1 (2)'[B] ),
FILTER (
ALLSELECTED ( 'Query1 (2)' ),
'Query1 (2)'[Index]
= MAX ( 'Query1 (2)'[Index] ) - 1
)
)
RETURN
SWITCH ( TRUE (), ThisRow_B >= LastRow_B, ThisRow_B - LastRow_B, ThisRow_B )
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it faster.
Hi @pwrbiadm
This might run faster in DAX. However, place the following M code in a blank query to see the steps. If it's not fast enough we'll try another approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDsAgCAWvYlibFlC0ehXj/a/RNl01fZiUsCIzhM8YJLzrnUF7ThQpOGEbM834FvJfwRZCRULxBIH9Dw+vjPDm4Q0NY4xx0YxocWgTROtFf4qrh8Au7kMkIdy9bnn2nyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type number}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"B"}),
col_ = List.RemoveLastN({0} & #"Replaced Value1"[B], 1),
res_ = Table.FromColumns(Table.ToColumns(#"Replaced Value1") & {col_}, {"A", "B","B_2"}),
#"Changed Type1" = Table.TransformColumnTypes(res_,{{"B_2", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [B]-[B_2], type number),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,0,Replacer.ReplaceValue,{"B_2"})
in
#"Replaced Value"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |