Forum Discussion

EPJones's avatar
EPJones
New Member
2 years ago

Text Field Counted Twice

I have a fairly simple excel spreadsheet but it's pretty long. I'm trying to visually represent how many trips per day for manpower reasons, and it counts all instances twice, I can't figure out why. I reimported the data, I copied/pasted the spreadsheet to a new one, tried countless DAX options, nothing works. It's a text field so the usual Max count doesn't work. I've tried everything in my meager arsenal, and I can't figure out why it counts everything twice. I would greatly appreciate any help, I spent my whole day on this and still didn't get anything. It seems much simpler than other queries I've managed to pull off, but this is text so I guess that's my issue. Thank you!

SERVICE DATECATEGORYROUTECOACH #LOCATIONINCIDENT / PROBLEM
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Dropped / Missed Trip123NoneNorth YardDue to manpower
Sunday, January 1, 2023Equipment3211505North YardEngine lights. No service delay. 
Sunday, January 1, 2023Customers / Operators3211703North YardPassenger put bike on rack and would not remove it, coach going out of service. 
Monday, January 2, 2023Equipment35211911North YardEngine lights.   no service delay. 
Tuesday, January 3, 2023Dropped / Missed Trip321NoneNorth YardDue to manpower
Tuesday, January 3, 2023Dropped / Missed Trip321NoneNorth YardDue to manpower
Tuesday, January 3, 2023Coach Substitution321553North YardAccident
Tuesday, January 3, 2023Coach Substitution123563North YardAccident
Tuesday, January 3, 2023Coach Substitution3211909North YardAccident
Tuesday, January 3, 2023Coach Substitution1231713North YardAccident
Tuesday, January 3, 2023Coach Substitution32131724North YardAccident
Tuesday, January 3, 2023Coach Substitution2139822North YardDue to manpower
Tuesday, January 3, 2023Equipment213565North YardCoach was no start. trip started delayed 12 minutes. 
Tuesday, January 3, 2023Coach Substitution3129815North YardMechanical issue
Tuesday, January 3, 2023Customers / Operators322563North YardAccident
Tuesday, January 3, 2023Coach Substitution2139909North YardMechanical issue
Tuesday, January 3, 2023Dropped / Missed Trip3213554North YardDue to manpower
Need Help

7 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    what's the expected output based on the sample data you provided?

    • EPJones's avatar
      EPJones
      New Member
      2 years ago

      Ah yes, my apologies!

      This is what I want:

       

      January 1   -  8trips missed due to manpower

                         - 1 trip missed due to mechanical/engine

                         - 1 trip missed due to other


      This is what I currently get, it double counts everything:

      January 1   -  16 trips missed due to manpower

                         - 2 trips missed due to mechanical/engine

                         - 2 trips missed due to other


      I'm looking for a graph that will show how many trips were missed due to manpower (in particular, others too) each day YTD.