Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX Consecutive days based on a flag

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?
 

2 ACCEPTED SOLUTIONS
CNENFRNL
Community Champion
Community Champion

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"

 

Screenshot 2021-07-09 215438.png


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!

View solution in original post

Jihwan_Kim
Super User
Super User

Picture2.png

 

MAX consecutive days that a flag of 0 =
VAR _newtable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Dates[Date], Data[Flag] ),
"@cumulate_flag",
CALCULATE (
CALCULATE (
SUM ( Data[Flag] ),
ALL ( Data[Flag] ),
VAR _currentdates =
MAX ( Dates[Date] )
VAR _currentmonth =
MAX ( Dates[End of Month] )
RETURN
FILTER (
ALL ( Dates[Date], Dates[End of Month] ),
Dates[Date] <= _currentdates
&& Dates[End of Month] = _currentmonth
)
)
)
),
Data[Flag] = 0
)
VAR _groupbyrowcount =
GROUPBY (
_newtable,
[@cumulate_flag],
"@rowcount", SUMX ( CURRENTGROUP (), 1 )
)
RETURN
IF (
HASONEVALUE ( Dates[Month & Year] ),
MAXX ( _groupbyrowcount, [@rowcount] )
)
 
 
 
 

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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Jihwan_Kim
Super User
Super User

Picture2.png

 

MAX consecutive days that a flag of 0 =
VAR _newtable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Dates[Date], Data[Flag] ),
"@cumulate_flag",
CALCULATE (
CALCULATE (
SUM ( Data[Flag] ),
ALL ( Data[Flag] ),
VAR _currentdates =
MAX ( Dates[Date] )
VAR _currentmonth =
MAX ( Dates[End of Month] )
RETURN
FILTER (
ALL ( Dates[Date], Dates[End of Month] ),
Dates[Date] <= _currentdates
&& Dates[End of Month] = _currentmonth
)
)
)
),
Data[Flag] = 0
)
VAR _groupbyrowcount =
GROUPBY (
_newtable,
[@cumulate_flag],
"@rowcount", SUMX ( CURRENTGROUP (), 1 )
)
RETURN
IF (
HASONEVALUE ( Dates[Month & Year] ),
MAXX ( _groupbyrowcount, [@rowcount] )
)
 
 
 
 

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.


Go to My LinkedIn Page


CNENFRNL
Community Champion
Community Champion

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"

 

Screenshot 2021-07-09 215438.png


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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.