Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Im trying to get a consecutive days count on data for each month and year. The data schema looks like this:
DATE - Flag
01/01/2021 - 1
01/02/2021 - 1
01/03/2021 - 0
01/04/2021 - 0
01/05/2021 - 1
01/06/2021 - 0
01/07/2021 - 1
----------------------------
Basically I just need a the MAX consecutive days that a flag of 0 was enabled. So for the data above, it would show 2, since the largest date range with a 0 flag is 2 (jan 3 and 4).
Output would be
January 2021 - 2
February 2021 - X
March 2021 - Y
Any suggestions?
Solved! Go to Solution.
Such an operation involves recursion, which is unnecessarily complex. Power Query does the trick easily.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0lQ6VYHYiQEaaQMUzIAC5kgilkiqnRDFOVOaYqC0whSwwhQwMkoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Flag = _t]),
#"Grouped Rows" = Table.Group(Source, {"Flag"}, {{"ar", each _}, {"Count", each Table.RowCount(_), Int64.Type}}, 0),
#"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"DATE"}, {"DATE"})
in
#"Expanded ar"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Anonymous,
Did the above suggestions and expressions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.
If these also not help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Such an operation involves recursion, which is unnecessarily complex. Power Query does the trick easily.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0lQ6VYHYiQEaaQMUzIAC5kgilkiqnRDFOVOaYqC0whSwwhQwMkoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Flag = _t]),
#"Grouped Rows" = Table.Group(Source, {"Flag"}, {{"ar", each _}, {"Count", each Table.RowCount(_), Int64.Type}}, 0),
#"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"DATE"}, {"DATE"})
in
#"Expanded ar"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |