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,
I need your help. I've been trying to do this with DAX but no luck. I'm stuck. What I want to achieve is for the running count to reset if the value of column "Instance within 5 weeks" is 1. If the column value is 1, then the running count resets to 1. If the next row is not 1, then the running count becomes 2 and then 3 and 4 which will reset to 1 if "Instance within 5 weeks" is 1 again.
Here's the pq script of the sample data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdI7C4MwFAXgvyKZHXLNQzN26Gpfo0iR0qEUdPH/Y87NbSHTFZSA+W6OB6fJ+N605nq+Py7jc9ya5bV/trXZl+97zS9uJ5svyivyePzvuc10UGlKeeWwr6tp0ihZC8peeMc0WJ0iqwfzQl2hpNMABRaE+kI7nUbsBosSOhTqVEqYzun6qqbgdcoHwQ91TUGn/Jnwv5alpqhTTOd0ZKVm6Un9nYgwnuMRSc+5qPkA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, #"Person & Error" = _t, #"QA#" = _t, #"Work week" = _t, #"Running Instance" = _t, #"Instance within 5 weeks" = _t, #"Desired Result" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Person & Error", type text}, {"QA#", type text}, {"Work week", Int64.Type}, {"Running Instance", Int64.Type}, {"Instance within 5 weeks", Int64.Type}, {"Desired Result", Int64.Type}}) in #"Changed Type"
Proud to be a Super User!
Solved! Go to Solution.
Hello,
For a DAX calculated column I would do something like this (it assumes the Index column increments by 1 on each row):
Result = VAR CurrentIndex = Data[Index] VAR PreviousResetIndex = CALCULATE ( MAX ( Data[Index] ), ALL ( Data ), Data[Index] <= CurrentIndex, Data[Instance within 5 weeks] = 1 ) RETURN CurrentIndex - PreviousResetIndex + 1
Does that work?
Hello,
For a DAX calculated column I would do something like this (it assumes the Index column increments by 1 on each row):
Result = VAR CurrentIndex = Data[Index] VAR PreviousResetIndex = CALCULATE ( MAX ( Data[Index] ), ALL ( Data ), Data[Index] <= CurrentIndex, Data[Instance within 5 weeks] = 1 ) RETURN CurrentIndex - PreviousResetIndex + 1
Does that work?
Thank you! This works like a charm. I was actually able to come up with the same result but a longer solution. Had to use LASTNONBLANK() to do an filldown of the running instance only if the instance column is 1 and then create another column to do a count based on the unique criteria and if index is less than the current index.
Your solution is way shorter and more elegant.
Proud to be a Super User!
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |