Text Field Counted Twice

[EPJones]

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 DATE	CATEGORY	ROUTE	COACH #	LOCATION	INCIDENT / PROBLEM
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Dropped / Missed Trip	123	None	North Yard	Due to manpower
Sunday, January 1, 2023	Equipment	321	1505	North Yard	Engine lights. No service delay.
Sunday, January 1, 2023	Customers / Operators	321	1703	North Yard	Passenger put bike on rack and would not remove it, coach going out of service.
Monday, January 2, 2023	Equipment	3521	1911	North Yard	Engine lights.   no service delay.
Tuesday, January 3, 2023	Dropped / Missed Trip	321	None	North Yard	Due to manpower
Tuesday, January 3, 2023	Dropped / Missed Trip	321	None	North Yard	Due to manpower
Tuesday, January 3, 2023	Coach Substitution	321	553	North Yard	Accident
Tuesday, January 3, 2023	Coach Substitution	123	563	North Yard	Accident
Tuesday, January 3, 2023	Coach Substitution	321	1909	North Yard	Accident
Tuesday, January 3, 2023	Coach Substitution	123	1713	North Yard	Accident
Tuesday, January 3, 2023	Coach Substitution	3213	1724	North Yard	Accident
Tuesday, January 3, 2023	Coach Substitution	213	9822	North Yard	Due to manpower
Tuesday, January 3, 2023	Equipment	213	565	North Yard	Coach was no start. trip started delayed 12 minutes.
Tuesday, January 3, 2023	Coach Substitution	312	9815	North Yard	Mechanical issue
Tuesday, January 3, 2023	Customers / Operators	322	563	North Yard	Accident
Tuesday, January 3, 2023	Coach Substitution	213	9909	North Yard	Mechanical issue
Tuesday, January 3, 2023	Dropped / Missed Trip	3213	554	North Yard	Due to manpower

[ryan_mayu]

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

[EPJones]

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. 

[Ashish_Mathur]

Hi,

Check the steps in the Query Editor.  In the second/third step, then number of rows should be equal to the number of files lying in the folder from there you are importing data.

[ryan_mayu]

don't know why what you get is counting twict.

i think you don't even need to create dax

pls see the attachment below

[EPJones]

I could indeed just do a pivot table, but I'm asked to plop this into PBI, hence why I'm fighting with it. I'll just go ahead and re-import as a csv, I haen't tried that yet, maybe it'll remove whatever odd thing is causing PBI to count everything twice.

Thank you for looking at my question, I really appreciate it!

[ryan_mayu]

so it's a data importing issue? pls provide the original file.

[EPJones]

I figured it out, thank you! Turned out my original data was corrupted somehow, and it reported twice. Thank you!