Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Max gap between 2 values using DAX

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 ...
  • Icey's avatar
    Icey
    4 years ago

    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.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Icey
      Community Support
      4 years ago

      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.