Help on Counting how many times a driver is active during a week

[Anonymous]

Hello

I'm trying to seek some help and find out a way to count how many days a driver is active during the week, example the business is making deliveries and I have a date table and delivery information as per orders. 

how can I count when a driver is active with a 1 in each day he is active,

Driver            Monday Tuesday Wednesday Thursday Friday Saturday Sunday

John Doe           1                           1                0             0            0           1            3

each order has a unique ID, but I only need to say 1 or 0 and totally at the end, 

if I need some information to provide or explain, please let me know

thanks 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your sample data, I created the following

I used DAX to create a independent driver name table.

DriverName = FILTER(DISTINCT('Sheet2'[DriverName]),[DriverName]<>BLANK())

This is a table by entering data.

This is the measure that returns 1 or 0.

Measure = IF(MAX('Table'[Num])=CALCULATE(MAX('Sheet2'[Weekday]),FILTER('Sheet2',[DriverName]=SELECTEDVALUE(DriverName[DriverName]))),1,0)

The result is this.

Check more details from the attachment.

Best Regards,

Stephen Tao

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

[Ashish_Mathur]

Hi,

Extract the time stamp from the DelWindowStart column into another column.  Prepate a Calendar Table with a DoW column.  Create a relationship from the Date column of the Table to the Date column of the Calendar Table.  To your vidual, drag DoW to the column section and Drive name to the row section.  Write this measure

=distincocount(Data[Driverid])

Hope this helps.

[FrankAT]

Hi @Anonymous ,

sorry, but your provided sample data doesn't have the ability to get your expected answer. Your data should hold 2 or three weeks and less drivers. Can you simplify your sample data?

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEyBGIDJNoAzo+NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Driver = _t, Monday = _t, Tuesday = _t, Wednesday = _t, Thursday = _t, Friday = _t, Saturday = _t, Sunday = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Driver", type text}, {"Monday", Int64.Type}, {"Tuesday", Int64.Type}, {"Wednesday", Int64.Type}, {"Thursday", Int64.Type}, {"Friday", Int64.Type}, {"Saturday", Int64.Type}, {"Sunday", Int64.Type}}),
    Sum = Table.AddColumn(#"Changed Type", "Total", each List.Sum(Record.ToList(Record.RemoveFields(_, "Driver"))))
in
    Sum

[Anonymous]

I forgot to mention that this is a table from a SQL server, not an actual excel, so I'm not sure how to add this code in there

thanks 

[Anonymous]

Thanks and is this a new measure or a new table

[FrankAT]

Hi @Anonymous ,

can you provide some sample data?

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

https://drive.google.com/file/d/1xMh0FZYUKUxiA7Ky7LFRdS_CEg2tCNo6/view?usp=sharing

here is some data and is missing the calendar table which is just a normal day, week, month, and year

thanks