Showing results for 
Search instead for 
Did you mean: 
New Member

DAX Measure to Calculate average number of messages and message sizes from tracking logs



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("",TrackingLog[sender-address],1,0)||SEARCH("",TrackingLog[sender-address],1,0)))/(CALCULATE(DISTINCTCOUNT(TrackingLog[sender-address]),SEARCH("",TrackingLog[sender-address],1,0)||SEARCH("",TrackingLog[sender-address],1,0)||SEARCH("",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.





Helpful resources

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors