Dynamics Community Forum Thread Details

True Active in PowerBi

Hi all,

I am trying to make a chart in PowerBI that shows Active Clients over the past month. Its quite difficult as though if contacts are marked as "Active" they might not have actually had any sub-records created against them.

What I would like to do is be able to say, show me all contact records that have had a sub-record created. Do you know if this is possible?

Kind regards,

Matt

Hi,

Thank you for your query.

First, you need to which related (sub records) you need to depend on.

Contact has several related records like Opportunites, Quotes Sales Orders, Invoices, Quotes, Cases, Activities, etc.

Define a rule on how you consider a contact active.

For example, lets assume an contact is active if it has at least one activity.

To achieve this, we have two options:

If you are on-premise and have access to the Database, use TSQL to write queries using EXISTS and check if Contact has related records. If you are on the cloud, these kinds of queries are not possible, however, you can load all related tabes to PowerBI and take distinct contacts out of it.
Another approach is to use rollup fields to hold the count of the related records on contact, this will make your queries quite straightforward. For example, count all contacts where new_activitycount > 1.

I have blogged a solution on how to show the activity count of a record using rollup field, you can use similar logic to add related records count on contact level:

Dynamics 365 CE: Check if Lead is contacted – Part 1 | CRM Logs (wordpress.com)

Quick Links

Leaderboard