simple count by year

[SeattleCrime]

I'm an uber beginner with Power BI and do not at all comprehend the expressions, so I'm trying to "drag and drop" by way to just achieve a simple count by year.  Here's the public data source: https://data.seattle.gov/api/odata/v4/tazs-3rd5.

I've been able to publish some interactive maps , but I'm also trying to add some simple counts by year and show YoY changes, etc.  I can't seem to figure this out even after exploring multiple help articles.  For some reason, I thought this would be as simple as dragging the offense and offense_start data into the same visual, but that just creates a pivot list.  Any help greatly appreciated.

[negi007]

@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.

[SeattleCrime]

@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.

[BA_Pete · Accepted solution]

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]

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.

[BA_Pete]

@SeattleCrime ,

Very happy to see a new user full of optimism hitting the forum, and happy I could help you on your Power BI journey - it is an exciting one for sure (if you're into this kind of thing!).

Pete