Max gap between 2 values using DAX

[Anonymous]

Hi all,

I am trying find the maximum gap between 2 values, but that excludes Saturday and Sunday.

Eg: 

	Sun	Mon	Tue	Wed	Thur	Fri	Sat
1	F	T	F	F	F	F	F
2	F	T	T	T	F	F	F
3	F	T	T	T	T	T	F

In the above table, I would like to calculte the max gap between 2 T's (Sat and Sun excluded)

So in the first row, the max gap will be 5

In the second row the max gap will be 3

In the third row the max gap will be 1

Can I please if it is possible to implement this in DAX?

If so, can i please understand on how to implement?

Any help is appreciated!

Thank you!

Megha

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Max gap between two Ts: =
IF (
    HASONEVALUE ( Data[Row Index] ),
    CALCULATE (
        COUNTROWS ( 'Weekday' ),
        FILTER ( RELATEDTABLE ( Data ), Data[Value] <> "T" )
    ) - 1
)

[Anonymous]

Thank you! @Jihwan_Kim 

The table is in this format and there are multiple values. 

Is there a way I can get the maximum gap in the same way without changing the format?

Thank you!

Megha

[Icey · Accepted solution]

Hi @Anonymous ,

In your sample, try to create a custom column in Power Query Editor like so:

= List.Count(List.Select(Record.ToList(Record.SelectFields(_,{"Monday","Tuesday","Wednesday","Thursday","Friday"})), each _ =  "F"))+1

In addition, here is the complete query and you can create a blank query and paste it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXID4hAojYpjdaKVjJBUhGCoBKkwxqICoTI2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Sunday = _t, Monday = _t, Tuesday = _t, Wednesday = _t, Thursday = _t, Friday = _t, Saturday = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Sunday", type text}, {"Monday", type text}, {"Tuesday", type text}, {"Wednesday", type text}, {"Thursday", type text}, {"Friday", type text}, {"Saturday", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Gap", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,{"Monday","Tuesday","Wednesday","Thursday","Friday"})), each _ =  "F"))+1)
in
    #"Added Custom"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.