Forum Discussion
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 | 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
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"))+1In 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.
3 Replies
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 )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
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"))+1In 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.
