cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SeattleCrime
Regular Visitor

simple count by year

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.

SeattleCrime_0-1608048606783.png

 

SeattleCrime_0-1608047083025.png

 

1 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:

BA_Pete_0-1608050480261.png

 

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:

BA_Pete_1-1608050696682.png

 

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

View solution in original post

5 REPLIES 5
negi007
Super User
Super User

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




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


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

 

SeattleCrime_0-1608048082811.png

 

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:

BA_Pete_0-1608050480261.png

 

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:

BA_Pete_1-1608050696682.png

 

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

View solution in original post

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.

 

SeattleCrime_1-1608072945892.png

 

 

 

 

@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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors