Hi.
I've been reading this article and trying to use the template (thanks very much for this) with some new pages / customisations. I'm trying to get my head around the DAX formulas for average messages sent/receive and average message sizes. I have been trying to validate the results against a small date selection from my message tracking logs but I'm struggling to match the figures up.
I copied the average message sent formula and changed this to be relevant to our three domains (I guess this is so I only pick internal). I have then created new tables for distinct recipients (one table) and distinct senders (another table), with relationships to the trackinglog table. (see below)
AvgMessageSent = (CALCULATE(DISTINCTCOUNT(TrackingLog[message-id]),TrackingLog[event-id]="RECEIVE",TrackingLog[source]="STOREDRIVER",SEARCH("@mydomain.a.co.uk",TrackingLog[sender-address],1,0)||SEARCH("@mydomain.b.co.uk",TrackingLog[sender-address],1,0)))/(CALCULATE(DISTINCTCOUNT(TrackingLog[sender-address]),SEARCH("@mydomain.a.co.uk",TrackingLog[sender-address],1,0)||SEARCH("@mydomain.b.co.uk",TrackingLog[sender-address],1,0)||SEARCH("@mydomain.c.co.uk",TrackingLog[sender-address],1,0),TrackingLog[event-id]="RECEIVE",TrackingLog[source]="STOREDRIVER"))
I have then dragged my recipient-address to a table and then dragged my measure to and filtered on one recipient address.
When I check the figures for one user. They don't seem to match up to what the tracking log exports.
The tracking logs show my user sent four messages (with different message-ids and an event-id of RECEIVE) but the average message sent show the result of 2.25.
I'm not sure if this is my bad mathematical capabilities or if the formula is incorrect.
Can anyone help me understand the formula better so I can ensure the data I am producing is valid and also use this as the basis next for average message size? I'd like to move onto average message size (overall and per user) and average messages received (with average sizes etc).
I'm loving PowerBi but I think I've got a great learning curve, it looks like there is already some great information out there to help me on my way.
Thanks
Jacqui
