Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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"))+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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |