New to PowerBI, need assistance

[kcalderw]

I am an adjunct professor where I utilize Twitter in my class. I am looking for an easier (and more visual) way to grade my students' activity on Twitter. Previously this was always a tedious manual process involving scanning their timelines myself and counting their tweets on various methods. I have found a Chrome extension which I can now download their timeline to a spreadsheet. I want to see if PowerBI can do the following:

1) Calculate the total number of tweets. (based on a date range, usually start of the semester to midpoint then midpoint to end)
2) Calculate the frequency of tweets per week.
3) Show the number of tweets with their blog post included (they all use blogger). I was thinking of creating a custom filter which looks for the word "blogspot" in their tweet.
4) number of tweets out of their total that included the class hashtag
5) Show number of original tweets (not just straight RTs or their Paper.li auto posts)

Also, I’d like to calculate all this data based on a date range. I give them a midterm and final grade. Is this easy enough to set up?

The headers on the downloaded Twitter timeline include:

Created_at (date)

text (twitter post)

fav (# of times the post was favorited)

rt (# of times it was retweeted)

[v-jiascu-msft]

Hi @kcalderw,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[HotChilli]

It all looks possible.  Make sure the datatypes are correct in the Modelling tab.

Experiment by dragging fields on to a table.

You might need a from/to date slicer to select the timeframe and you can use Visual Filters for items 3 and 4.

You could write DAX to accomplish all the above but if you're new to powerbi, experiment with the visual stuff first.

[kcalderw]

Ok, I'm slowly getting there. I have a question on the second chart.

I'm unsure what the 112 number is. I broke this chart down to # of tweets that included the hashtag between set dates. This student only has 71 total tweets between the start of the semester and the date of timeline capture (Nov 9th).

[HotChilli]

The gauge is usually used for 'progress towards a goal' so powerbi has taken 56 (which will come from your data somehow) and doubled it as a best guess because it can't find a target value.

[kcalderw]

Ah I see, I thought it would create a visual like that for "out of x total of tweets, y included the hashtag." I'm trying to find a way to have the total dates broken down by x amount of tweets per week but I can't seem to get that data.

[kcalderw]

One last question, how can I code the weeks of our semester? For instance, I'd like week 1 to be from Sept 5th - 12th. This is so I can visualize their posts by week.

[v-jiascu-msft]

Hi @kcalderw,

The formula could be like below. Since the week number is different in the different year, what's the rule to add a week number?

WeekNum = WEEKNUM([Date], 2)

Best Regards,
Dale

[HotChilli]

The standard answer is to create a Date dimension.  There are plenty of examples online.  One of your columns would be "Semester Week No" and you would have to code or enter directly that number for each day in your date table. 

You then add a relationship between the Date dimension and the rest of the model.  This allows you to slice and dice by week/day/month/semester.... anything you want.

If you don't want those features and want to keep the model simple (because I think you only have one table) add a calculated column to your table for "Semester Week No". The formula for the column would look at the date of the row and assign a semester week no.