Forum Discussion
simple count by year
Hi SeattleCrime ,
Personally, I wouldn't worry too much about a date tble just yet. You will need one when you want to get into more advanced time-intelligence functions but, for now, you'll probably get along better trying the following:
1) In Power Query Editor highlight your [offence_start_datetime] column and use this button on the ribbon to get a new column that is just the date:
This new date column is what you will use as your visual axes. As it's unlikely that many datetime values will align with others in terms of counts, this way you will be agreggating counts over entire days, so you should get more rewarding outputs.
2) You can set any fields that you drag and drop into your visuals to count if you want the easiest/quickest ouput. Just drag the fields to your visual then, on the right in the 'Values' area, right-click on the field you want to count and select the count option:
When you start getting a bit more comfortable, the correct way to implement this would be using measures similar to this:
_noofItems = COUNT(yourTable[FieldName]) //or _noofUniqueItems = DISTINCTCOUNT(yourTable[FieldName])Hopefully the above should get you started very quickly doing what you want to do then, once your knowledge grows, you can start relating date tables and creating more complex time-intelligence functions.
Pete
SeattleCrime Do you have date table or date field in your data. Without havinv a date field, it will not be possible to perform time intelligence calculation. Either create a seperate date table and then link it to your factTable in case you have the date field in your data then it will be easier to perform YoY and other time intelligence calculation.
negi007, below is a sample screen shot of the data table. I will research creating this separate date table and linking it to the factTable. All of this is completely new to me. Trying to find some content that's directly relatable with values to count over time in a help forum has been a bit challenging.
Hi SeattleCrime ,
Personally, I wouldn't worry too much about a date tble just yet. You will need one when you want to get into more advanced time-intelligence functions but, for now, you'll probably get along better trying the following:
1) In Power Query Editor highlight your [offence_start_datetime] column and use this button on the ribbon to get a new column that is just the date:
This new date column is what you will use as your visual axes. As it's unlikely that many datetime values will align with others in terms of counts, this way you will be agreggating counts over entire days, so you should get more rewarding outputs.
2) You can set any fields that you drag and drop into your visuals to count if you want the easiest/quickest ouput. Just drag the fields to your visual then, on the right in the 'Values' area, right-click on the field you want to count and select the count option:
When you start getting a bit more comfortable, the correct way to implement this would be using measures similar to this:
_noofItems = COUNT(yourTable[FieldName]) //or _noofUniqueItems = DISTINCTCOUNT(yourTable[FieldName])Hopefully the above should get you started very quickly doing what you want to do then, once your knowledge grows, you can start relating date tables and creating more complex time-intelligence functions.
Pete
Thank you BA_Pete ! Took me a while to crack the right-click to get into Power Query! I also looked up this MS Power Query tutorial. Fantastic. I'm starting to grasp the layers happening here. Very exciting.
